Excel - COLUMN Function Problem - Expert Solution

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
Excelchat Expert 17/09/2018 - 09:21
Welcome to Excel Chat.
User 17/09/2018 - 09:22
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.

Get instant expert help with Excel and Google Sheets

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.

Click here to get your free Excelchat help session

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
The Allstate Corporation
United Parcel Service
Dell Inc