Adjust Multiple Cell Borders Based on Cell Contents

**USFishin** · 04-06-2012, 06:50 PM

I am an electrical engineer and am trying to automate our electrical panel schedule a little bit. I've attached it for reference. We have 3 different size breakers that we enter into the the panel schedule, single pole, two pole, and 3 pole. When we enter the 2 and 3 pole breakers we like to eliminate the cell borders that go through the cells containing the breaker. Right now we do it manually, but I'd like to see if there is some way to automate it.

So for example, when I enter "3P" into cell L12 I'd like it to eliminate both top and bottom borders in cells L13 and Q-S13 as you can see from the example I have lower down in the attachment.

Same goes for when I enter "2P", I'd like it to eliminate the bottom borders to make the 2 cells appear as single 2-pole breaker.

Is there any way to automate this? I tried messing around with conditional formatting a little bit but I don't think that's going to do what I'm trying to do. Any suggestions?

**DGagnon** · 04-06-2012, 08:22 PM

You can set up conditional formating, but it will be alot of different rules. for example

in cell L12 =right($L12,1)="P" remove bottom border
in cell L13 =$L12="2P" remove top border
in cell L13 =$L12="3P" remove top and bottom border
in cell L14 =$L12="3P" remove top border

then a set like that in Q-S of the same rows, refering back to L12

let me know if you need an example of this.

**Mallycat** · 04-06-2012, 10:09 PM

Originally Posted by USFishin

Is there any way to automate this?

Yes, conditional formatting is the way to go. It can be tricky when the rules are so complex, but there are a couple of techniques you can use to solve this problem. First trick is to break the problem down into parts and the second trick is to solve the problem in the spreadsheet first, then create the conditional format last. I have attached my worksheet for you to follow.

1. determine if the current line should have the bottom border removed based on the BRKR column on the same line. Anything with a "P" meets this criteria. See formula in column V.
2. determine if the current line should have the bottom border removed based on the BRKR column on the line above. only a 3P meets this criteria. See formula in column W.
3. determine if the current line should have a bottom border considering the 2 rules above. See formula in column X.
4. Create one super formula that gives the answer without needing columns V,W,X. See forumula in column Y.

Now just copy the formual from cell Y12, highlight range Q12:T32 and apply a conditional format. Select the option "use a formula to determine which cells to format" and paste your formula. Then set the format to remove the bottom border.

Note: it is important when you do this last step that you are careful and aware of the "active cell" in the highlighted range. In this case, the active cell is Q12 (you will see it is a different colour than the other highlighted cells. The formula you paste into the conditional formatting box will be referenced to this cell, so the formula must take that into account. It will be then copied to every other hightlighted cell using normal Excel copy rules, which is why the $ is required to make sure that the formula always refers to the BRKR column L.

For the record, this is the formula (also known as a mega formula).

Please Login or Register  to view this content.

They are impossible to understand, but that is not needed because you built the formula from the bottom up as described above. It is a good idea to keep the working columns (V:Y) hidden so that if you have any problems, you can go back and sort it out.

EDIT: after I had finished, I realised that I could have made the formula in column W simpler. I will leave that for you to do for practice :-)

**USFishin** · 04-09-2012, 09:07 AM

Thanks for your replies guys. I'll play around with this and let you know how it works out. While I've got you, there's one more thing I was trying to do but couldn't figure it out. It's probably something simple I'm missing. The code that I have in the "wire" column, I'm trying to make it do the following but can't seem to get it without adverse affects:

You enter a "3P" breaker into L12 then "20" in L14 (leaving L13 blank) and it enters "12" in M12-14. Right now it only fills in M12 and M14 but you have to enter "20" into L13 for the M13 to show up. What am I missing in my code in the L column?

**DGagnon** · 04-09-2012, 10:04 AM

Try this, excel has trouble comparing text to an empty cell sometimes.

=IF(ISNUMBER(FIND("3P",L11)),M13,IF(ISNUMBER(FIND("3P",L12)),M14,IF(ISNUMBER(FIND("2P",L12)),M13,VLOOKUP(L12,V:W,2,0))))

**USFishin** · 04-09-2012, 11:04 AM

When I use that code and copy it down through the "WIRE" column I end up with a "900" in that column for all of the rows. When I enter a value into the "BRKR" column it changes to the correct value. How can I make the "900" just be blank if nothing is entered into the "BRKR" column?

**DGagnon** · 04-09-2012, 11:16 AM

there was an inadvertant space in the formula try this

=IF(ISNUMBER(FIND("3P",L11)),M13,IF(ISNUMBER(FIND("3P",L12)),M14,IF(ISNUMBER(FIND("2P",L12)),M13,VLOOKUP(L12,V:W,2,0))))

it should return 0 when it should be blank, inorder to convert the 0's to "" you could do something like this

=IF(IF(ISNUMBER(FIND("3P",L11)),M13,IF(ISNUMBER(FIND("3P",L12)),M14,IF(ISNUMBER(FIND("2P",L12)),M13,VLOOKUP(L12,V:W,2,0))))=0,"",IF(ISNUMBER(FIND("3P",L11)),M13,IF(ISNUMBER(FIND("3P",L12)),M14,IF(ISNUMBER(FIND("2P",L12)),M13,VLOOKUP(L12,V:W,2,0)))))

**USFishin** · 04-09-2012, 12:12 PM

When I copy the 2nd code you posted into the "WIRE" column it seems ok (the cell remains blank), but when I enter a value into the adjacent "BRKR" column the "WIRE" column cell displays "#####"

**DGagnon** · 04-09-2012, 12:33 PM

I made the same 'typo' again, not sure how the space keeps ending up in the formula, try this though

=IF(IF(ISNUMBER(FIND("3P",L11)),M13,IF(ISNUMBER(FIND("3P",L12)),M14,IF(ISNUMBER(FIND("2P",L12)),M13,VLOOKUP(L12,V:W,2,0))))=0,"",IF(ISNUMBER(FIND("3P",L11)),M13,IF(ISNUMBER(FIND("3P",L12)),M14,IF(ISNUMBER(FIND("2P",L12)),M13,VLOOKUP(L12,V:W,2,0)))))

Edit: it appears that a space is being added into the formula in the middle of one of the 'ISNUMBER' portion, please delete that space before using formula, i cannot get it to disapear in this post.

**USFishin** · 04-09-2012, 02:33 PM

That worked! Thanks to both of you guys for all of your help!!

**USFishin** · 04-09-2012, 05:44 PM

OK, now I'm running into one more issue. All of the above issues have been cleared up and are working nicely by the way. In my VLOOKUP table I have all numbers in my "Breaker Size" table but in the "Wire Size" side I have some text as well as numbers. The numbers are displaying fine but the items that are text aren't coming in. If I can somehow display those items as numbers then it would work but excel won't allow a zero in the denominator of a fraction. So the 1/0, 2/0, 3/0, and 4/0 won't show up when I enter the 150, 175, 200 and 225 breakers. Any suggestions?

**DGagnon** · 04-09-2012, 05:54 PM

try putting a ' before the entry, so it would be entered as '1/0, the ' should not be seen, but force it to act as text.

**USFishin** · 04-09-2012, 07:05 PM

I think it has to be seen as a number and not text. If I change the cell type of one of the numbers in my Vlookup table that is working properly to text instead of number then it doesn't work.

**DGagnon** · 04-09-2012, 07:59 PM

maybe i misunderstood what you are doing, could you attach again with the trouble sections highlighted?

**USFishin** · 04-09-2012, 10:32 PM

The file is attached. The highlighted sections are the only ones not working. The "non numbers" aren't being reflected from the Vlookup table into the corresponding "WIRE" column.

Also, I just noticed that when the "900" under the "Wire Size" Vlookup table is entered and in number form then it fills all blank cells in as "900." I left it there so you could see the effect. If you delete the 900 in the Wire Size Vlookup table they all go away.

**DGagnon** · 04-09-2012, 11:33 PM

not sure what i was thinking earlier, here is a better formula that should work for you

=IF(TEXT(L12,"0")="2P",M13,IF(TEXT(L12,"0")="3P",M13,IF(TEXT(L11,"0")="3P",M13,IF(L12="","",VLOOKUP(L12,V:W,2,0)))))

**USFishin** · 04-10-2012, 12:56 AM

Perfect! That did it. Thanks so much!

Adjust Multiple Cell Borders Based on Cell Contents

LinkBack

Thread Tools

Rate This Thread

Display

Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Re: Adjust Multiple Cell Borders Based on Cell Contents

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions