Summary of conditional formating on new sheet

[jimstrongy]

I did conditional formating of top 10% for 3 different colums (sales, cost, profit)not dependent to each other. Right now my top 10% is higlighted in green for each of the columns I'm wondering if there is a formula that I can put on the new sheet that would pull top 10% for each column (highlighted in green) and sort in descending order.

Thanks

[Fotis1991]

Hi

As i know, this is the problem of Conditional Formatting.

Can't use a formula for it. Only Copy-Paste, or using VBA.

[jimstrongy]

What kind a VBA any example?

[Fotis1991]

Sorry, i know almost nothing for VBA.

Maybe another member..

[dilipandey]

Hi Jim,

See the attached file where I have tried solving your query without VBA.

Cheers


Regards,
DILIPandey

<click on below 'star' if this helps>

[Fotis1991]

dilipandey, jimstrongy, wrotes:

I'm wondering if there is a formula that I can put on the new sheet that would pull top 10% for each column (highlighted in green) and sort in descending order.

So, my answer is regarding what jimstrongy, asked for. Different Sheet, Conditional Formatting.

Is this what are you doing?

[dilipandey]

Yes... the results which I have shown on the right side of the data set, can be obtained on any sheet, as shown in the same sheet.
Since I was not sure as which sheet will be used to show result hence thought to provide the results on the same sheet.
If the results are agreeable, then it can be achieved in any sheet. And yes, it is pulling only top 10% of the green highlighted cells from original data set.


I hope this clarifies ...

cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[Fotis1991]

You done a great job.

My complaint is, if we can use Conditional Formating, using data from another sheet.

I undertoond, that Jimmy asked for this!

So if i understoond not well the point, pls forget it. Never mind!!

[jimstrongy]

I have more complex formula than that because problem is a lot more complex and I achieve same thing as you. Top. 10% of 30 rows its 3 rows and then its a matter of filling in formula in 3 rows.
I have data 1234 rows big and doing your formula means I have to calculate what 10% of that number is and then do the formula.
Hence, conditional formating calculates byitself 10% and highlights it no matter how many rows. The point is that I need to have something automated and I don't have toadjust the formula if I only have 3 items that are in top 10% or 300 items.

[dilipandey]

Hey Fotis,

I guess I am not getting your point... is your questions is to get data from another sheet using conditional formatting OR you are asking if we can use conditional formatting on the data which we got (through formulas) from another sheet. Excuse me if I am not getting correctly..

Thanks,
DILIPandey

<click on below 'star' if this helps>

[Fotis1991]

Hi dilipandey

It's not exactly guestion...

I tried to say that if in sheet 2(per example) want to use in a range of cells, Conditional formatting, we can not use data that these are in another sheet...=if(sheet1!a1=1; ..green colour...)

Sorry for my bad English...

[dilipandey]

Hi Jim,

If I understood you correctly, you need some way to fill the formula down automatically, till it fills up the 10% of the rows, so in this case 30x 10% = 3 rows to fill up.
If this is correct, then only one option is VBA. Let me know if I am correct in my understanding so that I can provide that code to you.


Regards,
DILIPandey

<click on below 'star' if this helps>

[dilipandey]

Yes Fotis,

Now I got your point... though I do not deny this fact that we can not catch up green color cells as shown in your formula:=if(sheet1!a1=1; ..green colour...) but this is not required actually. If one thinks little deep on this, he/she will get to know that conditional formatting (green color) is applied only on top 10% of values in original list... so rather going to chase Green ones, why not pick up top 10% directly from that original list and this is what I have given a try.

Let me know if I am still lacking somewhere to understand you correctly.

Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[estige]

Hi, did a small adjustment to dilipandey's suggestion to make it more flexible.

[dilipandey]

Thanks estige.. Cheers
Hi Jim, let us know if this solves your purpose.

Regards,
DILIPandey

<click on below 'star' if this helps>