index values from columns across different sheets in one column

Lotfi Kaced

Hello,

For context, I'm trying to group all my projects (three different types of projects using three different summary sheets) into one (4th) sheet that I will use just for capacity planning purposes.

I have three different summary sheets for the three types of projects I have, where the primary column always has the name of the project:

• Newsell / sheet 1
• Upsell / sheet 2
• AS / sheet 3

In all three sheets, I have other columns including: Fiscal year, project team, capacity (numbe rof days) etc...

In the first column of a fourth sheet, I want to list all of the values in the primary column of the previously mentionned sheets.

I can't really use a helper column for the index as it will pull all the info from sheet 1, but then the index wouldn't work for the rest of the formula.

I've tried index match, but won't work as the only unique values I have on each sheet are the names, which i am trying to pull.

I tried to use my Fiscal Year (2025), but it will only list the first values of the range but won't itterate for the rest.

I want to use column formulas to be able to automate the whole process, I want to pull the name of the projects from all sheets regardless of their type, then use Index/match on column formulas of the reste of the sheet to get all info I need.

Do you guys have any idea how to work around this? Or a better idea to achieve this?

Thank you!

Scott Orsey

Hi @Lotfi Kaced, Will the same project appear on more than one sheet, or are they unique across all sheets? One way to do this would be to create two helper columns... the first lists the sheet and the second is the row from the sheet. Then you can use the combination of those columns to pull the right data from the right sheet for each row. One major issue with this approach is that your summary sheet will need to have enough rows to contain all of the projects from all three sheets.

Have you considered creating a row report where you pull in the data from the three sheets? Since you have a lot of common data (the same column in each sheet), this should be relatively compact. You can then group, sort or filter and even summarize as needed without creating all of those cross-sheet formulas. This would be much better from a maintenance perspective since it would automatically pull in all of the rows you need.

I hope this helps. Be well!