building formulas that change frequently using named cell ranges

  • Thread starter Thread starter Sheldon
  • Start date Start date
S

Sheldon

Basically, I've got a spreadsheet that has a range specified for each day of
the year. The formula is looking to subtract a previous date (2 weeks ago -
Nov 252007) from the latest range (today's date - Dec042007). **Dec042007 and
Nov252007 are named cell references**

As it stands now, I've got to do a find and replace each day so that the
data is current. I'd like to have a cell where I'd like to change daily that
would automatically change the formulas to the correct ranges. While the
find and replace works fine, it isn't as efficient as it could be.

Is this type of process possible? Can a formula be built off a table?

Thanks in advance.
 
If I understand you correctly, you have at least 365 named ranges.
I can practically guarantee there's a better approach.

Can you describe how you use those ranges?
Then we'll see if we can come with a good solution for you.
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
well, it's actually for sports stats. I download the stats each day to a
worksheet, and name the range with that date. Then on a separate worksheet,
I subtract a previous date (I want the freedom to select any date previous)
from today's date. My ultimate goal is to be able to come up with a list of
how many points a player has for any specified range during the year....
 
Without a representation of your actual data structure, I can only offer
these generic suggestions:

If your data list has dates across the top row and player names down the
left side, you could probably use a combination of INDEX and MATCH functions
that isolate date ranges for a player and calculate on the corresponding
values.

If you could make a sample Excel file of your structure available, we can
probably tailor a solution for you.

Some free file hosting websites that could be used:
http://www.flypicture.com/
http://cjoint.com/index.php
http://www.savefile.com/index.php
--------------------------

Regards,

Ron
Microsoft MVP (Excel)
(XL2003, Win XP)
 
Sheldon
Based on Ron Coderre's description of your data structure I offer the
following solution:
cell A1 = a date of your choice [but less than B1]
cell B1 = a date [possibly =TODAY()]
cells A2:P2 contain range of dates
cells A3:P3 contain any points awarded
formula in Q2 i
=(SUM(INDIRECT(ADDRESS(ROW(),MATCH($A$1,$A$2:$P$2,0),2)&":"&ADDRESS(ROW(),MATCH($B$1,$A$2:$P$2,0),2)))) - this can be copied down if required.
This will 'sum' the points between the dates in A1 & B1
Changing the dates in A1 & B1 will change the range of your 'sum'.
Hope this may help
Regards
Ron@Buy
 
Back
Top