Lookup formula with dynamic sheet reference
 

I have a workbook with a summary worksheet and several weather data
worksheets. The data worksheets have unique names but all have the same
structure. I would like to construct a formula that would lookup data for a
specific day (refrenced in a column on the summary sheet) for a specific
weather data worksheet (also listed in a column on the summary sheet).

Is this possible? Have I given enough description of the circumstance? I
have an idea that would utilize the "choose" function, but that would not be
dynamic.

--
William Blake

Lookup formula with dynamic sheet reference
 

Not quite enough description - which cells in the summary sheet
contain the date and the worksheet name? What is the structure of the
data in the weather sheets, and what data do you want to return to the
summary sheet if you find a match?

You can do it with the INDIRECT function, and if you can answer the
other questions I'll be able to suggest an appropriate formula.

Pete

On Aug 13, 5:04*pm, William Blake
wrote:
I have a workbook with a summary worksheet and several weather data
worksheets. *The data worksheets have unique names but all have the same
structure. *I would like to construct a formula that would lookup data for a
specific day (refrenced in a column on the summary sheet) for a specific
weather data worksheet (also listed in a column on the summary sheet).

Is this possible? *Have I given enough description of the circumstance? *I
have an idea that would utilize the "choose" function, but that would not be
dynamic.

--
William Blake

Lookup formula with dynamic sheet reference
 

The summary sheet contains a table of data columns B:L. The columns I:L are
where I am constructing my formulas. The date reference is in column E and
the sheet name reference is in column F.

The weather data sheets are setup in columns, with the date in column A and
the weather data (e.g. precipitation, wind speed, etc.) in the succeeding
columns.

I would like to set up a function to populate the summary sheet from the
cooresponding weather data sheet (which matches the sheet name reference in
column F of the summary table) with the data which cooresponds to the
appropriate date.

Is that more descript?
--
William Blake


"Pete_UK" wrote:

Not quite enough description - which cells in the summary sheet
contain the date and the worksheet name? What is the structure of the
data in the weather sheets, and what data do you want to return to the
summary sheet if you find a match?

You can do it with the INDIRECT function, and if you can answer the
other questions I'll be able to suggest an appropriate formula.

Pete

On Aug 13, 5:04 pm, William Blake
wrote:
I have a workbook with a summary worksheet and several weather data
worksheets. The data worksheets have unique names but all have the same
structure. I would like to construct a formula that would lookup data for a
specific day (refrenced in a column on the summary sheet) for a specific
weather data worksheet (also listed in a column on the summary sheet).

Is this possible? Have I given enough description of the circumstance? I
have an idea that would utilize the "choose" function, but that would not be
dynamic.

--
William Blake

Lookup formula with dynamic sheet reference
 

Well,I still have to make some assumptions - assume you have a header
row in your summary sheet, so that the first date appears in E2 with a
sheet name in F2. In I2 you can use this formula:

=VLOOKUP($E2,INDIRECT("'"&$F2&"'!A:E"),COLUMN(B1), 0)

This will bring the data from column B of your weather sheet. Then you
can copy this formula across into J2:L2, and then copy I2:L2 down as
far as you require.

Hope this helps.

Pete

On Aug 13, 6:00*pm, William Blake
wrote:
The summary sheet contains a table of data columns B:L. *The columns I:L are
where I am constructing my formulas. *The date reference is in column E and
the sheet name reference is in column F. *

The weather data sheets are setup in columns, with the date in column A and
the weather data (e.g. precipitation, wind speed, etc.) in the succeeding
columns.

I would like to set up a function to populate the summary sheet from the
cooresponding weather data sheet (which matches the sheet name reference in
column F of the summary table) with the data which cooresponds to the
appropriate date.

Is that more descript?
--
William Blake



"Pete_UK" wrote:
Not quite enough description - which cells in the summary sheet
contain the date and the worksheet name? What is the structure of the
data in the weather sheets, and what data do you want to return to the
summary sheet if you find a match?

You can do it with the INDIRECT function, and if you can answer the
other questions I'll be able to suggest an appropriate formula.

Pete

On Aug 13, 5:04 pm, William Blake
wrote:
I have a workbook with a summary worksheet and several weather data
worksheets. *The data worksheets have unique names but all have the same
structure. *I would like to construct a formula that would lookup data for a
specific day (refrenced in a column on the summary sheet) for a specific
weather data worksheet (also listed in a column on the summary sheet)..

Is this possible? *Have I given enough description of the circumstance? *I
have an idea that would utilize the "choose" function, but that would not be
dynamic.

--
William Blake- Hide quoted text -

- Show quoted text -

Lookup formula with dynamic sheet reference
 

Pete,

You sir are fantastic, the INDIRECT function was exactly what I needed.
Thank you.
--
William Blake


"Pete_UK" wrote:

Not quite enough description - which cells in the summary sheet
contain the date and the worksheet name? What is the structure of the
data in the weather sheets, and what data do you want to return to the
summary sheet if you find a match?

You can do it with the INDIRECT function, and if you can answer the
other questions I'll be able to suggest an appropriate formula.

Pete

On Aug 13, 5:04 pm, William Blake
wrote:
I have a workbook with a summary worksheet and several weather data
worksheets. The data worksheets have unique names but all have the same
structure. I would like to construct a formula that would lookup data for a
specific day (refrenced in a column on the summary sheet) for a specific
weather data worksheet (also listed in a column on the summary sheet).

Is this possible? Have I given enough description of the circumstance? I
have an idea that would utilize the "choose" function, but that would not be
dynamic.

--
William Blake

Lookup formula with dynamic sheet reference
 

You're welcome, William - thanks for feeding back.

Pete

On Aug 13, 8:39*pm, William Blake
wrote:
Pete,

You sir are fantastic, the INDIRECT function was exactly what I needed. *
Thank you.
--
William Blake