Prev: Text Functions
Next: conditional count
From: WLMPilot on 16 May 2010 10:01
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
> <WLMPilot(a)discussions.microsoft.com> 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
> >> <WLMPilot(a)discussions.microsoft.com> 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
> >>
> >>
> >> .
> >>
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Text Functions
Next: conditional count