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) wrote in message oups.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 wrote in message oups.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!! |
All times are GMT +1. The time now is 01:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com