Automatically add line to formula

[gaudrco]

I don't know if this is possible but here it is:

I have a sheet that I use to display an overview of competitors. The information on the sheet shows one competitor at a time based off of a data validation dropdown list of values. When the dropdown list is changed, the cells on the sheet change to reflect the chosen competitors information. The cells with the competitor information are IF statements. The IF statements for each cell with information say =IF($P$3="Competitor_1",'Competitor Overview Data'!E4,IF($P$3="Competitor_2",'Competitor Overview Data'!F4,IF($P$3="Competitor_3",'Competitor Overview Data'!G4))). (The formula reference cell systematically moves to the right)

So, I have another function in my spreadsheet that adds new competitors. When a new competitor is added, it appears in dropdown list but obviously when I choose the newly added competitor, all the competitor information cells return FALSE because the new competitor is not included in the IF statements.

Is there a way that automate this process so that when I add a new competitor, the formulas include the new competitor IF statement with its corresponding reference cell?

Thanks

 

[gaudrco]

I'm sorry I didn't clarify on that. I really appreciate both of you helping me with this

 

[jasonb75]

Given that common practice is to substitute real names for fictional, I would say that it was a given.

 

[jasonb75]

I really appreciate both of you helping me with this

You're welcome!
From your earlier replies, it looks as if you have the formula that I suggested working as you need it, but if not, please feel free to ask for additional help or advice as needed.

 

[gaudrco]

Yes Thank you,

I do have another very similar situation in my spreadsheet. I've been playing around with your formula a bit but can't get it right.

Again, another dropdown list with values being reports ( Report 1, Report 2, Report 3,). When I choose an option from the dropdown list, it dynamically changes my table to correspond to the report chosen. The reference data comes from sheet 'Competitor Comparison Data'. The data in the reference table is broken into sections of 100 rows . Each section belongs to a Report. To give you a little background, the reports compare features across competitors, so each section/report can have 100 features(there is another row in between sections to separate them). The columns are competitors.
So if you come up with a formula, I should be about to use the fill tool to drag it across the sheet.

The first feature for Report 1 is in cell E6 on sheet 'Competitor Comparison Data'

Is this enough information?

Edit:
If it helps, here are the first 3 IF statements I have in the first row currently

A8 = IF($B$3="Report 1",'Competitor Comparison Data'!E6, IF($B$3="Report 2",'Competitor Comparison Data'!E57,IF($B$3="Report 3",'Competitor Comparison Data'!E108,)))

B8 = IF($B$3="Report 1",'Competitor Comparison Data'!F6, IF($B$3="Report 2",'Competitor Comparison Data'!F57,IF($B$3="Report 3",'Competitor Comparison Data'!F108)))

C8 = IF($B$3="Report 1",'Competitor Comparison Data'!G6, IF($B$3="Report 2",'Competitor Comparison Data'!G57,IF($B$3="Report 3",'Competitor Comparison Data'!G108)))

 

[gaudrco]

I made a mistake, there are 50 rows for features with an extra row to separate them. Not 100

 

[jasonb75]

Is there a 'Report 1' heading anywhere in the first 50 rows, 'Report 2' heading in the next 50, etc? Or do we only have the position to work with?

If there are headings then my earlier suggestion should be adaptable, otherwise it will mean adapting weaver's suggestion.

 

[gaudrco]

Yes there is. The Report heading is in the column to the left of the features. The heading column for each report section is merged down to combine all 50 cells

 

[jasonb75]

From the way that you describe it, I think that this should work, entered into A8 then filled right and down as needed.
=IFERROR(INDEX('Competitor Comparison Data'!E:E,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS(A$8:A8)-1),"No Match")

 

[gaudrco]

That worked! Thank you very much

 

[gaudrco]

=IFERROR(INDEX('Competitor Comparison Data'!E:E,MATCH($B$3,'Competitor Comparison Data'!$D:$D,0)+ROWS(A$8:A8)-1),"No Match")

Could you please explain what is going on in this formula for me? It's great that it is working but I would like to understand what each part represents. Thank you