SOLVED
Home

Cell location while filtering

%3CLINGO-SUB%20id%3D%22lingo-sub-563999%22%20slang%3D%22en-US%22%3ECell%20location%20while%20filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-563999%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Folks%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20probably%20a%20simple%20procedure%2C%20but%20I%20haven't%20been%20able%20to%20figure%20it%20out.%20I%20have%20a%20cell%20with%20a%20few%20nested%20if%2For%20statements%20that%20rely%20on%20the%20data%20in%20another%20cell.%20For%20example%2C%20let's%20say%20that%20cell%20A1%20will%20display%20a%20company%20name%20depending%20on%20what%20is%20in%20cell%20B1.%20However%2C%20column%20B%20is%20filtered%20with%20a%20few%20hundred%20rows.%20How%20do%20I%20make%20cell%20A1%20always%20display%20what%20is%20the%20top%20filtered%20cell%20in%20column%20B%3F%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20a%20very%20short%20example%20of%20what%20I%20am%20doing.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIF(OR(B1%3D%22Something%22)%2C%22Name1%22%2C%3C%2FP%3E%3CP%3EIF(OR(B1%3D%22Something%20Else%22)%2C%22Name%202%22%2C%3C%2FP%3E%3CP%3EIF(OR(B1%3D%22A%20third%20something%22)%2C%22Name%203%22%2C%20%22Error%22)))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20problem%20is%2C%20when%20I%20filter%20Column%20B%2C%20the%20top%20cell%20retains%20its%20original%20row%20number.%20It%20doesn't%20change%20to%20B1.%20How%20do%20I%20point%20to%20whatever%20is%20in%20that%20location%3F%3C%2FP%3E%3CP%3EAlso%2C%20I%20can't%20run%20Macros%20due%20to%20administrator%20blocks.%20So%20I'm%20stuck%20with%20the%20nested%20if%2For%20statements.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%20Hopefully%20my%20explanation%20is%20clear.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-563999%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564633%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20location%20while%20filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564633%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F144618%22%20target%3D%22_blank%22%3E%40Cory%20McKissick%3C%2FA%3E%26nbsp%3B%2C%20for%20the%20model%20like%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20325px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F113194i8918EDF824D406AE%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CPRE%3E%3DINDEX(B2%3AB30%2CAGGREGATE(15%2C6%2C1%2F(SUBTOTAL(3%2COFFSET(B2%2CROW(B2%3AB30)-ROW(%24B%241)-1%2C0)))*(ROW(B2%3AB30)-ROW(%24B%241))%2C1))%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564916%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20location%20while%20filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564916%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%20%26nbsp%3BWow%2C%20thank%20you!%20I%20have%20absolutely%20no%20idea%20what%20is%20happening%20in%20your%20code%2C%20but%20it%20does%20what%20I%20need%20it%20to.%20I'll%20have%20to%20figure%20out%20how%20to%20integrate%20that%20into%20the%20If%2For%20statement%2C%20but%20it%20shouldn't%20be%20too%20tough.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-564941%22%20slang%3D%22en-US%22%3ERe%3A%20Cell%20location%20while%20filtering%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-564941%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F144618%22%20target%3D%22_blank%22%3E%40Cory%20McKissick%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYou%20may%20use%20directly%20like%3C%2FP%3E%0A%3CPRE%3E%3DIF(%26lt%3Bformula%26gt%3B%3D%22Something%22%2C%22Name1%22%2C%22no%20name%22)%3C%2FPRE%3E%0A%3CP%3Ebut%20better%20to%20keep%20formula%20result%20in%20helper%20cell(s)%20and%20use%20them%20for%20the%20reference.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Cory McKissick
Occasional 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. 

 

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.