Using a formula to create named range reference

Is it possible to use a formula to create a reference to a variable
named range? For example, I want a given cell to refer to a named
range. However, the specific named range it would reference is
dependent on today's date. I have named ranges for each weekday, i.e.
Data2, Data3, Data4, Data5, Data6.

In the cell which will reference these ranges, I want to create the
reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
docs to describe how to accomplish this.

TIA,
Rose

something like

=IF(INDIRECT("Data"&WEEKDAY(TODAY()))>100, "Yes","No")

--

HTH

RP
(remove nothere from the email address if mailing direct)


<rgb65@bellsouth.net> wrote in message
news:1120069674.779522.209140@z14g2000cwz.googlegroups.com...
> Is it possible to use a formula to create a reference to a variable
> named range? For example, I want a given cell to refer to a named
> range. However, the specific named range it would reference is
> dependent on today's date. I have named ranges for each weekday, i.e.
> Data2, Data3, Data4, Data5, Data6.
>
> In the cell which will reference these ranges, I want to create the
> reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
> docs to describe how to accomplish this.
>
> TIA,
> Rose
>

Hi!

I think this is what you're looking for:

INDIRECT("data"&WEEKDAY(TODAY()))

So that:

=SUM(INDIRECT("data"&WEEKDAY(TODAY())))

would Sum the named range Data4

However, if Data4 is a DYNAMIC range this will not work.

Biff

<rgb65@bellsouth.net> wrote in message
news:1120069674.779522.209140@z14g2000cwz.googlegroups.com...
> Is it possible to use a formula to create a reference to a variable
> named range? For example, I want a given cell to refer to a named
> range. However, the specific named range it would reference is
> dependent on today's date. I have named ranges for each weekday, i.e.
> Data2, Data3, Data4, Data5, Data6.
>
> In the cell which will reference these ranges, I want to create the
> reference to the range as "Data"+ WEEKDAY(TODAY()). I can't find any
> docs to describe how to accomplish this.
>
> TIA,
> Rose
>

Bob Phillips wrote:
> something like
>
> =IF(INDIRECT("Data"&WEEKDAY(TODAY()))>100, "Yes","No")
>
Thanks a bunch, Bob!! I actually was trying to get the contents of
the named range to appear so I did

=INDIRECT("Data"&WEEKDAY(TODAY()))

and it worked like a charm. Thanks a MILL! I am just a casual user
of Excel so I never would've figured this out on my own.

(on the 8th day, Dog created newsgroups...)

Rose

Biff wrote:

> Hi!
>
> I think this is what you're looking for:
>
> INDIRECT("data"&WEEKDAY(TODAY()))

Yup - that's it exactly. Thanks!!