Value in one cell changes the Text Properties in another......

  • Thread starter Thread starter DoD_au
  • Start date Start date
D

DoD_au

Hi,

I've asked all the excel swami guru's here at work and i have them all
stumped :D

I have one worksheet with a list of RJ45 Port Numbers. In the next
coloumn I have a list of corresponding words "Yes" or "No" (connected
or not connected at the patch panel).

In another worksheet I have created a representation of the room and
have filled cells with the number of the port in them spaced exactly as
they are in the real room.

I was wondering if there was a function that i could use that would
take value "yes" or "no" and have that value change the text or cell
properties of the 'rj45 port' cells on the representation of the
physical room spreadsheet.

Eg, If the Value in the list is "No" make the corresponding Cell in the
representation to change fill to RED or make the Text RED.

This function would also have to be repeatable easily enough becuase i
have a lot of rooms to 'recreate' :D

How would i do this?
 
Use Conditional Formatting.

Assuming the lit is on Sheet1, and the representation is on Sheet2.

Selecet the cells on Sheet 2 to be formatted (assume A1 is the first)
In CF, set Condition1 to Formula Is
Add the formula =INDEX(Sheet1!B:B,MATCH(A1,Sheet1!A:A,0)*1)
Click Foramt and set to suit
OK
OK


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
It said it wouldnt do it across sheets. So i put the representation o
the same sheet as the data and removed references to the other sheet
as outlined in your formula.

But the conditional formating did nothing....

Was i supposed to substitute anything else (like the "No" or "Yes"
into the forumla to set the particular format i want
 
Sorry mate, I gave you a duffer there. This should work for you.

Firstly, on Sheet1, select column A and add a workbook name of RJ45Ids (menu
Insert>Name>Define ... with a name of RJ45Ids)
Then,. select column B and add a workbook name of RJ45Statuses
Select the cells on Sheet 2 to be formatted (assume A1 is the first)
In CF, set Condition1 to Formula Is
Add the formula =INDEX(RJ45Statuses,MATCH(A1,RJ45Ids,0),1)
Click Format and set to suit
OK
OK

Hopefully this will work (I have tested it now).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
It's not how you described. You only put part of the port number in the cell
not the whole port.
Try this version of the formula G10, and copy to the rest

=INDEX(RJ45Statuses,MATCH("R6-363-"&G9,RJ45Ids,0),1)="No"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Sorry about that....I started to realise that was the problem after
staring at the spreadsheet like a cow stares at an oncoming train and
thought "Maybe thats why its not working" :D

Needless to say, that formula works.....Do i have to manually copy that
formula (and modify the cell location) for each cell on the
representation? or can i apply that formula in a blanket kinda way
across the whole representation?
 
GREAT! it works.....B-E-A-Utiful!

Thanks for your help...shout yourself a Crown Larger......

-Natha
 
Back
Top