Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Moving Data on opening Excel
Hi there- trying to create a spreadsheet to track drug prices for prescribing
use. I am using 2 columns- one to be blank to enter the currents day's prices, and another one to show the previous day's prices. 2 questions.... 1) Is there any way to set something up so that when I start the program, the data from the previous day's "current" column transfers itself to the "previous" column to give me a clear colum to enter the new day's current prices??? 2) Is there a function or way of retaining the minimum value EVER reached from these sets of values being entered Basically i want to enter the prices every day, but achieve a column that records the "best price" ever reached. I do not want to be building up the price change data from each days prices, as the sheet will become massive with some 150 drugs potentially changing price every day Hope this makes sense |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Moving Data on opening Excel
Hi
Just to answer a few of your questions, and provide a possible direction. Using Programming Code (VBA) a macro is Created in the Worksheet_Open event to transfer yesterdays data into the required column (Col A) and (Col B) is cleared for todays input - very possible and not too hard! To retain the minimum value that that product has ever reached would require data (in some form). The following is just a suggestion in a workbook on sheet1 = Daily Task sheet ColA = ProductName(or however you want to Identify the product) ColB = YesterdaysPrice ColC = CurrentPrice ColE = LowestPriceToDate On opening the workbook, ColC is Pasted to ColB and the macro compares the yesterdays Price to the Lowest Price, If Lower updates the Lowest Price , else leaves it as is. Hope this answers your questions "Rainbow-Lentil" wrote: Hi there- trying to create a spreadsheet to track drug prices for prescribing use. I am using 2 columns- one to be blank to enter the currents day's prices, and another one to show the previous day's prices. 2 questions.... 1) Is there any way to set something up so that when I start the program, the data from the previous day's "current" column transfers itself to the "previous" column to give me a clear colum to enter the new day's current prices??? 2) Is there a function or way of retaining the minimum value EVER reached from these sets of values being entered Basically i want to enter the prices every day, but achieve a column that records the "best price" ever reached. I do not want to be building up the price change data from each days prices, as the sheet will become massive with some 150 drugs potentially changing price every day Hope this makes sense |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Moving Data on opening Excel
Could anyone please help me with the code to do this at all?????
Many thanks Justin "steve_doc" wrote: Hi Just to answer a few of your questions, and provide a possible direction. Using Programming Code (VBA) a macro is Created in the Worksheet_Open event to transfer yesterdays data into the required column (Col A) and (Col B) is cleared for todays input - very possible and not too hard! To retain the minimum value that that product has ever reached would require data (in some form). The following is just a suggestion in a workbook on sheet1 = Daily Task sheet ColA = ProductName(or however you want to Identify the product) ColB = YesterdaysPrice ColC = CurrentPrice ColE = LowestPriceToDate On opening the workbook, ColC is Pasted to ColB and the macro compares the yesterdays Price to the Lowest Price, If Lower updates the Lowest Price , else leaves it as is. Hope this answers your questions "Rainbow-Lentil" wrote: Hi there- trying to create a spreadsheet to track drug prices for prescribing use. I am using 2 columns- one to be blank to enter the currents day's prices, and another one to show the previous day's prices. 2 questions.... 1) Is there any way to set something up so that when I start the program, the data from the previous day's "current" column transfers itself to the "previous" column to give me a clear colum to enter the new day's current prices??? 2) Is there a function or way of retaining the minimum value EVER reached from these sets of values being entered Basically i want to enter the prices every day, but achieve a column that records the "best price" ever reached. I do not want to be building up the price change data from each days prices, as the sheet will become massive with some 150 drugs potentially changing price every day Hope this makes sense |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Moving Data on opening Excel
Hi
The following code assumes that Sheet1 contains the data, and that Row 1 is a header row. It will copy the data from column C (current data) to column B (previous day's data. Historical low values for each product are held in column E, and will be updated if yesterday's values are lower, by using column F as a temporary column to compare data. Adjust sheet names and column letters to suit. Sub Auto_open() Application.ScreenUpdating = False With ActiveWorkbook.Sheets("Sheet1") '<=== Change Dim lr As Long lr = Cells(Rows.Count, 1).End(xlUp).Row Columns("C:C").Copy Columns("B:B") Application.CutCopyMode = False Range("F2").Activate ActiveCell.FormulaR1C1 = "=MIN(RC[-1],RC[-4])" Selection.AutoFill Destination:=Range("F2:F" & lr), Type:=xlFillDefault Range("F2:F" & lr).Copy Range("E2").Activate Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("F2:F" & lr).ClearContents Range("C2:C" & lr).ClearContents Range("C2").Activate End With Application.ScreenUpdating = True End Sub You can copy the code and paste it into your Visual Basic Editor (VBE) in a Standard Module located in your file. To do this, Alt + F11 (open VBE) Ctrl + R (open Project Explorer) Select the file name on the left Insert Module Paste code in Module Because the macro is called Auto_open, it will run automatically when you open the workbook. If you want to create a shortcut, then ToolsMacrosSelect the macroOptionsenter a key to use with CtrlOK If you are new to entering macros, then David McRitchie has lots of useful help on his site at http://www.mvps.org/dmcritchie/excel/install.htm http://www.mvps.org/dmcritchie/excel/getstarted.htm -- Regards Roger Govier "Rainbow-Lentil" wrote in message ... Could anyone please help me with the code to do this at all????? Many thanks Justin "steve_doc" wrote: Hi Just to answer a few of your questions, and provide a possible direction. Using Programming Code (VBA) a macro is Created in the Worksheet_Open event to transfer yesterdays data into the required column (Col A) and (Col B) is cleared for todays input - very possible and not too hard! To retain the minimum value that that product has ever reached would require data (in some form). The following is just a suggestion in a workbook on sheet1 = Daily Task sheet ColA = ProductName(or however you want to Identify the product) ColB = YesterdaysPrice ColC = CurrentPrice ColE = LowestPriceToDate On opening the workbook, ColC is Pasted to ColB and the macro compares the yesterdays Price to the Lowest Price, If Lower updates the Lowest Price , else leaves it as is. Hope this answers your questions "Rainbow-Lentil" wrote: Hi there- trying to create a spreadsheet to track drug prices for prescribing use. I am using 2 columns- one to be blank to enter the currents day's prices, and another one to show the previous day's prices. 2 questions.... 1) Is there any way to set something up so that when I start the program, the data from the previous day's "current" column transfers itself to the "previous" column to give me a clear colum to enter the new day's current prices??? 2) Is there a function or way of retaining the minimum value EVER reached from these sets of values being entered Basically i want to enter the prices every day, but achieve a column that records the "best price" ever reached. I do not want to be building up the price change data from each days prices, as the sheet will become massive with some 150 drugs potentially changing price every day Hope this makes sense |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moving a line chart data point revises data table value in Excel ' | Charts and Charting in Excel | |||
Moving data from one excel file to another | New Users to Excel | |||
Moving Data from a Web Browser to Excel | Excel Discussion (Misc queries) | |||
Moving data between Excel sheets | Excel Discussion (Misc queries) | |||
moving data in excel without deleting existing data | Excel Discussion (Misc queries) |