Extract from Tables from multiple tabs: One Workbook

Hello everyone, I am hoping someone is willing to help me out with something I am having a difficult time with.

Attached you will find a workbook with multiple tabs. I am trying to pull data from all the tabs and paste them into tab "Input Table" with the format given.

Please note that the tables are much larger and there are numerous tabs than the example given. So if we can write up a formula that captures the columns/rows.

In the "Input Table", I can place the middle account number in the first column along with the representing date, if we can find a formula that will fill in the rest, that will be helpful.

If it makes it easier, you can insert a row of corresponding dates for each tab. Considering the format is always the same.

Thank you in advance for your time and help in this.


EDIT - PLEASE SEE LATEST ATTACHMENT -

Originally Posted by belinda200

I see, so according to my suggestion - you will have to input the tab names manually

Hi Belinda - I have taken a look and it looks to be working except for one issue. What if there are more columns in the "Input Table".

I had put First Row, Second Row, Third Row, and Middle Row as examples. There are in fact 19 total rows, which would be 19 total columns.

I think I figured it out - you already mentioned it above regarding changing the red numbers in the cell.

Edit - Belinda, the way I tried it with your formula only worked partly but was not able to complete the entire data that I have in my original file. I think what I will do is simplify the format further so it's easier to work with. If I can attach another file to this thread, I will.

Thank you for trying to help me.

Ok, I have simplified the data table to pull. Please see attachment.

Two tabs, one is Fill In where the data needs to be pasted and the Data_Table tab where the data is held.

Please note that the Data_Table is small and let's assume that the "Store" continues down hundreds of rows, so if we can find a formula that captures down the entire column would be great.

In the Fill In tab, I've colored the cells that need to be pasted in yellow. I hope this is easier than the previous attachment hah.

Thank you very much for your help.

OK, it was not a bit easier (for me at least) , but the helper row & column that add the numbering is an anchor...
so I think I managed to find something.

How about that in C2 and across?

Please Login or Register  to view this content.

Let me know if that works for you.

Can you walk me through how I can do this. I filled in the dates and when I copy and paste the last row, it gives me #NUM! errors. I've attached what I'm referring to. ahhh lol

Please try at
A2
=--MID(INDEX(CHOOSE(INT((ROWS(A$2:A2)-1)/24)+1,'ABC I 2019'!$A:$A,'ABC II 2019'!$A:$A,'ABC III 2019'!A:A,'ABC I 2020'!$A:$A),MOD(INT(INT(ROWS(A$2:A2)-1)/12),2)*5+3+COLUMNS($A2:A2)),4,3)

B2
=--MID(INDEX(CHOOSE(INT((ROWS(B$2:B2)-1)/24)+1,'ABC I 2019'!$D$3:$O$3,'ABC II 2019'!$D$3:$O$3,'ABC III 2019'!$D$3:$O$3,'ABC I 2020'!$D$3:$O$3),MOD(ROWS(B$2:B2)-1,12)+1),8,20)

C2:F2
=--MID(INDEX(CHOOSE(INT((ROWS(B$2:B2)-1)/24)+1,'ABC I 2019'!$D$3:$O$3,'ABC II 2019'!$D$3:$O$3,'ABC III 2019'!$D$3:$O$3,'ABC I 2020'!$D$3:$O$3),MOD(ROWS(B$2:B2)-1,12)+1),8,20)


For more shees better use Power Query
Change file path in red

Please Login or Register  to view this content.

Oh boy I really can't get them to work

I have changed the tables for the last time in hopes that we can simplify it a bit more for me, very sorry.

There are 3 tabs of data, 2019, 2020, and 2021. Please assume that the tables go down hundreds of rows, so from Store AAA to Store ZZZ etc.

We then capture the data from those tables and almost transpose them in the appropriate CAT# based on the proper Date.

I feel like we just need to match the Store, CAT#, and Date but Date and CAT# are transposed.

If you guys think it would be easier if the data tables were formatted in a different way, please let me know.

Can't thank you more than enough for your time. Truly appreciate it!

I see Bo_Ry gave you the ultimate solution.....you can go with that.

Originally Posted by Bo_Ry

Please try at
A2
=--MID(INDEX(CHOOSE(INT((ROWS(A$2:A2)-1)/24)+1,'ABC I 2019'!$A:$A,'ABC II 2019'!$A:$A,'ABC III 2019'!A:A,'ABC I 2020'!$A:$A),MOD(INT(INT(ROWS(A$2:A2)-1)/12),2)*5+3+COLUMNS($A2:A2)),4,3)

B2
=--MID(INDEX(CHOOSE(INT((ROWS(B$2:B2)-1)/24)+1,'ABC I 2019'!$D$3:$O$3,'ABC II 2019'!$D$3:$O$3,'ABC III 2019'!$D$3:$O$3,'ABC I 2020'!$D$3:$O$3),MOD(ROWS(B$2:B2)-1,12)+1),8,20)

C2:F2
=--MID(INDEX(CHOOSE(INT((ROWS(B$2:B2)-1)/24)+1,'ABC I 2019'!$D$3:$O$3,'ABC II 2019'!$D$3:$O$3,'ABC III 2019'!$D$3:$O$3,'ABC I 2020'!$D$3:$O$3),MOD(ROWS(B$2:B2)-1,12)+1),8,20)


For more shees better use Power Query
Change file path in red

Please Login or Register  to view this content.

Hello Bo, please see my attachment as it is not working like yours (I dont have power query) so the old fashion functions will have to do

Please try at

A2
=INDEX(CHOOSE(INT((ROWS(A$2:A2)-1)/24)+1,'2019'!A:A,'2020'!A:A,'2021'!A:A),MOD(INT((ROWS(A$2:A2)-1)/12),2)*(COUNTA('2019'!A:A)-1)/2+4)

B2
=INDEX(CHOOSE(INT((ROWS(B$2:B2)-1)/24)+1,'2019'!$D$3:$O$3,'2020'!$D$3:$O$3,'2021'!$D$3:$O$3),MOD(ROWS(B$2:B2)-1,12)+1)

C2:U2
=INDEX(CHOOSE(INT((ROWS(C$2:C2)-1)/24)+1,'2019'!$D:$O,'2020'!$D:$O,'2021'!$D:$O),MOD(INT((ROWS(C$2:C2)-1)/12),2)*(COUNTA('2019'!$A:$A)-1)/2+3+COLUMNS($C2:C2),MOD(ROWS(C$2:C2)-1,12)+1)



Power Query is at Ribbon DATA
https://support.microsoft.com/en-us/...6-59e18d75b4de

Please Login or Register  to view this content.

Hello Bo, please see my attachment as it is not working like yours (I dont have power query) so the old fashion functions will have to do

Your forum profile says Excel 2016 - PowerQuery is part of that, so please confirm that it is indeed Excel 2016 that you are using.

Hi,
I made an amendment to column A and C, there was a miscalculation...
Please see attached.