Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Paste Functions and Update info
I am having an issue with a worksheet that reads and displyas info from an
SQL view/table. It should read the info and display, then I have a function in the last column that reads the date for each record (row) and VLOOKUP's another worksheet to display a name based on that date (=IF(E469<0, VLOOKUP(I469,'Traveler Color'!$A$3:$B$106,2,FALSE), " ")). As the worksheet is updated, i curntely have the rows moving up or down (depending upon dates, etc.). Then the functions reference the old cells and don't update the correct info. How can i get the functions to be correct. And if the row count fluctuates, I don't get #REF! errors because the fuctions were deleted at the end of the list durring updates? If you can follow that and tell me where I'm going wrong, can you let me know? Thanks -- humanoid/earthling |
#2
|
|||
|
|||
One way to immunize a formula to row deletions & insertions is to use the
offset function. Let's say your formula is in col Z, you'd use the following formula to refer to the same row in cols E & I =IF(OFFSET(Z2,0,-21)<0, VLOOKUP(OFFSET(Z2,0,-18),'Travel Color'!$A$3:$B$106,2,FALSE), " ") "jackle" wrote: I am having an issue with a worksheet that reads and displyas info from an SQL view/table. It should read the info and display, then I have a function in the last column that reads the date for each record (row) and VLOOKUP's another worksheet to display a name based on that date (=IF(E469<0, VLOOKUP(I469,'Traveler Color'!$A$3:$B$106,2,FALSE), " ")). As the worksheet is updated, i curntely have the rows moving up or down (depending upon dates, etc.). Then the functions reference the old cells and don't update the correct info. How can i get the functions to be correct. And if the row count fluctuates, I don't get #REF! errors because the fuctions were deleted at the end of the list durring updates? If you can follow that and tell me where I'm going wrong, can you let me know? Thanks -- humanoid/earthling |
#3
|
|||
|
|||
That looks cool. What would be the best way to keep those functiions strapped
to the cells? As rows get deleted (and moved up) thru the update, we sometimes "run out" of pasted functions and have to add more. It doesn't seem that were have our Data Range Properties set correctly. -- humanoid/earthling "Duke Carey" wrote: One way to immunize a formula to row deletions & insertions is to use the offset function. Let's say your formula is in col Z, you'd use the following formula to refer to the same row in cols E & I =IF(OFFSET(Z2,0,-21)<0, VLOOKUP(OFFSET(Z2,0,-18),'Travel Color'!$A$3:$B$106,2,FALSE), " ") "jackle" wrote: I am having an issue with a worksheet that reads and displyas info from an SQL view/table. It should read the info and display, then I have a function in the last column that reads the date for each record (row) and VLOOKUP's another worksheet to display a name based on that date (=IF(E469<0, VLOOKUP(I469,'Traveler Color'!$A$3:$B$106,2,FALSE), " ")). As the worksheet is updated, i curntely have the rows moving up or down (depending upon dates, etc.). Then the functions reference the old cells and don't update the correct info. How can i get the functions to be correct. And if the row count fluctuates, I don't get #REF! errors because the fuctions were deleted at the end of the list durring updates? If you can follow that and tell me where I'm going wrong, can you let me know? Thanks -- humanoid/earthling |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to update functions in excel sheet? | Excel Worksheet Functions | |||
Multiple worksheet queries | Excel Worksheet Functions | |||
Timestamp cell to left after update | Excel Discussion (Misc queries) |