let's say there is a constant value, $/hr or price of an item. so every line
number has a column to multiply by this value. once or twice a year, this
value may change.

do you just hide a column with that value replicated down until it needs to
be changed?

example, every line item is a day of the year. somebody makes $10 per hour.
sometime during the year, they get a raise and from then on the value in the
hours column needs to be multipled by the new rate.

what's the best way?

--


Gary

I'd keep a table, probably on a separate sheet (or at least in separate
columns) that had an effective date and hourly rate. Suppose this is in
Sheet2, columns A and B.
Then your daily table would have date, and hours in columns A and B. In
column C calculate the 'cost' as =B2*vlookup(a2,Sheet2!A:B,2). This would
calculate the earnings based on the hours and date in row 2, and you could
copy this formula down through each row of the table.
My two cents. --Bruce


"Gary Keramidas" wrote:

let's say there is a constant value, $/hr or price of an item. so every line
number has a column to multiply by this value. once or twice a year, this
value may change.

do you just hide a column with that value replicated down until it needs to
be changed?

example, every line item is a day of the year. somebody makes $10 per hour.
sometime during the year, they get a raise and from then on the value in the
hours column needs to be multipled by the new rate.

what's the best way?

--


Gary

Define a name to hold the value (using Insert Name Define). The name
could either contain the constant itself, or refer to a cell that contains
the constant.

Then in your formula use the name in place of the value.




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
let's say there is a constant value, $/hr or price of an item. so every
line number has a column to multiply by this value. once or twice a year,
this value may change.

do you just hide a column with that value replicated down until it needs
to be changed?

example, every line item is a day of the year. somebody makes $10 per
hour. sometime during the year, they get a raise and from then on the
value in the hours column needs to be multipled by the new rate.

what's the best way?

--


Gary

but when i change the value in that cell, it will recalculate the previous
entries with the new value. that doesn't help.

it's easier to do what i did, just put the value in the first cell, use =a1
in the next cell and replicate it. when the value changes somewhere down the
line, just change the value on that day and the formulas after that day will
have the new value.

--


Gary


"Jezebel" wrote in message
...
Define a name to hold the value (using Insert Name Define). The name
could either contain the constant itself, or refer to a cell that contains
the constant.

Then in your formula use the name in place of the value.




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
let's say there is a constant value, $/hr or price of an item. so every
line number has a column to multiply by this value. once or twice a year,
this value may change.

do you just hide a column with that value replicated down until it needs
to be changed?

example, every line item is a day of the year. somebody makes $10 per
hour. sometime during the year, they get a raise and from then on the
value in the hours column needs to be multipled by the new rate.

what's the best way?

--


Gary

Gary
I have had the same problems in the past and never really found a
satifactory answer to it. I found myself resorting to VLOOKUPs and
"pasteing special values" on top of their own formulas. Not only were
people's pay rates changing but the taxman would change his scales too.

What I am going to do now is calculate this information with a VBA
procedure. So now I do not have to worry about parameters changing.
There are no formulae and I can build conditions in. I havn't needed to
do this yet. But I have done similar things and feel pretty confident
that it will work a treat.

Hope that helps some..... It was great to see a question that I
understood.

Geoff