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

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

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