Reacting to updated information

[tommo]

JAN FEB MAR
DESCRIPTION UNITS COST Current Price
1.5oz Round 12402 1000 7 5 6 7
45ml Hex 8880 1000
4oz Hex 4950 1000
4oz Rd 4800 1000

I am not sure if what i am going to ask is possible but i am hoping it is!!
I have the following table. Basically, i need the current price column to
always be 'current' i.e. picking up the new months data as it is entered. If
i enter a new price in month April, i would like the spreadsheet to
automatically update current price to aprils value. Obviously there is the
really easy manual way of doing this but i am hoping that there is an
automatic way.

Any help would be appreciated

 

[Otto Moehrbach]

Maury
Not sure of what you have and what you want, but here is something you
might be able to use. When you have a table of data and formulas with some
values in them that are subject to change periodically, you can have a cell
or cells over to the side in which you put those values, in your case the
"current values". You then apply a name to each of those cells (Insert -
Name - Define). Then in your data table, formulas too, you can use those
names instead of the values. Then when you want to change the values, you
go to those cells to the side and change the values. Those changes will
then be picked up automatically everywhere those names appear.
For instance, say you have a cell named Mary. In that cells you have some
value. Somewhere else in your workbook, in some cell, you want, say, the
value in Mary. In that cell you would enter "=Mary" without the quotes.
Done. HTH Otto

 

[Max]

Assuming that you would be entering the "month" col labels: Jan, Feb, Mar, ..
in D1 across progressively each month from left to right (the rightmost col
label in D1 across would be taken as the "latest" month)

Array-enter* this in C3 (the first cell under the "Current" label):
=OFFSET(C3,,MAX(($D$1:$IV$1<>"")*(COLUMN($D$1:$IV$1)))-3)
Copy C3 down to return the required results for the "latest" month, eg:
results will be grabbed from the "Mar" col in your example set-up

*To "array-enter" means to press CTRL+SHIFT+ENTER [CSE] in confirming the
formula (instead of just pressing ENTER). If you did this confirmation
correctly, you should see Excel wrap curly braces: { } around the formula in
the formula bar. If you don't see it, click inside the formula bar and try
the CSE again.