Mar 28 2020 10:15 PM
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
Mar 28 2020 10:43 PM - edited Mar 28 2020 11:23 PM
@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.
Mar 28 2020 11:09 PM
@Riny_van_Eekelen Thank you very much! ill give this a go now.
I really appreciate your help.
Cheers
Mar 28 2020 11:59 PM
Mar 29 2020 12:09 AM
@RalphB1560 Not really, but glad that I could help out!