Advanced formula/inserting text question

[Chas]

I need help if it is possible. I have 4 workbooks that each contain 36
sheets named 01,02, thru 36. Each sheet is identical layout, just
unique data. Each book is named by year, 2003,2004,2005,2006. I have a
report page that I use to lookup data that I need to change once a
week. The lookup data could be on any of the sheets within anyone of
the books. I am using the following to find the information I need in
one of the cells.

=IF(ISERROR(VLOOKUP($B$4,'[2005.xls]33'!$D$7:$K$49,5,FALSE)),"0",VLOOKUP($B$4,'[2005.xls]33'!$D$7:$K$49,5,FALSE))

There are about 30 different occurrences like the above only the sheet
changes every week. Is there a way that I can replace the
'[2005.xls]33' in the above formula to look at another sheet / cell,
where all I have to do is change one cell each time. That way each week
I only need to change 6 cells. If I can get this to work I can refine
what I am looking for based on other sheets, I just don't want to
change all the formulas each week.

Thanks in advance and if I need to clarify what I am doing please let
me know.

Chas

 

[Niek Otten]

Hi Chas,

Look in HELP for the INDIRECT() function

 

[Chas]

Thank you for your reply, but I have looked thru that and no matter how
I try it I get the you have an error in your formula popup. Could you
be so kind to show me what I may be missing. using the above lookup
without the if and iserror is fine, I will add all that once I get the
formula to work. The cell B 200 is on sheet reports and is in the
woorkbook weekly.

Thanks again
Chas

 

[broro183]

Hi Chas,
Firstly, I would enter the vlookup formula in a column & refer to that
column in your if statement, this means that the vlookup which can slow
a spreadsheet's calculation dramatically (if used a lot) is only
calculated once rather than twice as it would in your current if
statement when there is no error. The vlookup column can then be hidden
from view.

Secondly, this is a quote from somewhere?!
"You could put the name of the source file (2005FebB.xls) in a cell in
the current worksheet (D5, for example) and replace the above
expression with one like this:
=INDIRECT("[" & D5 & "]Sheet1!B3")
Changing the contents of cell D5 to a different filename will make
that file the new source of data. There are a couple of "gottchas,"
however. First of all, you need to *manually open the file that is the
target* of the link; Excel won't do it for you as it would with a
regular link. Also, workbook filenames that contain spaces will trip
up the INDIRECT formula. If you think you might have spaces in your
filenames, you should change the formula to this:
=INDIRECT("'["&D5&"']Sheet1!B3")
"
To apply it in your situation try:
=IF(ISERROR(VLOOKUP(Master!$B$4,indirect("'["&$B$1&".xls]"&$C$1&"'!$D$7:$K$49"),5,FALSE)),"0",VLOOKUP(Master!$B$4,'[2005.xls]33'!$D$7:$K$49,5,FALSE))

To put this together:
B1 = filename (without the ".xls" ending)
c1 = sheetname
d1 =
VLOOKUP($B$4,indirect("'["&$B$1&".xls]"&$C$1&"'!$D$7:$K$49"),5,FALSE)
e1 = if(isna(d1),"0",d1)

nb: I have used isna rather than iserror as NA# is the result when data
is not found while other errors occur for different reasons (check out
help on vlookup).
Change the cell references etc as needed.

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[Chas]

Thanks broro183 I figured it out.

I was close, I just had a [ in the wrong place somehow.

Works like a charm.

Chas

 

[broro183]

Hi Chas,
Awesome - "working like a charm" is exactly what's wanted

Rob Brockett
NZ
Always learning & the best way to learn is to experience...