wk19!$A:$O => SOME_FUNCTION!$A:$O

J

Jouni Kananen

Hi,

I have a number of formulas referring to certain data sheets (named as per
week) like this one..:
=IF(ISNA(VLOOKUP($A2,wk19!$A:$O,2,FALSE)),"",VLOOKUP($A2,wk19!$A:$O,2,FALSE))

The maintenance of the worksheet would be easier if I had those wk19, wk20,
wk21, ... as a header row in the data table and refer to those cells in the
formula, but how do I achieve that?

wk19!$A:$O should be something that.:
1' reads the value from cell D1 and
2' combines that with a string !$A:$O

Then I could just copy the formula to the next column and it finds the
corresponding week ID from cell E1. Might be easy, but I just can't find it.

Thanks,
Jouni
 
B

Biff

Hi!
wk19!$A:$O should be something that.:
1' reads the value from cell D1 and
2' combines that with a string !$A:$O
Click to expand...

D1 = wk19
E1 = wk20
F1 = wk21
etc

=IF(ISNA(VLOOKUP($A2,INDIRECT(D1&"!A:O"),2,FALSE)),"",VLOOKUP($A2,INDIRECT(D1&"!A:O"),2,FALSE))

Biff
 
J

Jouni Kananen

Works perfect, thanks Biff!

Jouni

Biff said:
Hi!


D1 = wk19
E1 = wk20
F1 = wk21
etc

=IF(ISNA(VLOOKUP($A2,INDIRECT(D1&"!A:O"),2,FALSE)),"",VLOOKUP($A2,INDIRECT(D1&"!A:O"),2,FALSE))

Biff
Click to expand...
 
Top