Was VLOOKUP the wrong choice?

[magmike]

I am looking for strategy advice. Excel is not what I do for a living, but it is certainly a big help in what I do. When I discovered VLOOKUP, I was elated, because it replaced my insanely long IF Statements. So I used it ferociously in my sales tracker. As I put in what I sold, using VLOOKUP, it easily calculated each category and the total of the sale. But rates change, and now that I go to edit my rate tables, I realize that my data from the past couple of years will change too. Now I fear the only solution is copying and pasting as values, all of the past data to keep it true before I update my tables.

Is there a better fix now and what would be a better strategy moving forward, planning for the next rate change?

Thanks in advance for your help,
magmike

 

[Claus Busch]

Hi Mike,

Am Tue, 23 Dec 2014 08:27:04 -0800 (PST) schrieb magmike:

I am looking for strategy advice. Excel is not what I do for a living, but it is certainly a big help in what I do. When I discovered VLOOKUP, I was elated, because it replaced my insanely long IF Statements. So I used it ferociously in my sales tracker. As I put in what I sold, using VLOOKUP, it easily calculated each category and the total of the sale. But rates change, and now that I go to edit my rate tables, I realize that my data from the past couple of years will change too. Now I fear the only solution is copying and pasting as values, all of the past data to keep it true before I update my tables.

VLOOKUP is better than nested IFs.
But before you change rates in rate table copy your existing data and
paste it back as values.
I guess that this is the easiest and fastest way to handle this
changing.


Regards
Claus B.

 

[Claus Busch]

Hi Mike,

Am Tue, 23 Dec 2014 17:36:43 +0100 schrieb Claus Busch:

I guess that this is the easiest and fastest way to handle this
changing.

or you create a new rate table in another range and refer from now on to
the new range with your VLOOKUP


Regards
Claus B.

 

[magmike]

Hi Mike,

Am Tue, 23 Dec 2014 17:36:43 +0100 schrieb Claus Busch:


or you create a new rate table in another range and refer from now on to
the new range with your VLOOKUP


Regards
Claus B.

Brilliant!

 

[GS]

I agree with Claus' 2nd suggestion as that how I handle this. It allows
you to preserve prior rates/calcs as those rate change over time. Using
a 'rates table' makes ongoing maintenance very easy!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Claus Busch]

Hi Mike, hi Garry,

Am Tue, 23 Dec 2014 12:43:38 -0500 schrieb GS:

I agree with Claus' 2nd suggestion as that how I handle this. It allows
you to preserve prior rates/calcs as those rate change over time. Using
a 'rates table' makes ongoing maintenance very easy!

but I would insert 1 or 2 blank rows between old and new data that there
will be no problem if someone clicks double on a fill handle.


Regards
Claus B.

 

[magmike]

Hi Mike, hi Garry,

Am Tue, 23 Dec 2014 12:43:38 -0500 schrieb GS:


but I would insert 1 or 2 blank rows between old and new data that there
will be no problem if someone clicks double on a fill handle.


Regards
Claus B.

I have decided to duplicate the sheet my tables are on, when there are changes. That way, the old references still lead to the same place, and in subsequent rows (a template row is inserted when new records are entered) the new one, I will just have change the name of the sheet the reference is on in the formulas.

Example
VLOOKUP(C5,Rates!DataRates,2,FALSE)

Becomes
VLOOKUP(C5,Rates2!DataRates,2,FALSE)

 

[GS]

I have decided to duplicate the sheet my tables are on, when there
are changes. That way, the old references still lead to the same
place, and in subsequent rows (a template row is inserted when new
records are entered) the new one, I will just have change the name of
the sheet the reference is on in the formulas.

Example
VLOOKUP(C5,Rates!DataRates,2,FALSE)

Becomes
VLOOKUP(C5,Rates2!DataRates,2,FALSE)

Well.., I use a hidden lookup table at the top of the sheet using the
rates on that table. I use this for variable rates depending on values
in other cols.

Alternatively, I'll use a 'Rate' col that automajically refs the cell
above so it carries down until a hard value is entered...

D2: 5%
D3: =LastCell
..and so on
D20: 7% (new rate replaces formula)
D21: =LastCell

...so the only maintenance required is when/where the new rate gets
inserted. All other cell formulas ref their respective rate
accordingly, by row. This persists on-the-fly rate changes as they
occur, and obviates the need for a lookup rates table.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

I failed to mention that 'LastCell' is a local scope defined name that
is fully relative to where used, whereby it refs the cell above...

Active cell is A2:
name: "Sheet1!LastCell"
refersto: "=A1"

There's others as well...

LastCellL (cell left of cell above)
LastCellR (cell right of cell above)
NextCell (cell below)
NextCellL (cell left of cell below)
NextCellR (cell right of cell below)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion