I have Worksheets("Data") and worksheets named for each year ONLY the
current year and previous years are available, ie Worksheets("2011") will not
exist until it is 2011.

Worksheets("Data") is a summary worksheet. It pulls totals and/or averages
from each year worksheet. Since future year worksheets do not exist until
that year arrives, I have formulas on the Data worksheet that check to see if
that year is here. If not, then "".

Hence, when a new year worksheet is created, it will also place that year,
ie 2010, as the column header, say in cell E8 of Worksheets("Data"). The
cells in column E, below E8 will reference a particular cell on the 2010
worksheet.

My question is this: What is the formula that will use the value in E8, in
this case 2010, to reference E16 on Worksheets("2010")? F8 will eventually
be 2011, etc. and the cells below each will pull data from the appropriate
worksheet based on the value (year) in E8, F8, G8....AH8.

Example:
Worksheets("Data")
E8 = 2010
Instead of formula in E16 being: =IF(E8 = "","",2010!E16)
I want the 2010 portion of the formula to reference E8 to pull the value.

Sorry if wordy, but wanted to make clear as possible.

Thanks for your help,

Les

On Fri, 14 May 2010 11:51:01 -0700, WLMPilot
wrote:

I have Worksheets("Data") and worksheets named for each year ONLY the
current year and previous years are available, ie Worksheets("2011") will not
exist until it is 2011.

Worksheets("Data") is a summary worksheet. It pulls totals and/or averages
from each year worksheet. Since future year worksheets do not exist until
that year arrives, I have formulas on the Data worksheet that check to see if
that year is here. If not, then "".

Hence, when a new year worksheet is created, it will also place that year,
ie 2010, as the column header, say in cell E8 of Worksheets("Data"). The
cells in column E, below E8 will reference a particular cell on the 2010
worksheet.

My question is this: What is the formula that will use the value in E8, in
this case 2010, to reference E16 on Worksheets("2010")? F8 will eventually
be 2011, etc. and the cells below each will pull data from the appropriate
worksheet based on the value (year) in E8, F8, G8....AH8.

Example:
Worksheets("Data")
E8 = 2010
Instead of formula in E16 being: =IF(E8 = "","",2010!E16)
I want the 2010 portion of the formula to reference E8 to pull the value.

Sorry if wordy, but wanted to make clear as possible.

Thanks for your help,

Les

Try this formula in cell E16:

=IF(E8="","",INDIRECT(E8&"!E16"))

Hope this helps / Lars-Åke

You can do that using INDIRECT and ADDRESS:
=IF(LEN(E$8)=0,"",INDIRECT(ADDRESS(16,5,,,E$8)))

16 is row 16 and 5 is column E. If you prefer, you can use the ROW & COLUMN
functions with E16 like this:
=IF(LEN(E$8)=0,"",INDIRECT(ADDRESS(ROW(E16),COLUMN (E16),,,E$8)))

Hope this helps,

Hutch

"WLMPilot" wrote:

I have Worksheets("Data") and worksheets named for each year ONLY the
current year and previous years are available, ie Worksheets("2011") will not
exist until it is 2011.

Worksheets("Data") is a summary worksheet. It pulls totals and/or averages
from each year worksheet. Since future year worksheets do not exist until
that year arrives, I have formulas on the Data worksheet that check to see if
that year is here. If not, then "".

Hence, when a new year worksheet is created, it will also place that year,
ie 2010, as the column header, say in cell E8 of Worksheets("Data"). The
cells in column E, below E8 will reference a particular cell on the 2010
worksheet.

My question is this: What is the formula that will use the value in E8, in
this case 2010, to reference E16 on Worksheets("2010")? F8 will eventually
be 2011, etc. and the cells below each will pull data from the appropriate
worksheet based on the value (year) in E8, F8, G8....AH8.

Example:
Worksheets("Data")
E8 = 2010
Instead of formula in E16 being: =IF(E8 = "","",2010!E16)
I want the 2010 portion of the formula to reference E8 to pull the value.

Sorry if wordy, but wanted to make clear as possible.

Thanks for your help,

Les

I tried the formula you indicated and got a #REF

Worksheets("Data") has the following:
E8: 2010 (number format)
E16: =IF(E8="","",Indirect(E8 & "!E16") --- Answer to formula is #REF

Worksheets("2010") has the following:
E16 = 5 (number format)


Any suggestions on this problem?

Thanks,
Les

"Lars-Ã…ke Aspelin" wrote:

On Fri, 14 May 2010 11:51:01 -0700, WLMPilot
wrote:

I have Worksheets("Data") and worksheets named for each year ONLY the
current year and previous years are available, ie Worksheets("2011") will not
exist until it is 2011.

Worksheets("Data") is a summary worksheet. It pulls totals and/or averages
from each year worksheet. Since future year worksheets do not exist until
that year arrives, I have formulas on the Data worksheet that check to see if
that year is here. If not, then "".

Hence, when a new year worksheet is created, it will also place that year,
ie 2010, as the column header, say in cell E8 of Worksheets("Data"). The
cells in column E, below E8 will reference a particular cell on the 2010
worksheet.

My question is this: What is the formula that will use the value in E8, in
this case 2010, to reference E16 on Worksheets("2010")? F8 will eventually
be 2011, etc. and the cells below each will pull data from the appropriate
worksheet based on the value (year) in E8, F8, G8....AH8.

Example:
Worksheets("Data")
E8 = 2010
Instead of formula in E16 being: =IF(E8 = "","",2010!E16)
I want the 2010 portion of the formula to reference E8 to pull the value.

Sorry if wordy, but wanted to make clear as possible.

Thanks for your help,

Les

Try this formula in cell E16:

=IF(E8="","",INDIRECT(E8&"!E16"))

Hope this helps / Lars-Ã…ke


.

Make sure that the name of the "2010" worksheet is exactly "2010".
If the name has an extra space, like "2010 " or " 2010" you will get
the #REF error.

Hope this helps / Lars-Åke

On Sat, 15 May 2010 08:09:01 -0700, WLMPilot
wrote:

I tried the formula you indicated and got a #REF

Worksheets("Data") has the following:
E8: 2010 (number format)
E16: =IF(E8="","",Indirect(E8 & "!E16") --- Answer to formula is #REF

Worksheets("2010") has the following:
E16 = 5 (number format)


Any suggestions on this problem?

Thanks,
Les

"Lars-Åke Aspelin" wrote:

On Fri, 14 May 2010 11:51:01 -0700, WLMPilot
wrote:

I have Worksheets("Data") and worksheets named for each year ONLY the
current year and previous years are available, ie Worksheets("2011") will not
exist until it is 2011.

Worksheets("Data") is a summary worksheet. It pulls totals and/or averages
from each year worksheet. Since future year worksheets do not exist until
that year arrives, I have formulas on the Data worksheet that check to see if
that year is here. If not, then "".

Hence, when a new year worksheet is created, it will also place that year,
ie 2010, as the column header, say in cell E8 of Worksheets("Data"). The
cells in column E, below E8 will reference a particular cell on the 2010
worksheet.

My question is this: What is the formula that will use the value in E8, in
this case 2010, to reference E16 on Worksheets("2010")? F8 will eventually
be 2011, etc. and the cells below each will pull data from the appropriate
worksheet based on the value (year) in E8, F8, G8....AH8.

Example:
Worksheets("Data")
E8 = 2010
Instead of formula in E16 being: =IF(E8 = "","",2010!E16)
I want the 2010 portion of the formula to reference E8 to pull the value.

Sorry if wordy, but wanted to make clear as possible.

Thanks for your help,

Les

Try this formula in cell E16:

=IF(E8="","",INDIRECT(E8&"!E16"))

Hope this helps / Lars-Åke


.

I corrected the problem of having " 2010" instead of "2010" and even made
sure the sheet name also reflected "2010".

It finally worked, especially after I corrected the syntax error I made in
the formula. You indicated ....INDIRECT(E8 &"!E16") and I had
INDIRECT(E8 & "!" & E*).

Thanks for your help.
Les

"Lars-Ã…ke Aspelin" wrote:

Make sure that the name of the "2010" worksheet is exactly "2010".
If the name has an extra space, like "2010 " or " 2010" you will get
the #REF error.

Hope this helps / Lars-Ã…ke

On Sat, 15 May 2010 08:09:01 -0700, WLMPilot
wrote:

I tried the formula you indicated and got a #REF

Worksheets("Data") has the following:
E8: 2010 (number format)
E16: =IF(E8="","",Indirect(E8 & "!E16") --- Answer to formula is #REF

Worksheets("2010") has the following:
E16 = 5 (number format)


Any suggestions on this problem?

Thanks,
Les

"Lars-Ã…ke Aspelin" wrote:

On Fri, 14 May 2010 11:51:01 -0700, WLMPilot
wrote:

I have Worksheets("Data") and worksheets named for each year ONLY the
current year and previous years are available, ie Worksheets("2011") will not
exist until it is 2011.

Worksheets("Data") is a summary worksheet. It pulls totals and/or averages
from each year worksheet. Since future year worksheets do not exist until
that year arrives, I have formulas on the Data worksheet that check to see if
that year is here. If not, then "".

Hence, when a new year worksheet is created, it will also place that year,
ie 2010, as the column header, say in cell E8 of Worksheets("Data"). The
cells in column E, below E8 will reference a particular cell on the 2010
worksheet.

My question is this: What is the formula that will use the value in E8, in
this case 2010, to reference E16 on Worksheets("2010")? F8 will eventually
be 2011, etc. and the cells below each will pull data from the appropriate
worksheet based on the value (year) in E8, F8, G8....AH8.

Example:
Worksheets("Data")
E8 = 2010
Instead of formula in E16 being: =IF(E8 = "","",2010!E16)
I want the 2010 portion of the formula to reference E8 to pull the value.

Sorry if wordy, but wanted to make clear as possible.

Thanks for your help,

Les

Try this formula in cell E16:

=IF(E8="","",INDIRECT(E8&"!E16"))

Hope this helps / Lars-Ã…ke


.


.