Extracting data onto 1 sheet depending on the given sheet name

It would help if you attached a sample Excel workbook.

To do this, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post. Don't try to use the Paperclip icon, as it doesn't work on this forum.

Hope this helps.

Pete

Hi

Please help us to help you.

How much data do you have on each of the sheets? Better, what is the range of data in each of the sheets (A, B, C, D, ...)?

Will you use the DATA sheet for other calculations or is it just to receive the data from the other sheets?

Would it be easier if in every sheet in cell A1 I insert the unique codes in there to represent their sheets. E.g. Sheet A | Cell A1 = Customer A Sheet B | Cell A1 = Customer B

It will be unlimited data From cell A to cell I.
And yes I will be using this data for other calculations.

A picture of a workbook is not much use - please attach the actual workbook, using the procedure that I described above.

Pete

Hi

Use INDIRECT function In DATA sheet

I suppose that with array function is better than a single formula but you can try with your data

I'm also assuming line 1 of Sheet A, B, C, ... has no values and the maximum number of rows are 10000

Normal formula
In DATA!A2 use

=INDIRECT($A$1&"!"&"A2")

and drag to I10000

Array formula
Select A2:I10000, insert the following formula and confirm by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Formula:

Please Login or Register  to view this content.

See the file with array formula implemented

Perfect! Thank you José!

You're welcome

I'm happy to have helped.

Do not forget to mark this thread as SOLVED.