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

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)),"",VLOOK UP($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

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

Hi!

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

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

"Jouni Kananen" wrote in message
...
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)),"",VLOOK UP($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

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

Works perfect, thanks Biff!

Jouni

"Biff" wrote in message
...
Hi!

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

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

"Jouni Kananen" wrote in message
...
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)),"",VLOOK UP($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

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

You're welcome. Thanks for the feedback.

Biff

"Jouni Kananen" wrote in message
...
Works perfect, thanks Biff!

Jouni

"Biff" wrote in message
...
Hi!

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

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

"Jouni Kananen" wrote in message
...
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)),"",VLOOK UP($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