I have done this before, but it has been several years and I cannot recall
exactly how I did it. I am now trying to do this in Excel 2002.

I have Sheets (exported to Excel from another application) for each month
(named Jan 2006, Feb 2006, Mar 2006, etc). On another sheet, I have Columns
for each month (Jan, Feb, Mar, etc) for a side by side comparison. I need a
formula that references the column heading to determine which sheet to get
the data from. I.e. - ='Aug 2006'!B4 is the formula for the Aug 2006 column
row 4, but in the next column (Jul 2006) it should be ='Jul 2006'!B4.

I have used CONCATENATE (&) to create a formula that will will evaluate and
return the cell contents as indicated above. The following is close, but the
end result displays the formula I want evaluated.

="='"&B1&"'!"B4 yields ='Aug 2006'!B4 displayed (rather than the contents of
cell B4 from the sheet named Aug 2006). I am hoping to avoid the need to
find/replace in each column after copying the formula to the right by simply
having the formula refer to the column heading which corresponds to the name
of the sheet from which to retrieve the data. I know the concatenation is
creating the correct formula. How do I get Excel to evaluate the resulting
formula?

Thanks for any help.

Wrap the formula in the INDIRECT function

This: ="='"&B1&"'!"B4
Should be this: =INDIRECT("'"&B1&"'!"&B4)

If B1 contains: Aug 2006
and B4 contains: L19
Then the formula evaluates to ='Aug 2006'!L19

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"sith janitor" wrote:

I have done this before, but it has been several years and I cannot recall
exactly how I did it. I am now trying to do this in Excel 2002.

I have Sheets (exported to Excel from another application) for each month
(named Jan 2006, Feb 2006, Mar 2006, etc). On another sheet, I have Columns
for each month (Jan, Feb, Mar, etc) for a side by side comparison. I need a
formula that references the column heading to determine which sheet to get
the data from. I.e. - ='Aug 2006'!B4 is the formula for the Aug 2006 column
row 4, but in the next column (Jul 2006) it should be ='Jul 2006'!B4.

I have used CONCATENATE (&) to create a formula that will will evaluate and
return the cell contents as indicated above. The following is close, but the
end result displays the formula I want evaluated.

="='"&B1&"'!"B4 yields ='Aug 2006'!B4 displayed (rather than the contents of
cell B4 from the sheet named Aug 2006). I am hoping to avoid the need to
find/replace in each column after copying the formula to the right by simply
having the formula refer to the column heading which corresponds to the name
of the sheet from which to retrieve the data. I know the concatenation is
creating the correct formula. How do I get Excel to evaluate the resulting
formula?

Thanks for any help.

Thanks, Ron.

I had never even encountered the INDIRECT function before your response. It
seems to work great, BUT (saw that coming didn't you), the relative reference
to B4 produces a circular reference error as Excel is evaluating it as the
same, current sheet rather than the B4 of the referenced sheet. Moving the
double-quotes outside the B4 solves the circular reference error and is fine
for copying ACROSS the columns. However, it makes the cell reference static
when copying DOWN (i.e. - when copied into row 10, it is still referencing
row 4, rather than row 10, of the Aug 2006 sheet).

How do I get the cell reference to adjust as I copy down the column? I
tried leaving the B inside the double-quotes and adding &ROW(). This appears
correct in the function editor window, but I get #REF! error. What have I
overlooked?

Thanks again!

"Ron Coderre" wrote:

Wrap the formula in the INDIRECT function

This: ="='"&B1&"'!"B4
Should be this: =INDIRECT("'"&B1&"'!"&B4)

If B1 contains: Aug 2006
and B4 contains: L19
Then the formula evaluates to ='Aug 2006'!L19

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"sith janitor" wrote:

I have done this before, but it has been several years and I cannot recall
exactly how I did it. I am now trying to do this in Excel 2002.

I have Sheets (exported to Excel from another application) for each month
(named Jan 2006, Feb 2006, Mar 2006, etc). On another sheet, I have Columns
for each month (Jan, Feb, Mar, etc) for a side by side comparison. I need a
formula that references the column heading to determine which sheet to get
the data from. I.e. - ='Aug 2006'!B4 is the formula for the Aug 2006 column
row 4, but in the next column (Jul 2006) it should be ='Jul 2006'!B4.

I have used CONCATENATE (&) to create a formula that will will evaluate and
return the cell contents as indicated above. The following is close, but the
end result displays the formula I want evaluated.

="='"&B1&"'!"B4 yields ='Aug 2006'!B4 displayed (rather than the contents of
cell B4 from the sheet named Aug 2006). I am hoping to avoid the need to
find/replace in each column after copying the formula to the right by simply
having the formula refer to the column heading which corresponds to the name
of the sheet from which to retrieve the data. I know the concatenation is
creating the correct formula. How do I get Excel to evaluate the resulting
formula?

Thanks for any help.

See if this gets you headed in the right direction:

With
B1: Aug 2006

B2: =INDIRECT("'"&$B$1&"'!"&ADDRESS(ROW(B2)+2,COLUMN(B 2)))
Copy that formula down and over as far as you need.
That formula returns the value of cell B4 on the Aug 2006 sheet,
automatically changing column and row references as it is copied.


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"sith janitor" wrote:

Thanks, Ron.

I had never even encountered the INDIRECT function before your response. It
seems to work great, BUT (saw that coming didn't you), the relative reference
to B4 produces a circular reference error as Excel is evaluating it as the
same, current sheet rather than the B4 of the referenced sheet. Moving the
double-quotes outside the B4 solves the circular reference error and is fine
for copying ACROSS the columns. However, it makes the cell reference static
when copying DOWN (i.e. - when copied into row 10, it is still referencing
row 4, rather than row 10, of the Aug 2006 sheet).

How do I get the cell reference to adjust as I copy down the column? I
tried leaving the B inside the double-quotes and adding &ROW(). This appears
correct in the function editor window, but I get #REF! error. What have I
overlooked?

Thanks again!

"Ron Coderre" wrote:

Wrap the formula in the INDIRECT function

This: ="='"&B1&"'!"B4
Should be this: =INDIRECT("'"&B1&"'!"&B4)

If B1 contains: Aug 2006
and B4 contains: L19
Then the formula evaluates to ='Aug 2006'!L19

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"sith janitor" wrote:

I have done this before, but it has been several years and I cannot recall
exactly how I did it. I am now trying to do this in Excel 2002.

I have Sheets (exported to Excel from another application) for each month
(named Jan 2006, Feb 2006, Mar 2006, etc). On another sheet, I have Columns
for each month (Jan, Feb, Mar, etc) for a side by side comparison. I need a
formula that references the column heading to determine which sheet to get
the data from. I.e. - ='Aug 2006'!B4 is the formula for the Aug 2006 column
row 4, but in the next column (Jul 2006) it should be ='Jul 2006'!B4.

I have used CONCATENATE (&) to create a formula that will will evaluate and
return the cell contents as indicated above. The following is close, but the
end result displays the formula I want evaluated.

="='"&B1&"'!"B4 yields ='Aug 2006'!B4 displayed (rather than the contents of
cell B4 from the sheet named Aug 2006). I am hoping to avoid the need to
find/replace in each column after copying the formula to the right by simply
having the formula refer to the column heading which corresponds to the name
of the sheet from which to retrieve the data. I know the concatenation is
creating the correct formula. How do I get Excel to evaluate the resulting
formula?

Thanks for any help.

Ron -

Thank you so much. That last formula was definitely "something [i] can work
with".

I was still getting the #REF! error after the change, but realized I still
had the "='" in the INDIRECT function. After dropping the = from the
INDIRECT function, it worked like a charm. I did some tweaking (the $B$1
keeps all column heading references to the column B heading, so I dropped the
absolute on the column reference; the column reference on the monthly sheets
is ALWAYS column B, so I put in an absolute there; stuff like that), but the
bottom line - it worked.

Thanks again for your help.

"Ron Coderre" wrote:

See if this gets you headed in the right direction:

With
B1: Aug 2006

B2: =INDIRECT("'"&$B$1&"'!"&ADDRESS(ROW(B2)+2,COLUMN(B 2)))
Copy that formula down and over as far as you need.
That formula returns the value of cell B4 on the Aug 2006 sheet,
automatically changing column and row references as it is copied.


Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP


"sith janitor" wrote:

Thanks, Ron.

I had never even encountered the INDIRECT function before your response. It
seems to work great, BUT (saw that coming didn't you), the relative reference
to B4 produces a circular reference error as Excel is evaluating it as the
same, current sheet rather than the B4 of the referenced sheet. Moving the
double-quotes outside the B4 solves the circular reference error and is fine
for copying ACROSS the columns. However, it makes the cell reference static
when copying DOWN (i.e. - when copied into row 10, it is still referencing
row 4, rather than row 10, of the Aug 2006 sheet).

How do I get the cell reference to adjust as I copy down the column? I
tried leaving the B inside the double-quotes and adding &ROW(). This appears
correct in the function editor window, but I get #REF! error. What have I
overlooked?

Thanks again!

"Ron Coderre" wrote:

Wrap the formula in the INDIRECT function

This: ="='"&B1&"'!"B4
Should be this: =INDIRECT("'"&B1&"'!"&B4)

If B1 contains: Aug 2006
and B4 contains: L19
Then the formula evaluates to ='Aug 2006'!L19

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"sith janitor" wrote:

I have done this before, but it has been several years and I cannot recall
exactly how I did it. I am now trying to do this in Excel 2002.

I have Sheets (exported to Excel from another application) for each month
(named Jan 2006, Feb 2006, Mar 2006, etc). On another sheet, I have Columns
for each month (Jan, Feb, Mar, etc) for a side by side comparison. I need a
formula that references the column heading to determine which sheet to get
the data from. I.e. - ='Aug 2006'!B4 is the formula for the Aug 2006 column
row 4, but in the next column (Jul 2006) it should be ='Jul 2006'!B4.

I have used CONCATENATE (&) to create a formula that will will evaluate and
return the cell contents as indicated above. The following is close, but the
end result displays the formula I want evaluated.

="='"&B1&"'!"B4 yields ='Aug 2006'!B4 displayed (rather than the contents of
cell B4 from the sheet named Aug 2006). I am hoping to avoid the need to
find/replace in each column after copying the formula to the right by simply
having the formula refer to the column heading which corresponds to the name
of the sheet from which to retrieve the data. I know the concatenation is
creating the correct formula. How do I get Excel to evaluate the resulting
formula?

Thanks for any help.