Select cell based on separate/non adjacent cell range

%3CLINGO-SUB%20id%3D%22lingo-sub-1261993%22%20slang%3D%22en-US%22%3ESelect%20cell%20based%20on%20separate%2Fnon%20adjacent%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1261993%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20someone%20could%20please%20help%20me%20with%20a%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20image%20I%20have%20attached%20is%20the%20spreadsheet%20I%20am%20working%20with.%20The%20yellow%20shaded%20cell%20(0.07%25)%20is%20conditionally%20formatted%20to%20highlight%20the%20cell%20yellow%20when%20the%20cell%20value%20falls%20between%20-0.05%25%20and%200.1%25%2C%20as%20it%20has%20done%20successfully.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBased%20on%20the%20range%20(-0.05-0.1%25)%26nbsp%3B%200.07%25%20occurs%20at%20%243.18%20(first%20column%20on%20left).%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20question%20is!%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20way%20for%20me%20to%20automate%20the%20process%3F%20Where%20instead%20of%20scrolling%20down%20and%20manually%20noting%20that%200.07%25%20occurs%20at%20%243.18%20can%20this%20dollar%20value%20auto%20fill%20into%20a%20cell%20on%20a%20summary%20page%20at%20the%20top%20of%20the%20table.%20As%20a%20result%20I%20can%20quickly%20identify%20that%200.07%25%20occurs%20at%20a%20price%20of%20%243.18%20rather%20than%20scrolling%20through%20the%20table.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMany%20thanks%20everyone.%20I%20am%20little%20bit%20out%20of%20my%20depth%20with%20the%20formula%20or%20potentially%20macro%20required%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERalph%20B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1261993%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262019%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20cell%20based%20on%20separate%2Fnon%20adjacent%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262019%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20very%20much!%20ill%20give%20this%20a%20go%20now.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20really%20appreciate%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262000%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20cell%20based%20on%20separate%2Fnon%20adjacent%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262000%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F599503%22%20target%3D%22_blank%22%3E%40RalphB1560%3C%2FA%3E%26nbsp%3BIf%20your%20Excel%20recognises%20the%20dynamic%20array%20function%20FILTER%2C%20you%20can%20achieve%20this%20as%20demonstrated%20in%20the%20attached%20file.%20It%20will%20create%20a%20summary%20list%20of%20all%20rows%20that%20comply%20to%20the%20percent%20range%20you%20are%20interested%20in.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAlternatively%2C%20and%20since%20you%20already%20have%20conditionally%20formatted%20(yellow)%20the%20percentages%20that%20fall%20within%20the%20range%2C%20you%20could%20also%20filter%20the%20percent-column%20by%20color.%20This%20is%20also%20demonstrated%20in%20the%20attached%20file.%20It%20doesn't%20give%20you%20a%20summary%20table%20but%20it%20gives%20an%20instant%20overview%20without%20having%20to%20scroll%20down%20a%20long%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262054%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20cell%20based%20on%20separate%2Fnon%20adjacent%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262054%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3BThank%20you%20that%20works%20great!%20problem%20solved%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20are%20a%20legend!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1262059%22%20slang%3D%22en-US%22%3ERe%3A%20Select%20cell%20based%20on%20separate%2Fnon%20adjacent%20cell%20range%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1262059%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F599503%22%20target%3D%22_blank%22%3E%40RalphB1560%3C%2FA%3E%26nbsp%3BNot%20really%2C%20but%20glad%20that%20I%20could%20help%20out!%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

Hello everyone,

 

I was hoping someone could please help me with a solution.

 

The image I have attached is the spreadsheet I am working with. The yellow shaded cell (0.07%) is conditionally formatted to highlight the cell yellow when the cell value falls between -0.05% and 0.1%, as it has done successfully. 

 

Based on the range (-0.05-0.1%)  0.07% occurs at $3.18 (first column on left).

 

My question is! 

Is there way for me to automate the process? Where instead of scrolling down and manually noting that 0.07% occurs at $3.18 can this dollar value auto fill into a cell on a summary page at the top of the table. As a result I can quickly identify that 0.07% occurs at a price of $3.18 rather than scrolling through the table.

 

Many thanks everyone. I am little bit out of my depth with the formula or potentially macro required?

 

Ralph B

4 Replies
Highlighted

@RalphB1560 If your Excel recognises the dynamic array function FILTER, you can achieve this as demonstrated in the attached file. It will create a summary list of all rows that comply to the percent range you are interested in.

 

Alternatively, and since you already have conditionally formatted (yellow) the percentages that fall within the range, you could also filter the percent-column by color. This is also demonstrated in the attached file. It doesn't give you a summary table but it gives an instant overview without having to scroll down a long list.

Highlighted

@Riny_van_Eekelen Thank you very much! ill give this a go now. 

 

I really appreciate your help.

 

Cheers

Highlighted

@Riny_van_Eekelen Thank you that works great! problem solved

 

You are a legend! 

Highlighted

@RalphB1560 Not really, but glad that I could help out!