Hi,
I'm looking to do a vlookup in a sales report that includes subtotals.
The formula i have come up with thus far is the following:
=VLOOKUP("1822 Total",'[Period 6 sales by
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).
The only problem with this formula is that I have to mannually change
the "____ Total" part for each corresponding store. Thus I have to
input "122 Total" for store 122 in the worksheet and so on. The 122
portion of the part in quotations is foudn in another cell on the
workseet so I was hoping to program the formula to pick up the store
number automatically from the cell somethign along the lines of
=VLOOKUP(B4+" Total",'[Period 6 sales by
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE). Can
you please tell me if this is possible and offer any suggestions.
Thank you.


--
akaramarkovic
------------------------------------------------------------------------
akaramarkovic's Profile: http://www.excelforum.com/member.php...o&userid=24801
View this thread: http://www.excelforum.com/showthread...hreadid=383584

You were VERY close:

=VLOOKUP(B4+" Total",'[Period 6 sales by
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).

Should be:
=VLOOKUP(B4&" Total",'[Period 6 sales by
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).

Reason:
The + sign is an arithmetic operator
The & sign is a text concatenator

Example:
="any"&"thing" evaluates to "anything"
while ="any"+"thing" evaluate to #value

Does that help?

Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=383584

Change
=VLOOKUP(B4+" Total",
to
=VLOOKUP(B4&" Total",
HTH,
--
Gary Brown

If this post was helpful, please click the ''''Yes'''' button next to
''''Was this Post Helpfull to you?".


"akaramarkovic" wrote:


Hi,
I'm looking to do a vlookup in a sales report that includes subtotals.
The formula i have come up with thus far is the following:
=VLOOKUP("1822 Total",'[Period 6 sales by
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE).
The only problem with this formula is that I have to mannually change
the "____ Total" part for each corresponding store. Thus I have to
input "122 Total" for store 122 in the worksheet and so on. The 122
portion of the part in quotations is foudn in another cell on the
workseet so I was hoping to program the formula to pick up the store
number automatically from the cell somethign along the lines of
=VLOOKUP(B4+" Total",'[Period 6 sales by
SKU.xls]MFGRP-34_MFR-43152_20050522-200'!$B$29:$I$5643,8,FALSE). Can
you please tell me if this is possible and offer any suggestions.
Thank you.


--
akaramarkovic
------------------------------------------------------------------------
akaramarkovic's Profile: http://www.excelforum.com/member.php...o&userid=24801
View this thread: http://www.excelforum.com/showthread...hreadid=383584

Ron,
You helped me score some major brownie points with my boss!!!
Best regards,
Aleks Karamarkovic


--
akaramarkovic
------------------------------------------------------------------------
akaramarkovic's Profile: http://www.excelforum.com/member.php...o&userid=24801
View this thread: http://www.excelforum.com/showthread...hreadid=383584

You were mostly there, already. I'm glad I could help.

Regards,
Ron


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=383584