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

 

[Stacko]

I would start by putting an expression in column C. For C6 it would be if C4 is 3 or C5 is 2 then ---- else ' '. Then fill the other columns based on C.

This will work as you add rows (and thus overwrite the formulae) but not if you move a computer out of a position. You would need to copy the formula back in and I'm not sure you gain much over manual editing.

 

[bwinchell]

Hi Stacko,
I would not be able to put a formula in column 'C' as it already has an Index formula to initially populate C-G
Thanks
B

 

[Peter_SSs]

Welcome to the MrExcel board!

I would not be able to put a formula in column 'C' as it already has an Index formula ..

So, in your sample above, what exactly shows in cells C3, C6 and C7 on your sheet?

 

[bwinchell]

Here is the actual formula that resides in C-G

=INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),13)

So it basically finds the key in the MASTER_tbl and then finds the appropriate referenced column for a value.

C3, C6, C7 (in the example above):
Have no entry as A is empty. Once you pick an entry in A from the dynamic list created by the key in MASTER_tbl, then those columns will populate.

What I am trying to accomplish is:
1. Once you pick an item in A2, items in C-G are populated via INDEX
a. This works
2. If C2 != blank or C2 >1
a. Colour fill the next X rows
i. X = C2-1

Item 2 is where I am stuck.

Hopefully I explained this clearly in my broken "Excel".
Thanks
B

 

[Peter_SSs]

Once you pick an entry in A from the dynamic list created by the key in MASTER_tbl, then those columns will populate.

How does that happen, say in row 3, given that you have said C3 is empty.

If C3 is empty but gets populated by a formula if A3 has a value entered, how does the formula then get into C3? Manually? Code? Something else?

 

[bwinchell]

=INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),22)
=IF(B2=Varibles!A2,INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),20),0)
=IF(B2=Varibles!A3,INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),21),0)
=INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),15)

The formula above resides in every cell in columns C-G.

So if you do not pick an entry in column A, then the INDEX finds nothing (in reality it actually comes back with 0 or #N/A.... maybe this is what you were looking for).

Thanks
B

 

[bwinchell]

Correct above:

I left out a formula.

=INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),13)
=INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),22)
=IF(B2=Varibles!A2,INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),20),0)
=IF(B2=Varibles!A3,INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),21),0)
=INDEX(MASTER_tbl,MATCH('M3-C1'!A2,MASTER_tbl[Name],0),15)

C = top
G= bottom

 

[Peter_SSs]

I don't think the actual formula will be relevant to the problem. I was asking the questions to determine if those cells in column C were actually empty or not. You had initially said that they did contain formulas but then I was hesitant after you stated

C3, C6, C7 (in the example above):
Have no entry

We still may need a more substantial set of sample data, with color applied, but see if this is headed in the right direction.

Select A2:G??
Home ribbon tab -> Conditional Formatting -> New rule... -> Use a formula to determine which cells to format -> Format values where this formula is true -> Enter formula from below screen shot -> Format -> apply the format you want -> OK -> Ok

Excel Workbook
	A	B	C	D	E	F	G
1	System	Voltage	RU	BTU	110v	208v	KG
2	computer1	208	2	1945	2.7	1.2	17
3
4	computer2	208	1	780	1.4	0.7	7
5	computer3	208	3	1000	3	1.5	11
6
7
8
9			4
10			2
11
12
13			1
14			1
15			3
16
17
18
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2	1. / Formula is =AND(NOT(ISNUMBER($C2)),ROW()-LOOKUP(9.99E+307,$C$1:$C1,ROW($C$1:$C1))	Abc

 

[bwinchell]

That seems to have done the trick.

Let me do some double checking over the weekend to verify and I will upload the finished product if it all works out.
Thanks
B