Forum Discussion

Copper Contributor

Aug 18, 2021

Links within xls

Hello. I use a simple link within a workbook, pulling data from one cell within a sheet to another sheet in the same workbook. I use two $ either side of the link (eg =SHEET1!$F$400). I am addin...

excel

Riny_van_Eekelen

Platinum Contributor

Aug 18, 2021

walmsb So it seems that you always want to pick-up whatever is in the last cell in column F on Sheet2. Correct? If so, the following formula will do just that:

=LOOKUP(2,1/(Sheet2!F:F<>""),Sheet2!F:F)

More about how that works in the link below:

https://exceljet.net/formula/get-value-of-last-non-empty-cell

walmsb

Copper Contributor

Aug 18, 2021

Hi thanks for spending the time to reply. I should have explained better, it is not the last cell. I have multiple links within the Sheet 1 and am trying to feed them through to Sheet 2. But when I add rows into Sheet 1, it fails to update the link.

Riny_van_Eekelen
Platinum Contributor
Aug 18, 2021
walmsb Sorry! Misread the sheet names, but that doesn't matter. Can you upload a file demonstrating what you are dealing with.
- walmsb
  Copper Contributor
  Aug 18, 2021
  Riny_van_Eekelen
  Link.xlsx10 KB
  - Riny_van_Eekelen
    Platinum Contributor
    Aug 18, 2021
    walmsbThanks! Now explain what you do with it. You linked two cells in Sheet2 to cells in Sheet1, column E based on a matching description in column A on both sheets. The Asset Manager in Sheet 2 is now linked to a date for the first Asset Manager in Sheet1. Should you want to have that automatically updated to the date belonging to the last "Asset Manager" in Sheet1, the one in row 14?
    
    If that is the case, see attached. Sheet2, column D contains a similar formula as the one I suggested earlier.
    Link.xlsx11 KB