Durjoy Paul

Hi Niki,
To find out the price, you need to calculate the product of unit and unit quantity. Your sheet 1 denotes the unit of rate 1, rate 2, rate 3, and rate 4. The unit quantity will be found in sheet 2. So, in that case, you can apply the VLOOKUP formula to get the specific unit and unit quantity. You can use the following formula to calculate.
=VLOOKUP(J8,$B$7:$C$12,2,FALSE)*VLOOKUP(J8,Sheet1!$B$4:$C$8,2,FALSE)
=VLOOKUP(J8,$B$7:$C$12,2,FALSE):
First, you need to define the value you are looking for. Here, J8 refers to the lookup value. After that, define the table array where you think the required answer can remain. Then, define the column number of the expected result. Finally, use false to get the exact match. Then, we have to multiply the result of the first VLOOKUP by the result of the second VLOOKUP.
VLOOKUP(J8,Sheet1!$B$4:$C$8,2,FALSE):
As we put the unit in sheet 1, we have to look up the rate 1 unit by utilizing the VLOOKUP function. First, define rate 1 as the lookup value. Then, set the table array in sheet 1. After that, define the column number of the expected result. Finally, use false to get the exact match.
The product of these two values will give the price of rate 1. Do the same procedure for other cases.
Do you get your preferred answer? check on it. Otherwise, give us a more accurate question. We are always ready to help you.
Thank you.

Hello BOMBSHELLSHOCK, first, you need to convert your dataset into Excel table format. To do that, you have to select your whole dataset and go to the Insert tab on the ribbon. After that, select Table from the Tables group. As a result, it will convert your dataset into a table. Then, you can write something in the last row and column and press Enter. As a result, Excel will insert new rows and columns into your Excel table. So, to perform the whole process, you must convert your dataset into a table. Mind it.
If you face any more problems, you can share them with us. We are happy to deal with it and provide you with a proper solution.

Hello Nathan,
first of all, if you want to use sum color function, you can use the following code.

Function SumByCol(SumRange As Range, SumColor As Range)
Dim SumColValue As Integer
Dim TotSum As Long
SumColValue = SumColor.Interior.ColorIndex
Set pCell = SumRange
For Each pCell In SumRange
If pCell.Interior.ColorIndex = SumColValue Then
TotSum = TotSum + pCell.Value
End If
Next pCell
SumByCol = TotSum
End Function

Then, close the VBA code and select any cell where you want to put the sum value. After that, write the following formula
=SumByCol(C5:C10,C13)
Where the range of cells C5 to C10 defines the range. C13 defines the color. You need to show only color in one cell. From where you can take the reference. Here, C13 denotes that color reference. So, when you want to change the color, you have to change the color reference also.

Hello Mark,
We have sent you a mail. Can you see it and give us a response?

Hello Jorge,
This is an array formula. It must be completed by pressing Ctrl+Shift+Enter. Otherwise, it won’t work. For a normal formula, we press only Enter. But for an array, you have to press Ctrl+Shift+Enter. I hope you get your solution. If you find any more problems, feel free to ask in the comment box. We are always here to help you.

Hello Kane,

You need to put the picture in one cell. Just resize it and put the image in a single cell then apply the sorting. I think you will get your desired result.

1. First, change the row height of the cell to adjust the image in the cell or resize it to adjust it in a single cell.

2. Then, select the range of cells. Go to the Data tab and select your preferred sorting.

3. Finally, you will see the image is also changing its place while sorting.

Hello Mark SIMONS and GVG,
Can you please share your Excel dataset with us? Then, we will try our best to provide you with the required solution.

Hello Roger,
Can you please share your Excel dataset with us? We will try our best to solve your problem. Because in our Excel 365, the conditional formatting works perfectly for absolute and relative cell references in both cases.

Hello Tawfik,
Can you please share your Excel file with us? We will try our best to solve your problem.

Hello, Can you please email us your Excel dataset with the problem?

We will try our best to give you a proper solution.

Hi Henrik, to highlight rows in Excel, you need to apply VBA code in that certain worksheet like this article. Otherwise, you need to run the code every time when you go to the next row and highlight it. This is a major disadvantage of this method. If you need to apply in all the worksheets, then you need to utilize the following code.

Sub Highlights_Active_Row()
Static xRow
If xRow <> “” Then
With Rows(xRow).Interior
.ColorIndex = xlNone
End With
End If
Active_Row = Selection.Row
xRow = Active_Row
With Rows(Active_Row).Interior
.ColorIndex = 7
.Pattern = xlSolid
End With
End Sub

Note: Remember every time you go to the next row, you have to run the code every single time.

Hello Brow, yes you are right. It should be Row(A1) = HighlightActiveRow

Hey Max, try the following VBA code

Sub Changing_Date_Format_Using_Condition()
Dim i As Integer

i = InputBox(“Enter a Number”)

If i = 2 Then
Range(“A1”).NumberFormat = “mm/dd/yyyy”
ElseIf i = 10 Then
Range(“A2”).NumberFormat = “dddd-dd-mmm-yy”
Else
Range(“A3”).NumberFormat = “mmm-yy”
End If

End Sub

Is this your required solution? If not then explain more about it. We will help you to get the result.

⇒OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3)) : In the OFFSET formula, you need to put the cell reference, rows and cols number.
⇒OFFSET($B$2………): Here $B$2 is the cell reference of the OFFSET function.

⇒OFFSET($B$2, ROUNDUP(ROWS($1:1)/3,0)-1……): Here, the ROUNDUP function gives the specific number of rows down. ROWS function provides the number of rows in a given array. Here, the cell reference is $1:1. So, the Rows function returns 1. As we have three rows in our dataset. So, divide the return value of the Rows function by 3. It will return 0.333. Then, the ROUNDUP value will round the number into the nearest whole number. The whole number of 0.333 is 1. After that, subtract 1 from it. So, the final value is 0 which is the required rows down.

⇒OFFSET($B$2,ROUNDUP(ROWS($1:1)/3,0)-1,MOD(ROWS($1:1)-1,3)): To extract the columns right, we utilize the MOD function. It gives a reminder after a number is divided by the divisor. First, the Rows function provides the number of rows from the given array. Here, it returns 1. Then, subtract 1 from the return value. Then, divide the value by the total number of columns. The MOD function will return the reminder. Here, it returns 0.
So, for cell reference $B$2 and rows 0 down and cols 0 right, it returns Arif as our answer. Do the same for other cases.
Try this solution I think you will get your desired result. If you face any more problems, inform us.