I have 2 spreadsheets with identical tabs... 1 name Pricing Model and 1 name
Pricing Model A. I add identical tabs to both spreadsheets every week. The
products on each spreadsheet are not identical but if an item exist in
Pricing Model it's price should be carried over to Pricing Model A. I have a
formula on a1 to get the sheet name for Pricing Model A. Each week is named
on the following format year_week no (ex 2007_01 for week 1 of year 2007) and
they are on both spreadsheets. I created the following formula to lookup the
price in Pricing Model
I have the following formula in A1 to get the sheet name:
=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1,125)

then have the following formula in the cell where i want the price to appear

=VLOOKUP(A2,CONCATENATE("'i:\pricing\[pricing
model.xls]"&A1&"'!f$8:l$249"),7,FALSE)

The reason why I want to do is to sychcronize both spreadsheets so that I
will be comparing the same weeks as I sheets every week but the formula keep
getting me a #VALUE. What's wrong with the formula?

You don't need to use & within the CONCATENATE function. However, this
isn't the problem - you would need to use INDIRECT instead of
CONCATENATE to build up a reference. INDIRECT will only work if both
workbooks are open (in which case you do not need the file path
references).

Hope this helps.

Pete

On Jan 27, 1:39 am, mmmbl wrote:
I have 2 spreadsheets with identical tabs... 1 name Pricing Model and 1 name
Pricing Model A. I add identical tabs to both spreadsheets every week. The
products on each spreadsheet are not identical but if an item exist in
Pricing Model it's price should be carried over to Pricing Model A. I have a
formula on a1 to get the sheet name for Pricing Model A. Each week is named
on the following format year_week no (ex 2007_01 for week 1 of year 2007) and
they are on both spreadsheets. I created the following formula to lookup the
price in Pricing Model
I have the following formula in A1 to get the sheet name:
=MID(CELL("filename",A1), FIND("]", CELL("filename", A1))+ 1,125)

then have the following formula in the cell where i want the price to appear

=VLOOKUP(A2,CONCATENATE("'i:\pricing\[pricing
model.xls]"&A1&"'!f$8:l$249"),7,FALSE)

The reason why I want to do is to sychcronize both spreadsheets so that I
will be comparing the same weeks as I sheets every week but the formula keep
getting me a #VALUE. What's wrong with the formula?