Lookup in multple worksheets
 

,Can anyone help!

I have a workbook containing worksheets representing each week of the
year,numbered 1 - 52, there is also a worksheet on which I collect data.

I need to populate cells on the Data worksheet with infromation from the
worksheet relating to the current week of the year. I can get HLOOKUP to work
but only if I enter the Sheet number in the fomula. How dow I write the
formula to look up the correct worksheet , based on the Number I enter into
the Week Number cell on my Data worksheet

Lookup in multple worksheets
 

If your formula has


Week 10 Sht!A1:A100

If row 1 contain the week number then

Indirect("Week " & C1 & " Sht!A1:A100")


"Mike B" wrote:

,Can anyone help!

I have a workbook containing worksheets representing each week of the
year,numbered 1 - 52, there is also a worksheet on which I collect data.

I need to populate cells on the Data worksheet with infromation from the
worksheet relating to the current week of the year. I can get HLOOKUP to work
but only if I enter the Sheet number in the fomula. How dow I write the
formula to look up the correct worksheet , based on the Number I enter into
the Week Number cell on my Data worksheet

Lookup in multple worksheets
 

Hi Mike B,

To get Week no of current year put following formula in your sheet say in
cell A1.

=INT((TODAY()-DATE(YEAR(TODAY()),1,1))/7)

Now you can use Indirect function in Hlookup like following

=HLOOKUP(C12,INDIRECT(A1&"!A1:B5"),2,FALSE)

Replace cell ref as per your requirements.

Regards

H S Shastri

++++++++++++++++++++++++++++++++++++++++++++++++++ +++

"Mike B" wrote:

,Can anyone help!

I have a workbook containing worksheets representing each week of the
year,numbered 1 - 52, there is also a worksheet on which I collect data.

I need to populate cells on the Data worksheet with infromation from the
worksheet relating to the current week of the year. I can get HLOOKUP to work
but only if I enter the Sheet number in the fomula. How dow I write the
formula to look up the correct worksheet , based on the Number I enter into
the Week Number cell on my Data worksheet

Lookup in multple worksheets
 

Hi HS,

Might as well use enter 1 in A2 and 2 in A2 and highlight both and fill down
to row 53 or 54.

Or you can get fancy with

=WEEKNUM(39814+ROW(A1)*7-7)

or

=WEEKNUM(DATE(2009,1,1)+ROW(A1)*7-7)

I think the op probably wants you second formula.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"HARSHAWARDHAN. S .SHASTRI" wrote:

Hi Mike B,

To get Week no of current year put following formula in your sheet say in
cell A1.

=INT((TODAY()-DATE(YEAR(TODAY()),1,1))/7)

Now you can use Indirect function in Hlookup like following

=HLOOKUP(C12,INDIRECT(A1&"!A1:B5"),2,FALSE)

Replace cell ref as per your requirements.

Regards

H S Shastri

++++++++++++++++++++++++++++++++++++++++++++++++++ +++

"Mike B" wrote:

,Can anyone help!

I have a workbook containing worksheets representing each week of the
year,numbered 1 - 52, there is also a worksheet on which I collect data.

I need to populate cells on the Data worksheet with infromation from the
worksheet relating to the current week of the year. I can get HLOOKUP to work
but only if I enter the Sheet number in the fomula. How dow I write the
formula to look up the correct worksheet , based on the Number I enter into
the Week Number cell on my Data worksheet

Lookup in multple worksheets
 

Thanks for the help, the replies I received have help[ed a great deal.

"Joel" wrote:

If your formula has


Week 10 Sht!A1:A100

If row 1 contain the week number then

Indirect("Week " & C1 & " Sht!A1:A100")


"Mike B" wrote:

,Can anyone help!

I have a workbook containing worksheets representing each week of the
year,numbered 1 - 52, there is also a worksheet on which I collect data.

I need to populate cells on the Data worksheet with infromation from the
worksheet relating to the current week of the year. I can get HLOOKUP to work
but only if I enter the Sheet number in the fomula. How dow I write the
formula to look up the correct worksheet , based on the Number I enter into
the Week Number cell on my Data worksheet

Lookup in multple worksheets
 

Thanks for your help, I had spent a lot of time trying to work around the
problem.

Mike

"HARSHAWARDHAN. S .SHASTRI" wrote:

Hi Mike B,

To get Week no of current year put following formula in your sheet say in
cell A1.

=INT((TODAY()-DATE(YEAR(TODAY()),1,1))/7)

Now you can use Indirect function in Hlookup like following

=HLOOKUP(C12,INDIRECT(A1&"!A1:B5"),2,FALSE)

Replace cell ref as per your requirements.

Regards

H S Shastri

++++++++++++++++++++++++++++++++++++++++++++++++++ +++

"Mike B" wrote:

,Can anyone help!

I have a workbook containing worksheets representing each week of the
year,numbered 1 - 52, there is also a worksheet on which I collect data.

I need to populate cells on the Data worksheet with infromation from the
worksheet relating to the current week of the year. I can get HLOOKUP to work
but only if I enter the Sheet number in the fomula. How dow I write the
formula to look up the correct worksheet , based on the Number I enter into
the Week Number cell on my Data worksheet