Log in

View Full Version : Excel conditional enable range disable range


pcmann2004
Nov 10, 2011, 11:56 PM
I am trying to enable disable rows and exclude cells that are empty from the random counter code example:

Enable Rank Random Officer Name Output1
yes 23 0.440135354 4216 Leonardo Vela 1 steven ford
yes 7 0.74673831 4217 Donald Pudlowski 2 leonardo vela
13 0.659526727 4218 3 donald pudlowski
yes 24 0.424849358 4219 Steven Ford 4

this code is similar for each case
code : =RANK(C2,$C$2:$C$5) =RAND() =VLOOKUP(G2,B2:E5,4,0)

I need to modify if row three is not enabled in the output and automatically gets position last or after the enabled data

JBeaucaire
Nov 11, 2011, 08:37 AM
Can you post an actual workbook showing your current setup, then add a second sheet showing your desired results and explain them in the workbook in context?

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

pcmann2004
Nov 11, 2011, 10:07 AM
Here's the Spreadsheet

JBeaucaire
Nov 11, 2011, 01:26 PM
Here's how I would do that...

pcmann2004
Nov 12, 2011, 11:09 PM
WOW that's amazing, I will put this into my sheet and see if it works in my situation, can you define what you have done so I can understand the code better

JBeaucaire
Nov 15, 2011, 07:38 AM
Column C formula will only insert a randomized number if column A says "yes".

Column B will only rank that row if there is a "yes".

Column G will only show a number if it is less than the maximum number currently showing in column B.

Column H will only do a VLOOKUP() to match the Ranks and pull the name from column E if there is a visible number in column G.