Forum Discussion
Cell location while filtering
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.
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.
3 Replies
Cory McKissick , for the model like here
=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))
SergeiBaklan 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.
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.
