,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

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

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

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

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

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