Excel - COLUMN Function Problem - Expert Solution

Question description:
This user has given permission to use the problem statement for this blog.

a formula for excel to find the last cell of data in a variable column and to then add data beneath this
Solved by S. H. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user information.
Excelchat Expert 23/03/2018 - 11:31
Hello, I understand that you need help with finding the last cell in a column with data.
Excelchat Expert 23/03/2018 - 11:31
Before we proceed, this is a reminder that our policy is 1 question per session so for this session we'll be resolving this particular question. We also currently do not support VBA/Macro solutions.
User 23/03/2018 - 11:32
yes, so basically i want to extract data fro multiple different spreadshet sand to copy this into a central workbook
User 23/03/2018 - 11:32
howeevr, as the data in the other sheets is variable, I don't know how to be able to pull this all in into one sheet
Excelchat Expert 23/03/2018 - 11:32
Can you give me a sample data to analyze so that I can see if it can be done via formulas alone?
User 23/03/2018 - 11:34
so i would like all the number (1, 2) in sheet 2 and 3 to appear in number column A in sheet 1
User 23/03/2018 - 11:34
and england and wales to be pulled into column B
User 23/03/2018 - 11:34
however, sheet 2 and 3 will be constantly changing
Excelchat Expert 23/03/2018 - 11:34
Can you complete Sheet1 please or press Enter? It is still being edited so I can't see it.
User 23/03/2018 - 11:35
Excelchat Expert 23/03/2018 - 11:35
Okay, what should the output look like in Sheet1?
User 23/03/2018 - 11:35
column A in sheet 2 and 3 should be pulled into column A sheet 1
User 23/03/2018 - 11:36
column B sheet 2 and 3 should be pulled into coolumn B sheet 1
User 23/03/2018 - 11:36
however, as I said, the data in sheet 2 and 3 is goign to be changing on a regular basis
Excelchat Expert 23/03/2018 - 11:37
The data in Sheet2 and 3 ends when there is a blank row, right? I meant there will never be a blank row in between them so we can be sure that once a blank row is found, it is the end of the record?
User 23/03/2018 - 11:38
Excelchat Expert 23/03/2018 - 11:40
Okay, I got to be honest with you. This is much easier with a VBA solution but it may be possible using Formulas. I'm just not sure if that's what you are looking for since solving this using formulas can result to a very slow consolidated view dependin on the number of records and columns.
Excelchat Expert 23/03/2018 - 11:40
Will it be different files as well or the same file but different sheets?
User 23/03/2018 - 11:40
different files
User 23/03/2018 - 11:40
hmm i thought VBA code might be the best answer...
Excelchat Expert 23/03/2018 - 11:41
If they are of different files then I'm afraid VBA is our only viable solution.
Excelchat Expert 23/03/2018 - 11:44
The only other way I can think of to solve this without VBA is to use the Consolidate feature of Excel but it won't be automated.
User 23/03/2018 - 11:44
Excelchat Expert 23/03/2018 - 11:45
I'm not even sure if that's exactly what you need but honestly, since the data will be located in different files, your best bet is really to use VBA.
Excelchat Expert 23/03/2018 - 11:45
Solving this with VBA would be easy but I'm afraid that we currently do not support VBA solutions. I think it's due to security reasons so even if I want to, I can't.
User 23/03/2018 - 11:46
ah dammmm. that's a shame
User 23/03/2018 - 11:46
thanks for your help anywy
Excelchat Expert 23/03/2018 - 11:47
There are a couple of resources you can go to as there are a lot of general VBA code to do what you need so try searching for "consolidate data from different workbooks VBA code"
Excelchat Expert 23/03/2018 - 11:47
It is against policy to send a URL link so I'll have to ask you search for that instead. I sincerely hope you understand.
Excelchat Expert 23/03/2018 - 11:48
If you want, you can contact us again in a few days and we may be supporting VBA/Macro solutions then. Any of our VBA experts can easily help you with this.
Excelchat Expert 23/03/2018 - 11:49
I'll be rating you 5 stars so our group of experts know that you are a good client and to take good care of you should you contact us again.
User 23/03/2018 - 11:49
that's fantastc
User 23/03/2018 - 11:49
sounds good
Excelchat Expert 23/03/2018 - 11:49
That's the best I can do for you for now, I really hope you understand. Thank you.
Excelchat Expert 23/03/2018 - 11:50
Our session is about to expire. Thank you for contacting Got It Pro. Have an awesome day!

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