Conditional Formatting of multiple lines based on variable?

[bwinchell]

Hello,
I am a newbie to doing anything beyond the basics in Excel so you might have to very specific in explanations. unfortunately I do not know VB (some Javascript & bash) and afraid what I am looking for will require it.
Thanks

Environment: Excel 2013

Current Workbook layout:
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]System[/TD]
[TD]Voltage[/TD]
[TD]RU[/TD]
[TD]BTU[/TD]
[TD]110v[/TD]
[TD]208v[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]computer1[/TD]
[TD]208[/TD]
[TD]2[/TD]
[TD]1945[/TD]
[TD]2.7[/TD]
[TD]1.2[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]computer2[/TD]
[TD]208[/TD]
[TD]1[/TD]
[TD]780[/TD]
[TD]1.4[/TD]
[TD].7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]computer3[/TD]
[TD]208[/TD]
[TD]3[/TD]
[TD]1000[/TD]
[TD]3.0[/TD]
[TD]1.5[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Explanation of columns:
A = a system name based on a dynamic list created from a tab called "master" > tbl_master
B = the parameter used to search against the object in the tbl_master
C - G = are filled via an INDEX formula against the tbl_master using the system name as the key


Final Result (what I am trying to accomplish [---- represents highlight or something similar]):
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]System[/TD]
[TD]Voltage[/TD]
[TD]RU[/TD]
[TD]BTU[/TD]
[TD]110v[/TD]
[TD]208v[/TD]
[TD]KG[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]computer1[/TD]
[TD]208[/TD]
[TD]2[/TD]
[TD]1945[/TD]
[TD]2.7[/TD]
[TD]1.2[/TD]
[TD]17[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]computer2[/TD]
[TD]208[/TD]
[TD]1[/TD]
[TD]780[/TD]
[TD]1.4[/TD]
[TD].7[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]computer3[/TD]
[TD]208[/TD]
[TD]3[/TD]
[TD]1000[/TD]
[TD]3.0[/TD]
[TD]1.5[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[TD]----[/TD]
[/TR]
</tbody>[/TABLE]


Explanation of "----" in rows 3, 6,7:

• column C represents the amount of "U" in a computer rack
  • Using the value in C, I would like to automatically fill in the rows with conditional format or symbols (something to indicate the blank space is actually occupied)
  • E.g.
    • if C = 1, no fill necessary
    • if C = 2, fill in the row below with yellow
    • if C = 3, fill in the next 2 rows below
    • etc.....

Any assistance is appreciated.
Thanks
B

 

[bwinchell]

This formula seems to work just fine and do exactly what I want.

Can you explain the formula so I can understand more.
Thanks

 

[Peter_SSs]

Can you explain the formula so I can understand more.

Writing them is easier than explaining them, but I'll give it a go.

The formula changes depending on what row it is in. Let's look at what it would be in row 7 of my sample in post #9:
=AND(NOT(ISNUMBER($C7)),ROW()-LOOKUP(9.99E+307,$C$1:$C6,ROW($C$1:$C6))<LOOKUP(9.99E+307,$C$1:$C6))

The AND, means both the red and blue sections must be true to format the row.
The red part says that whatever is in column C must not be a number. That is True for row 7
The underlined section of the blue part firstly finds the last number in the range C1:C6 (that is the 3) and then returns the row number where it found that value (row 5). So that blue part becomes
ROW()-5<LOOKUP(9.99E+307,$C$1:$C6)
ROW() returns the row number that the formula is in (7) and the LOOKUP() part again returns the last number in the range C1:C6 (3)
So now we have
7-5<3 which is also True for row 7
Both True therefore row 7 is highlighted.

 

[bwinchell]

Thanks for all your assistance.

Attached RackCalculator_4.0.1 is a template of the computer rack elevation calculator I was working on. Hopefully this might help someone else.

1. Master sheet = fill in fields with your equipment details
2. M3-c1 to M3-c5 = the actual 42U computer racks. Choose the device in column B that occupies that U (the list is dynamically built from the entries in the Master sheet)
3. Comp_Racks = gives you an overall view of your racks (space, power, cooling, and cost)

B

 

[Peter_SSs]

Thanks for all your assistance.

You're welcome.