SOLVED

Cell location while filtering

Copper Contributor

Hi Folks,

 

This is probably a simple procedure, but I haven't been able to figure it out. I have a cell with a few nested if/or statements that rely on the data in another cell. For example, let's say that cell A1 will display a company name depending on what is in cell B1. However, column B is filtered with a few hundred rows. How do I make cell A1 always display what is the top filtered cell in column B? 

This is a very short example of what I am doing. 

 

IF(OR(B1="Something"),"Name1",

IF(OR(B1="Something Else"),"Name 2",

IF(OR(B1="A third something"),"Name 3", "Error")))

 

The problem is, when I filter Column B, the top cell retains its original row number. It doesn't change to B1. How do I point to whatever is in that location?

Also, I can't run Macros due to administrator blocks. So I'm stuck with the nested if/or statements. 

 

Thanks. Hopefully my explanation is clear. 

3 Replies

@Cory McKissick , for the model like here

image.png

=INDEX(B2:B30,AGGREGATE(15,6,1/(SUBTOTAL(3,OFFSET(B2,ROW(B2:B30)-ROW($B$1)-1,0)))*(ROW(B2:B30)-ROW($B$1)),1))

@Sergei Baklan   Wow, thank you! I have absolutely no idea what is happening in your code, but it does what I need it to. I'll have to figure out how to integrate that into the If/or statement, but it shouldn't be too tough. 

 

best response confirmed by Cory McKissick (Copper Contributor)
Solution

@Cory McKissick ,

 

You may use directly like

=IF(<formula>="Something","Name1","no name")

but better to keep formula result in helper cell(s) and use them for the reference.

 

1 best response

Accepted Solutions
best response confirmed by Cory McKissick (Copper Contributor)
Solution

@Cory McKissick ,

 

You may use directly like

=IF(<formula>="Something","Name1","no name")

but better to keep formula result in helper cell(s) and use them for the reference.

 

View solution in original post