Forum Discussion

KrisCachia's avatar
KrisCachia
Copper Contributor
Oct 22, 2020

updating links from another spreadsheet

HI all, 

 

I am a still a newbie in excel and i am trying to do something simple but its not working as i want it to. 

 

I have 2 Employees spreadsheets, the basic and the detailed. the idea is to have users access the basic spreadsheet and management access the detailed spreadsheet with other confidential data. So I want rows that are added in basic to automatically be filled in the detailed spreadsheet so that common data isn't entered twice. 

 

basic 

Peter (Cell A3)

 

detailed spreadsheet linked to basic

Peter (=[basic.xlsx]Sheet1!$A$3)

 

i want to click this formula and drag it down so that the next cell number 4 becomes (=[basic.xlsx]Sheet1!$A$4)

 

However its not, when i drag it down the formula remains (=[basic.xlsx]Sheet1!$A$3). I have to stay typing 4 manually. With a large spreadsheet this is very tedious. 

 

Is there a way to drag the cell and update the cells below it to the correct row number ?

 

Appreciate any help 🙂

 

 

 

 

 

 

 

 

 

 

excel
Formulas and Functions

3 Replies

  • KrisCachia's avatar
    KrisCachia
    Copper Contributor
    Oct 23, 2020
    found the solution need to remove the $ before the row number

    modified formula as follows:
    from
    =[basic.xlsx]Sheet1!$A$3

    to
    =[basic.xlsx]Sheet1!$A3

    then when dragged down the formula all the row's formulas were properly updated to A4, A5, A6 respectively.

    Problem solved 🙂
    • NikolinoDE's avatar
      NikolinoDE
      Platinum Contributor
      Oct 23, 2020
      I was pleased that you were able to solve the problem yourself.
      For further questions I am gladly at your disposal.

      Have a nice day / night.

      Nikolino
      I know I don't know anything (Socrates)
  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Oct 23, 2020

    KrisCachia 

     

    With your permission, if I can recommend you, add a file (without sensitive data) to your project.

    Explain your plans in relation to this file. So you can get a solution that is tailored to your needs much faster.

    At the same time, it is much easier for someone who wants to help to understand the subject.

    A win-win situation for everyone.

    Please no Picture, even if it is said that a picture can say a thousand words, it is certainly not in the case of Excel, on the contrary in some cases.

     

    * Knowing the Excel version and operating system would also be an advantage.

     

    Thank you for your understanding and patience

     

     

    Nikolino

     

    I know I don't know anything (Socrates)