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

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

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

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