Offset and sumproduct function

[Ver101]

<TABLE style="WIDTH: 203pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=271 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2889" width=79><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=64 height=20>A1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>B1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>C1</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 59pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=79>D1</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A2</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Product</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Location</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Amount</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A3</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Orange</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A4</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">China</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">14,000.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A5</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Indonesia</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">15,000.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A6</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Mango</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">China</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">16,000.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A7</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Indonesia</TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">17,000.00 </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A8</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD class=xl63 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A9</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A10</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>A11</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Product</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Amount</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">Orange</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">?</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
Hi Guys Can i use offset and sumproduct to get the total of per product with the location is indented per product?

 

[Paradigm_Shift]

if I understand you correctly (your post was rather confusing...) you want to sum both the 14,000 & 15,000 for oranges with 1 formula, then in a separate cell, sum the 16,000 and 17,000 for Mangos?

Correct?

 

[Ver101]

Yes. can it be done?

 

[Paradigm_Shift]

Yes it can. By using IF, SUM and OFFSET...

=IF($A$2=$A$11,SUM(OFFSET($A$2,0,2,2,1)),0) [for Oranges]
and
=IF($B$6=$B$13,SUM(OFFSET($B$6,0,2,2,1)),0) [for Mango]

Type these into B11 and B12, respectively.

Since the cell reference values you supplied aren't quite how Excel does it (eg. A1, A2, B1, C1), I changed the way the table of data was set up slightly. This set up will work with my formulas.

<table width="285" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="width:48pt" width="64" span="2"> <col style="mso-width-source:userset;mso-width-alt:2962;width:61pt" width="81"> <col style="mso-width-source:userset;mso-width-alt:2779;width:57pt" width="76"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td class="xl65" style="width:48pt" width="64">A</td> <td class="xl65" style="width:61pt" width="81">B</td> <td class="xl65" style="width:57pt" width="76">C</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">1</td> <td class="xl66">Product</td> <td class="xl66">Location</td> <td class="xl66">Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">2</td> <td class="xl67">Orange</td> <td class="xl67">China</td> <td class="xl68">14,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">3</td> <td class="xl67">
</td> <td class="xl67">Indonesia</td> <td class="xl68">15,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">4</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">5</td> <td class="xl67">Mango</td> <td class="xl67">China</td> <td class="xl68">16,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">6</td> <td class="xl67">
</td> <td class="xl67">Indonesia</td> <td class="xl68">17,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">7</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">8</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">9</td> <td class="xl67">
</td> <td class="xl67">
</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">10</td> <td class="xl66">Product</td> <td class="xl66">Amount</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">11</td> <td class="xl67">Orange</td> <td class="xl67">29000</td> <td class="xl67">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl65" style="height:15.0pt" height="20">12</td> <td class="xl67">Mango</td> <td class="xl67">33000</td> <td class="xl67">
</td> </tr> </tbody></table>
<table style="width: 221px; height: 252px;" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height:15.0pt" height="20"><td class="xl64" style="height:15.0pt;width:48pt" width="64" height="20">
</td><td class="xl64" style="width:61pt" width="81">
</td><td class="xl64" style="width:57pt" width="76">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td class="xl63" align="right">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td class="xl63" align="right">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td class="xl63" align="right">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td class="xl63" align="right">
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td>
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td class="xl64" style="height:15.0pt" height="20">
</td><td class="xl64">
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td align="right">
</td><td>
</td></tr><tr style="height:15.0pt" height="20"><td style="height:15.0pt" height="20">
</td><td align="right">
</td><td>
</td> </tr> </tbody></table>

 

[Ver101]

ok. thanks.

 

[Ver101]

What is the formula in case you have two criteria?

Ex. Product Location Amount
Mango China ?

Thanks again!!!

 

[Paradigm_Shift]

Is this what you're wanting:

Sum ALL amounts of oranges that ONLY are from China?

So in the example dataset above, you would get 14,000?

Please try to be a bit more specific in future posts.

 

[Ver101]

Yes.that would be the case, and another thing if change the data in A11 with mango (product) how will it updates the amount? seems the formula is only linked to A2 as criteria (=IF($A$2=$A$11,SUM(OFFSET($A$2,0,2,2,1)),0)

 

[Paradigm_Shift]

Ok, this took me a while, but I finally got what you need.

First, I apologize for the error in the original formula I posted for mangos...I wasn't even paying attention.

Now, here is the setup for which the following formula will work:

<table width="406" border="0" cellpadding="0" cellspacing="0" height="252"><colgroup><col style="width:48pt" width="64"> <col style="mso-width-source:userset;mso-width-alt:3876;width:80pt" width="106"> <col style="mso-width-source:userset;mso-width-alt:3913;width:80pt" width="107"> <col style="mso-width-source:userset;mso-width-alt:2889;width:59pt" width="79"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:48pt" width="64" height="20">
</td> <td class="xl68" style="width:80pt" width="106">A</td> <td class="xl68" style="width:80pt" width="107">B</td> <td class="xl68" style="width:59pt" width="79">C</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">1</td> <td class="xl65">Product </td> <td class="xl65">Location </td> <td class="xl65">Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">2</td> <td class="xl66">Orange </td> <td class="xl66">China </td> <td class="xl67">14,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">3</td> <td class="xl66">
</td> <td class="xl66">Indonesia </td> <td class="xl67">15,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">4</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">5</td> <td class="xl66">Mango </td> <td class="xl66">China </td> <td class="xl67">16,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">6</td> <td class="xl66">
</td> <td class="xl66">Indonesia </td> <td class="xl67">17,000.00</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">7</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">8</td> <td class="xl66">
</td> <td class="xl66">
</td> <td class="xl66">
</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">9</td> <td class="xl65">Select Product</td> <td class="xl65">Select Location</td> <td class="xl65">Amount</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">10</td> <td class="xl66">Mango </td> <td class="xl66">China </td> <td class="xl66">16000</td> </tr> </tbody></table>

Insert this formula into C10:
=IF(ISBLANK(B10),IF(INDEX(A:A,MATCH(A10,A2:A6,0)+1)=A10,SUM(OFFSET(INDEX(A:A,MATCH(A10,A2:A6,0)+1),0,2,2,1)),""),IF(B10=OFFSET(INDEX(A:A,MATCH(A10,A2:A6,0)+1),0,1,1,1),OFFSET(INDEX(A:A,(MATCH(A10,A2:A6,0)+1)),0,2,1,1),OFFSET(INDEX(A:A,(MATCH(A10,A2:A6,0)+1)),1,2,1,1)))

What does it do?
It uses what the user types into A10 and B10 and looks for anything that matches in column A, and if it finds a match, and user specifies location too, it looks for a corresponding match in location for the specific product. Then it returns the sum based on the user selections. The user can:

1. only specify a product and leave location blank,
2. specify both a product and a location
3. but CANNOT only specify a product and leave location blank (it would have taken me another 30 mins to add this into the already complex formula)

*NOTE: if both A10 and B10 are left blank, the formula returns #N/A.
WARNING: the formula will currently only work for the exact setup. If you have more items in A and B, you will need to adjust the ranges in the formula that currently say A2:A6 or B2:B6 accordingly. You will also have to update the references that say A10 and B10 accordingly. You can figure that out.

Finally, I suggest you make lists on a separate sheet for the products and locations (if there are A LOT) and make a list using data validation in A10 and B10 so that the user can choose from a dropdown. Here is a link on how to do that: http://www.contextures.com/xlDataVal01.html

Hope this all helps!!!

 

[Ver101]

Hi thanks for this guidelines!!!!!!! highly appreciate