Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reacting to updated information
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reacting to updated information
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 "tommo" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reacting to updated information
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. -- Max Singapore http://savefile.com/projects/236895 xdemechanik --- "tommo" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
links that cannot be updated | Excel Discussion (Misc queries) | |||
Links Cannot Be Updated | Excel Discussion (Misc queries) | |||
updated row comes at the top | Excel Discussion (Misc queries) | |||
Updated cells | Excel Worksheet Functions | |||
Conditional Format -- shading ten fields in a row reacting to colu | Excel Discussion (Misc queries) |