building formulas that change frequently using named cell ranges
 

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.

building formulas that change frequently using named cell ranges
 

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)



"Sheldon" wrote in message
...
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.

building formulas that change frequently using named cell rang
 

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....

"Ron Coderre" wrote:

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)



"Sheldon" wrote in message
...
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.

building formulas that change frequently using named cell rang
 

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" wrote in message
...
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....

"Ron Coderre" wrote:

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)



"Sheldon" wrote in message
...
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.

building formulas that change frequently using named cell rang
 

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 is
=(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

"Ron Coderre" wrote:

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" wrote in message
...
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....

"Ron Coderre" wrote:

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)



"Sheldon" wrote in message
...
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.