updating links from another spreadsheet

%3CLINGO-SUB%20id%3D%22lingo-sub-1811161%22%20slang%3D%22en-US%22%3Eupdating%20links%20from%20another%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1811161%22%20slang%3D%22en-US%22%3E%3CP%3EHI%20all%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20a%20still%20a%20newbie%20in%20excel%20and%20i%20am%20trying%20to%20do%20something%20simple%20but%20its%20not%20working%20as%20i%20want%20it%20to.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%202%20Employees%20spreadsheets%2C%20the%20basic%20and%20the%20detailed.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ebasic%26nbsp%3B%3C%2FP%3E%3CP%3EPeter%20(Cell%20A3)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Edetailed%20spreadsheet%20linked%20to%20basic%3C%2FP%3E%3CP%3EPeter%20(%3D%5Bbasic.xlsx%5DSheet1!%24A%243)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ei%20want%20to%20click%20this%20formula%20and%20drag%20it%20down%20so%20that%20the%20next%20cell%20number%204%20becomes%26nbsp%3B(%3D%5Bbasic.xlsx%5DSheet1!%24A%244)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHowever%20its%20not%2C%20when%20i%20drag%20it%20down%20the%20formula%20remains%26nbsp%3B(%3D%5Bbasic.xlsx%5DSheet1!%24A%243).%20I%20have%20to%20stay%20typing%204%20manually.%20With%20a%20large%20spreadsheet%20this%20is%20very%20tedious.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20to%20drag%20the%20cell%20and%20update%20the%20cells%20below%20it%20to%20the%20correct%20row%20number%20%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20help%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1811161%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1811236%22%20slang%3D%22de-DE%22%3ESubject%3A%20updating%20links%20from%20another%20spreadsheet%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1811236%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F441794%22%20target%3D%22_blank%22%3E%40KrisCachia%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20your%20permission%2C%20if%20I%20can%20recommend%20you%2C%20add%20a%20file%20(without%20sensitive%20data)%20to%20your%20project.%3C%2FP%3E%3CP%3EExplain%20your%20plans%20in%20relation%20to%20this%20file.%20So%20you%20can%20get%20a%20solution%20that%20is%20tailored%20to%20your%20needs%20much%20faster.%3C%2FP%3E%3CP%3EAt%20the%20same%20time%2C%20it%20is%20much%20easier%20for%20someone%20who%20wants%20to%20help%20understand%20the%20subject.%3C%2FP%3E%3CP%3EA%20win-win%20situation%20for%20everyone.%3C%2FP%3E%3CP%3EPlease%20no%20Picture%2C%20even%20if%20it%20is%20said%20that%20a%20picture%20can%20say%20a%20thousand%20words%2C%20it%20is%20certainly%20not%20in%20the%20case%20of%20Excel%2C%20on%20the%20contrary%20in%20some%20cases.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Knowing%20the%20Excel%20version%20and%20operating%20system%20would%20be%20an%20advantage.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20for%20your%20understanding%20and%20patience%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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

 

 

 

 

 

 

 

 

 

 

3 Replies
Highlighted

@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)

Highlighted
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
Highlighted
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)