If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updating a column only on specific date
I'm realitivly new to excel and this seems like a simple problem but I
am not figuring it out. I have columns that correspond to days in a month and on the curent date I need to enter the data from a column that holds the month to date data. I dont want that data to change when the column date is less that the current date. this is what I have so far, it works, but gives an error message about circular referances when I open the workbook. =IF(H$3=$D$2,$G4,H4) H3:AB3 = dates in the month D2 = Today G4:G19 = values that update daily from another workbook H4:AB19 = cell where I want to record daily values to view a history of values. I'm thinking I may need to run a script to do this but I run into simular problems. Thanks in advance for the help. Jeffery Attachment filename: dashtest2.xls Download attachment: http://www.excelforum.com/attachment.php?postid=544910 --- Message posted from http://www.ExcelForum.com/ |
#2
|
|||
|
|||
Updating a column only on specific date
the reference to H4 in your formula is the circular refrence. What your
formula says is: If the date on the top of the column is equal to today's date, then put the value at the beginning of the row here, otherswise put the value that is already here here. So, yes, it reads funny in english, becuase it doesn't make sense. The functionality you're looking for is exactly the opposite of what formulas are meant to do. Formulas recalulate their results any time a value in a referenced cell changes. In you're case, it almost sounds like you'd like the calulation to occur only if the formula is in a column that matches today's date. The way to rectify this is to a) make sure to use something other than $G4 so that it resolves to something static for dates prior to "today" b) after you open the spreadsheet and the formula resolves for cells where the date is today, copy the cell and paste-special-values over top of it. c) use a macro to find the cells with today's date, and then enter the results of your formula. "jsollman " wrote in message ... I'm realitivly new to excel and this seems like a simple problem but I am not figuring it out. I have columns that correspond to days in a month and on the curent date I need to enter the data from a column that holds the month to date data. I dont want that data to change when the column date is less that the current date. this is what I have so far, it works, but gives an error message about circular referances when I open the workbook. =IF(H$3=$D$2,$G4,H4) H3:AB3 = dates in the month D2 = Today G4:G19 = values that update daily from another workbook H4:AB19 = cell where I want to record daily values to view a history of values. I'm thinking I may need to run a script to do this but I run into simular problems. Thanks in advance for the help. Jeffery Attachment filename: dashtest2.xls Download attachment: http://www.excelforum.com/attachment.php?postid=544910 --- Message posted from http://www.ExcelForum.com/ |
Thread Tools | |
Display Modes | |
|
|