Multiple sheet data into single sheet

[satyanarayana]

Hi Excel Experts,

We are maintain our daily sales report in excel. For each day we update in new sheet in same excel file. Now I would like to extract from individual sheet into one sheet. so my date will be in rows products in columns.

Please help me how to do this. I have data from 1st Jan to till date. Here I am attaching the excel file (daily sales report) from there we need to extract all individual data into single sheet i.e summary - Orders sheet and revenue in Summary - Sales

Thanks,
Satya

[ranman256]

The data should be stored as:
[topic] [Products/Services] [Sales] [Orders] [date] [salesperson]
MY SPARK HEALTH SALES $ IV Full Bag $850 6 07-Jun-21 Clark Kent
MY SPARK HEALTH SALES $ IV Fast Bag $90 1 07-Jun-21 Clark Kent
MY SPARK HEALTH SALES $ Glutathione Add-on $50 1 07-Jun-21 Clark Kent

THEN you can pivot on the data an product totals for ANYTHING/PERSON/DATE

[JohnTopley]

If you want to extract from 180+ days you will need VBA.

The only way to reference each sheet via formula is using INDIRECT which is a volatile function and will have serious impact on performance.

The data should have been placed in one sheet with the date as a column (as per post #2 reply).

[neme87]

If you are going to attempt doing this with a formula my suggestion would be to use the indirect function if you are absolutely sure of the data integrity (the product-names must be written 100% correct as we search for them).

First I change the Date column from A3 and downwards to text (example is '1-Jun-21 written as text instead of date) so that or indirect function can use it to refer to all the sheets.

To avoid having to rename one and one date you can use the concatenate function: In an empty column i type the number 1 in the first row, the number 2 in the second row, the number 3 in the third row, i then mark the three cells containing the numbers and grab a hold of the lower right cell handle and pull it downwards so that excel gives me a sequence of numbers up to 31. On the column beside this number column I type '-Jan-21 and copy this text downward alongside my number sequence, then in the next column i just use =CONCATENATE(A1;B1) and use the lower right cell handle and pull it downwards to populate. Now I got 1-Jan-21 to 31-Jan-21 in this column which is the name of all your sheets for the first month. I copy this result and paste it as a value in both your summary sales and orders sheet to replace the dates you've put in there.

Then in your summary - sales sheet in B3 (beside your new date format) i write the formula =IFERROR(VLOOKUP(B$2;INDIRECT("'"&$A3&"'!$A$1:$C$100");2;0);0)

In your summary - orders I also paste the new dates (as text) in the A column, and in the B column I write the formula =IFERROR(VLOOKUP(B$2;INDIRECT("'"&$A3&"'!$A$1:$C$100");3;0);0)

[JohnTopley]

RE post#4: Avoid INDIRECT!!!

[neme87]

Unpopular opinion: You can use indirect if you're just pulling the data together this one time. If old dates will be edited at later times, then I'd rather check John's solution. If it's just a one time grab for each date, I'd use indirect to grab the values, and re-paste as values removing all the indirect formulas.

[JohnTopley]

Please Login or Register  to view this content.

"RUN" button on "Summary - Sales"

[satyanarayana]

Thanks for your suggestions. Indirect formula worked well.