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

 

[jasonb75]

Sure, keeping it brief (there are other places that explain in detail better than I can).

Using 'Report 2' in B3 as an example, MATCH($B$3,'Competitor Comparison Data'!$D:$D,0) will look for Report 2 and find which row it is in (row 57).

+ROWS(A$8:A8)-1 makes an adjustment to that row number by adding the count of rows from A8 down to the current row, then subtracting 1 as you fill down, so in A8, 57+1-1 = 57, in A9, 57+2-1 = 58, etc.

This is passed back to the INDEX function which returns whatever is in that row of column E.

IFERROR is there to warn you when there is no data to be found, which would happen if the report number in B3 is not found in column D.

More in depth descriptions on each of the functions used can be found in the excel help file, or by using search terms like 'excel function index' on google.

 

[gaudrco]

Thank you for this explanation. It has helped me very much