Look up twice
 

I need to do a report in a workbook, the file is like this,

there are tabs for each year named 2005, 2006, etc. On each tab, there
are quarters, like

Q1 Q2
data1 data2 data1 data2

I'd like to link a data based on current quarter and year, for example,
I want on Report sheet,

A1 - 2006.Q2.data2

in which 2006 and Q2 are variables (referenced by names like CurY,
CurQ).

How to construct the lookup or offset? Thank you.

Look up twice
 

You have the following

CurY = YEAR
CurQ = QUARTER

You also have tab names that are consistent with the tab names.

Let's assume that you want to extract data for Q2 2006. The data for 2006
is on worksheet 2006 and the quarter names are in row 1 and the data is in
ROW 2.

You may need to change A1:D2 to match the range you'd use for your lookup.
The first row needs to include the quarters and the last row includes the
data. Also, if the data isn't in the row below the quarter name, you'll
need to change the ,2, to something else.

=HLOOKUP(CurQ,INDIRECT("'"&CurY&"'!A1:D2"),2,FALSE )

HTH,
Barb Reinhardt




"muster" wrote:

I need to do a report in a workbook, the file is like this,

there are tabs for each year named 2005, 2006, etc. On each tab, there
are quarters, like

Q1 Q2
data1 data2 data1 data2

I'd like to link a data based on current quarter and year, for example,
I want on Report sheet,

A1 - 2006.Q2.data2

in which 2006 and Q2 are variables (referenced by names like CurY,
CurQ).

How to construct the lookup or offset? Thank you.

Look up twice
 

That works for data1, but not data2 and so on. Remember the data in
this format

Q1 Q2
data1 data2 data3 ... data1 data2 data3 ....

I want to offset or index somehow, but they seemingly don't take
variables (names, combinations, address(), etc.)

Please give more hints. Thank you.



Barb Reinhardt wrote:
You have the following

CurY = YEAR
CurQ = QUARTER

You also have tab names that are consistent with the tab names.

Let's assume that you want to extract data for Q2 2006. The data for 2006
is on worksheet 2006 and the quarter names are in row 1 and the data is in
ROW 2.

You may need to change A1:D2 to match the range you'd use for your lookup.
The first row needs to include the quarters and the last row includes the
data. Also, if the data isn't in the row below the quarter name, you'll
need to change the ,2, to something else.

=HLOOKUP(CurQ,INDIRECT("'"&CurY&"'!A1:D2"),2,FALSE )

HTH,
Barb Reinhardt




"muster" wrote:

I need to do a report in a workbook, the file is like this,

there are tabs for each year named 2005, 2006, etc. On each tab, there
are quarters, like

Q1 Q2
data1 data2 data1 data2

I'd like to link a data based on current quarter and year, for example,
I want on Report sheet,

A1 - 2006.Q2.data2

in which 2006 and Q2 are variables (referenced by names like CurY,
CurQ).

How to construct the lookup or offset? Thank you.

Look up twice
 

This worked,

=INDEX(INDIRECT("'"&curY&"'!A1:L1"),1, n)

It looks a bit awkard, let me know if you get a better way. Thank you.

muster wrote:
That works for data1, but not data2 and so on. Remember the data in
this format

Q1 Q2
data1 data2 data3 ... data1 data2 data3 ....

I want to offset or index somehow, but they seemingly don't take
variables (names, combinations, address(), etc.)

Please give more hints. Thank you.



Barb Reinhardt wrote:
You have the following

CurY = YEAR
CurQ = QUARTER

You also have tab names that are consistent with the tab names.

Let's assume that you want to extract data for Q2 2006. The data for 2006
is on worksheet 2006 and the quarter names are in row 1 and the data is in
ROW 2.

You may need to change A1:D2 to match the range you'd use for your lookup.
The first row needs to include the quarters and the last row includes the
data. Also, if the data isn't in the row below the quarter name, you'll
need to change the ,2, to something else.

=HLOOKUP(CurQ,INDIRECT("'"&CurY&"'!A1:D2"),2,FALSE )

HTH,
Barb Reinhardt




"muster" wrote:

I need to do a report in a workbook, the file is like this,

there are tabs for each year named 2005, 2006, etc. On each tab, there
are quarters, like

Q1 Q2
data1 data2 data1 data2

I'd like to link a data based on current quarter and year, for example,
I want on Report sheet,

A1 - 2006.Q2.data2

in which 2006 and Q2 are variables (referenced by names like CurY,
CurQ).

How to construct the lookup or offset? Thank you.