Consolidation of data from multiple sheets with different headers

[Anastasiya23]

I am working on an excel model with single input sheet for multiple projects. The inputs feed into individual proforma tabs for each project and all calculations are same for all projects. However, the starting column (project start date) will vary. I can have one proforma starting 1/1/23 forward, one starting 6/1/22, etc. I then need to be able to consolidate all sheets using matching time period headers. Meaning, on one tab 1/1/23 may be column 2 but on another it is column 6. I need excel to see which column to pick up and can't figure out how to do it. Individual proformas are not set up as a data table (there are a bunch of calculations, etc). I would normally use Sum(Beg:End) but here it doesn't work. I also don't want to do manual formulas with HLookups linking to each sheet. I need to be able to remove tabs from consolidation or add new tabs to consolidation and not have to re-link stuff. I could set up all projects to start in the past and have data populated depending on when the project start date is, but i already set everything up differently with different start dates and would prefer not to re-do the whole thing.

I've attached an example of the issue. thank you!!

[sandy666]

try to set your data to 1NF
your consolidation sheet is exactly the same like others - no differences

[Anastasiya23]

thank you for responding. Unfortunately, i am not that advanced. I read the link you shared but don't really understand how this will apply to my case. How would I be able to consolidate different sheets with that set up? I have the same "key" in header for each tab but they are not in the same order. When you say "your consolidation sheet is exactly the same like others - no differences" - what does this mean? The only formula I know to consolidate (other than manually going to each tab) is Sum(Beg:End) and then you just put sheets in you want to add up. You must be referencing some other consolidation sheet that I am not familiar with.

[sandy666]

show how your consolidation sheet should looks like (expected result) because there is no any difference to me except years/dates

edit:
maybe like this? (part of)

1	2	3	4	5	6
01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023
1	1	1	1	1	1
01/01/2022	01/02/2022	01/03/2022	01/04/2022	01/05/2022	01/06/2022
1	1	1	1	1	1
01/06/2022	01/07/2022	01/08/2022	01/09/2022	01/10/2022	01/11/2022
1	1	1	1	1	1
01/01/2024	01/02/2024	01/03/2024	01/04/2024	01/05/2024	01/06/2024
1	1	1	1	1	1

[Anastasiya23]

Are you able to see the attachment i had in the original post? I have 1 tab "Project 1" with columns by month starting with 1/1/23, 2/1/23, 3/1/23, etc. "Project 2" tab starts with 6/1/22, 7/1/22, 8/1/22, etc. "Project 3" tab starts with 1/1/24, 2/1/24, 3/1/24. I then will have a consolidated tab that i want to start with 1/1/23. So relevant column from each tab need to consolidate - meaning in consolidated tab under 1/1/23 I want to see data from column labeled 1/1/23 (if exists) from all 3 projects. I don't want to see "Project 1" 1/1/23 + "Project 2" 6/1/22 + "Project 3" 1/1/24. Does this make sense what I am asking?

[Anastasiya23]

The consolidation needs to be in the same format as individual tabs but it would add the data from each tab. Sorry, that was probably not clear. I want to be able to add up individual projects into one "giant project" summing everything. The issue is that the periods between each tab in column headers don't match column by column.

[sandy666]

I know what you mean but headers are from 1 to 169, dates are NOT a headers
would be simply with 1NF structure of the TABLE(s)
anyway I give up maybe someone else

have a nice day

1	2	3	header
01/01/2022	01/02/2022	01/03/2022	data
1	1	1	data

[Anastasiya23]

Let's use periods as headers not dates. Here is what I need to consolidated. How would I do that (not manually and not using HLookups linking to individual tabs).

Capture.PNG

[sandy666]

see sheet TEST

[Anastasiya23]

Sorry, this is not what i am looking for. See attached what I need. But this is manual way which I do not want. Thanks for your time, though!

[Anastasiya23]

It is not letting me upload the updated file for some reason. Trying again.Excel Sample revised.xlsx

[sandy666]

but where is

169

01/01/2038

15

on your consolidation sheet?

hoever I need more coffee so as I said before
Good Luck