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]

Assuming that you have competitor names in 'Competitor Overview Data'!E1:G1 for the examples given, try

=IFERROR(INDEX('Competitor Overview Data'!$4:$4,MATCH($P$3,'Competitor Overview Data'!$1:$1,0)),"No Match")

 

[kweaver]

Another thought:

=OFFSET('Competitor Overview Data'!D4,0,MID(P3,FIND("_",P3)+1,3))

(this would allow for 999 competitors)

 

[gaudrco]

jasonb75

Your formula simplified mine exponential

However, I cannot get it to work for my other cells. (or maybe I just don't understand the formula well enough to translate it.)

For the competitor name, that worked but I have these dynamic cells too
Description
Highlights
Company

I have more as well but if I can see how to make it work for these then I should be able to replicate it. All of my reference data is in a table with the Competitor name as the headers. Then the rows are Description, Highlights, Company in that order. How to I adjust the formula to go to these cells that hold the data

Thanks

 

[gaudrco]

kweaver

This sounds great but I can't get it to work right. Is there a part of that formula that I need to adjust for my situation?

 

[gaudrco]

Nevermind jasonb75 I figured it out! This is so much better! I thought it was impossible

 

[kweaver]

There isn't anything you need to adjust.

Excel 2010
	P
2
3	Competitor_4
4
5	For 4
Sheet7

Cell Formulas
Range	Formula
P5	=OFFSET('Competitor Overview Data'!D4,0,MID(P3,FIND("_",P3)+1,3))

 

[jasonb75]

There isn't anything you need to adjust.

That is assuming that it does actually say "Competitor_1", etc rather than "Competitor_#" being a substitute for the actual competitor name.

 

[gaudrco]

That is assuming that it does actually say "Competitor_1", etc rather than "Competitor_#" being a substitute for the actual competitor name.

You are correct. "Competitor_#" is a substitute for actual names

 

[kweaver]

Well, that was not made clear from the outset.