Question description:
This user has given permission to use the problem statement for this
blog.
i have 1 spread sheet that i take out with me on site and fill in as i do site survey
i have a 2nd spreadsheet that i need to populate with the info from spreadsheet 1 but this is a large spreadsheet and has loads of formulas i have done the first column and then thought i could just drag the formula down but it doesn't work any suggestions please?
probably didn't explain that very well
Solved by E. Q. in 39 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
17/09/2018 - 09:21
Hi
Excelchat Expert
17/09/2018 - 09:21
Welcome to Excel Chat.
User
17/09/2018 - 09:22
h
Excelchat Expert
17/09/2018 - 09:22
Can you please share the Excel file to review and update the formulas? Hope, you are fixing the ranges using "$" to make it absolute?
Excelchat Expert
17/09/2018 - 09:24
Please upload the file using paper clip icon next to chat box.
User
17/09/2018 - 09:24
here is the
[Uploaded an Excel file]
User
17/09/2018 - 09:25
this is the one that i need to get the formulas to follow on ?
User
17/09/2018 - 09:25
this is the one that i populate onsite
[Uploaded an Excel file]
Excelchat Expert
17/09/2018 - 09:25
I have opened the file. Please provide me the details further. I would like to highlight few points about poclily meanwhile.
Excelchat Expert
17/09/2018 - 09:26
Before we get started, this is a reminder that our policy is 1 problem per session with additional Q&A on that problem as time allows.
Excelchat Expert
17/09/2018 - 09:27
Let me ask you a couple of quick questions to make sure I fully understand your problem.
Excelchat Expert
17/09/2018 - 09:27
Where is the formula you are referring?
User
17/09/2018 - 09:29
in the led doc in all the separate tabs when i put in a number i want that number to correspond with the main spreadsheet
Excelchat Expert
17/09/2018 - 09:30
Ok. Whether both sheets have same format?
Excelchat Expert
17/09/2018 - 09:30
and same pattern? I mean the cell locations are same?
User
17/09/2018 - 09:31
you will see i have done the first tab but that took ages and i am looking for a way to make it much quicker
User
17/09/2018 - 09:32
yes they are the same locations but on a separate tab and have to add up as i go along
Excelchat Expert
17/09/2018 - 09:32
If they are same, then you can make one perfect sheet.
Excelchat Expert
17/09/2018 - 09:33
with linking formulas.
Excelchat Expert
17/09/2018 - 09:33
And then copy the cells from one sheet and paste it in other sheet.
Excelchat Expert
17/09/2018 - 09:33
Then you can use find and replace option to replace the sheet name in the formulas.
Excelchat Expert
17/09/2018 - 09:33
Then, it updates all the cells.
User
17/09/2018 - 09:34
how does that work find and replace option?
Excelchat Expert
17/09/2018 - 09:34
As an example: If you link RM1 cells to other spread sheet, copy the cells in RM1 and paste it in RM2.
Excelchat Expert
17/09/2018 - 09:35
And use Find and replace.
Excelchat Expert
17/09/2018 - 09:35
Press Ctrl + F
Excelchat Expert
17/09/2018 - 09:35
Click on Replace at the top.
Excelchat Expert
17/09/2018 - 09:35
And In Find What: RM1
Excelchat Expert
17/09/2018 - 09:35
Replace With: RM2.
Excelchat Expert
17/09/2018 - 09:36
So, all formulas will be linked to RM2 sheet.
Excelchat Expert
17/09/2018 - 09:36
Please click on Replace All option.
Excelchat Expert
17/09/2018 - 09:37
.
[Uploaded an Excel file]
Excelchat Expert
17/09/2018 - 09:38
Similar way for other sheets.
User
17/09/2018 - 09:40
this is the formula =+'[LED DOC.xlsx]RM1'!$C$4+'[LED DOC.xlsx]RM2'!$C$4+'[LED DOC.xlsx]RM3'!$C$4+'[LED DOC.xlsx]RM4'!$C$4+'[LED DOC.xlsx]RM5'!$C$4+'[LED DOC.xlsx]RM6'!$C$4+'[LED DOC.xlsx]RM7'!$C$4+'[LED DOC.xlsx]RM8'!$C$4+'[LED DOC.xlsx]RM9'!$C$4+'[LED DOC.xlsx]RM10'!$C$4+'[LED DOC.xlsx]RM11'!$C$4+'[LED DOC.xlsx]RM12'!$C$4+'[LED DOC.xlsx]RM13'!$C$4+'[LED DOC.xlsx]RM14'!$C$4+'[LED DOC.xlsx]RM15'!$C$4+'[LED DOC.xlsx]RM16'!$C$4+'[LED DOC.xlsx]RM17'!$C$4+'[LED DOC.xlsx]Sheet18'!$C$4+'[LED DOC.xlsx]Sheet19'!$C$4+'[LED DOC.xlsx]Sheet20'!$C$4+'[LED DOC.xlsx]Sheet21'!$C$4+'[LED DOC.xlsx]Sheet22'!$C$4+'[LED DOC.xlsx]Sheet23'!$C$4+'[LED DOC.xlsx]Sheet24'!$C$4+'[LED DOC.xlsx]Sheet25'!$C$4+'[LED DOC.xlsx]Sheet26'!$C$4+'[LED DOC.xlsx]Sheet27'!$C$4+'[LED DOC.xlsx]Sheet28'!$C$4+'[LED DOC.xlsx]Sheet29'!$C$4+'[LED DOC.xlsx]Sheet30'!$C$4+'[LED DOC.xlsx]Sheet31'!$C$4+'[LED DOC.xlsx]Sheet32'!$C$4+'[LED DOC.xlsx]Sheet33'!$C$4+'[LED DOC.xlsx]Sheet34'!$C$4+'[LED DOC.xlsx]Sheet35'!$C$4+'[LED DOC.xlsx]Sheet36'!$C$4+'[LED DOC.xlsx]Sheet37'!$C$4+'[LED DOC.xlsx]Sheet38'!$C$4+'[LED DOC.xlsx]Sheet39'!$C$4+'[LED DOC.xlsx]Sheet40'!$C$4+'[LED DOC.xlsx]Sheet41'!$C$4+'[LED DOC.xlsx]Sheet42'!$C$4+'[LED DOC.xlsx]Sheet43'!$C$4+'[LED DOC.xlsx]Sheet44'!$C$4+'[LED DOC.xlsx]Sheet45'!$C$4+'[LED DOC.xlsx]Sheet46'!$C$4+'[LED
User
17/09/2018 - 09:41
as you can see it changes from RM1 through to RM52
Excelchat Expert
17/09/2018 - 09:42
Where is the formula applied? Please let me know the sheet name?
Excelchat Expert
17/09/2018 - 09:42
You can apply 3D formula for this and also, i see you have used "$" for C4
Excelchat Expert
17/09/2018 - 09:43
When you are dragging the formula. Remove $ in the formula.
Excelchat Expert
17/09/2018 - 09:43
You can use find and replace to remove "$" in the formula.
Excelchat Expert
17/09/2018 - 09:43
Find What:"$"
Excelchat Expert
17/09/2018 - 09:44
And replace with: No need to enter anything. Press Replace All.
Excelchat Expert
17/09/2018 - 09:45
Then copy the cell and paste it on other cells.
Excelchat Expert
17/09/2018 - 09:48
=SUM('[LED DOC.xlsx]RM1:Sheet46'!C4)
Excelchat Expert
17/09/2018 - 09:48
Or just apply the above formuila. It is called 3-D formula.
Excelchat Expert
17/09/2018 - 09:48
It takes care from RM1 to Sheet46. It is simple.
Excelchat Expert
17/09/2018 - 09:49
No need to enter intermediate sheets. Excel picks all intermediate sheets.
Excelchat Expert
17/09/2018 - 09:49
=SUM('[LED DOC.xlsx]RM1:Sheet46'!C4)
User
17/09/2018 - 09:51
thank you so much that looks like it has worked thanks again!!
Excelchat Expert
17/09/2018 - 09:51
Great to hear. It is short and sweet formula.
Excelchat Expert
17/09/2018 - 09:52
Please visit Got IT Pro. Have a wonderful day ahead.
Excelchat Expert
17/09/2018 - 09:52
Please end the session and leave a valuable feedback.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
