Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this...as
well as for aesthetics.

Any advice...has anybody else tried this before?

Nope. There's nothing like that in format|conditional formatting--well, at
least through xl2003.



SteveMax wrote:

Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this...as
well as for aesthetics.

Any advice...has anybody else tried this before?

--

Dave Peterson

AFAIK, you can't do it without code.

BUT, keep in mind that VB absolutely HATES merged cells.... makes
codes screw up. can't you just use additional columns?

how are you currently having it populate? via data validation?
susan




On Jul 9, 9:38 am, SteveMax
wrote:
Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this...as
well as for aesthetics.

Any advice...has anybody else tried this before?

Thanks for the response Susan. I may not have painted the picture well
enough, or possibly misunderstanding the benefit of additional columns in
this context.

Basically I have created a worksheet tab which generates Equipment Rack
Drawings, from another worksheet that contains the flat data & named ranges
to pull & populate from automatically. So I have Columns B, C, & D sized at
2.5, 16.75, & 2.5 respectively...skip Column E, rinse & repeat the sizes.
This give an appearance of an Equipment rack(cabinet) with Rack Unit(RU)
identifier numbers(Columns B & D) surrounding Devices which are racked
up(Column C). These are 44 RU cabinets so I begin in Row 7, and I end in Row
50 in descending order.

I have included the formula below from Cell C7, which I use to drag down to
C50. This renders my Devices which are 1 RU in height as repeated text. This
works ok for me as the data used is a unique Device Name...but naturally, if
I could have the sheet, or range of cells, automatically merge & unmerge
(with Borders & Color potentially) for certain reasons (i.e. contiguous cells
with identical results), then it would be a more aesthetically pleasing
drawing to look at...rather than C7 & C8 repeating the same information.

I hope I articulated the scenario correctly...but I believe you & Dave have
both confirmed my initial assumption that it would require some VB code...and
even then would not be very easy...relatively speaking. One more note...this
workbook has been done in 2007, but I dont believe the functions used are
unique to 2007.

C$4 is just the Rack Number which is used to help find it in the
"Rackup_Name" range.

=IF(ISERROR(VLOOKUP(C$4&"-"&B7,Rackup_Name,4,FALSE)),IF(ISERROR(VLOOKUP(C8,R ackup_Device_Name,1,FALSE)),"",IF(VLOOKUP(C8,Racku p_Device_Name,2,FALSE)+VLOOKUP(C8,Rackup_Device_Na me,6,FALSE)B7,C8,"")),VLOOKUP(C$4&"-"&B7,Rackup_Name,4,FALSE))


Regards,
Steve

"Susan" wrote:

AFAIK, you can't do it without code.

BUT, keep in mind that VB absolutely HATES merged cells.... makes
codes screw up. can't you just use additional columns?

how are you currently having it populate? via data validation?
susan




On Jul 9, 9:38 am, SteveMax
wrote:
Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this...as
well as for aesthetics.

Any advice...has anybody else tried this before?

well, i'm one of those kinesthetic "hands-on" learners, & i can't
visualize what you're trying to describe. although i'm sure you're
saying it perfectly understandably!
i'd be happy to take a look at it via e-mail, but i don't have excel
07 (have 2k). send it if you wish.
:)
susan



On Jul 9, 2:16 pm, SteveMax
wrote:
Thanks for the response Susan. I may not have painted the picture well
enough, or possibly misunderstanding the benefit of additional columns in
this context.

Basically I have created a worksheet tab which generates Equipment Rack
Drawings, from another worksheet that contains the flat data & named ranges
to pull & populate from automatically. So I have Columns B, C, & D sized at
2.5, 16.75, & 2.5 respectively...skip Column E, rinse & repeat the sizes.
This give an appearance of an Equipment rack(cabinet) with Rack Unit(RU)
identifier numbers(Columns B & D) surrounding Devices which are racked
up(Column C). These are 44 RU cabinets so I begin in Row 7, and I end in Row
50 in descending order.

I have included the formula below from Cell C7, which I use to drag down to
C50. This renders my Devices which are 1 RU in height as repeated text. This
works ok for me as the data used is a unique Device Name...but naturally, if
I could have the sheet, or range of cells, automatically merge & unmerge
(with Borders & Color potentially) for certain reasons (i.e. contiguous cells
with identical results), then it would be a more aesthetically pleasing
drawing to look at...rather than C7 & C8 repeating the same information.

I hope I articulated the scenario correctly...but I believe you & Dave have
both confirmed my initial assumption that it would require some VB code....and
even then would not be very easy...relatively speaking. One more note...this
workbook has been done in 2007, but I dont believe the functions used are
unique to 2007.

C$4 is just the Rack Number which is used to help find it in the
"Rackup_Name" range.

=IF(ISERROR(VLOOKUP(C$4&"-"&B7,Rackup_Name,4,FALSE)),IF(ISERROR(VLOOKUP(C8,* Rackup_Device_Name,1,FALSE)),"",IF(VLOOKUP(C8,Rack up_Device_Name,2,FALSE)+V*LOOKUP(C8,Rackup_Device_ Name,6,FALSE)B7,C8,"")),VLOOKUP(C$4&"-"&B7,Rackup_*Name,4,FALSE))

Regards,
Steve



"Susan" wrote:
AFAIK, you can't do it without code.

BUT, keep in mind that VB absolutely HATES merged cells.... makes
codes screw up. can't you just use additional columns?

how are you currently having it populate? via data validation?
susan

On Jul 9, 9:38 am, SteveMax
wrote:
Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this....as
well as for aesthetics.

Any advice...has anybody else tried this before?- Hide quoted text -

- Show quoted text -

File sent...let me know if there are any problems with the conversion.

Thanks,
Steve

"Susan" wrote:

well, i'm one of those kinesthetic "hands-on" learners, & i can't
visualize what you're trying to describe. although i'm sure you're
saying it perfectly understandably!
i'd be happy to take a look at it via e-mail, but i don't have excel
07 (have 2k). send it if you wish.
:)
susan



On Jul 9, 2:16 pm, SteveMax
wrote:
Thanks for the response Susan. I may not have painted the picture well
enough, or possibly misunderstanding the benefit of additional columns in
this context.

Basically I have created a worksheet tab which generates Equipment Rack
Drawings, from another worksheet that contains the flat data & named ranges
to pull & populate from automatically. So I have Columns B, C, & D sized at
2.5, 16.75, & 2.5 respectively...skip Column E, rinse & repeat the sizes.
This give an appearance of an Equipment rack(cabinet) with Rack Unit(RU)
identifier numbers(Columns B & D) surrounding Devices which are racked
up(Column C). These are 44 RU cabinets so I begin in Row 7, and I end in Row
50 in descending order.

I have included the formula below from Cell C7, which I use to drag down to
C50. This renders my Devices which are 1 RU in height as repeated text. This
works ok for me as the data used is a unique Device Name...but naturally, if
I could have the sheet, or range of cells, automatically merge & unmerge
(with Borders & Color potentially) for certain reasons (i.e. contiguous cells
with identical results), then it would be a more aesthetically pleasing
drawing to look at...rather than C7 & C8 repeating the same information.

I hope I articulated the scenario correctly...but I believe you & Dave have
both confirmed my initial assumption that it would require some VB code....and
even then would not be very easy...relatively speaking. One more note...this
workbook has been done in 2007, but I dont believe the functions used are
unique to 2007.

C$4 is just the Rack Number which is used to help find it in the
"Rackup_Name" range.

=IF(ISERROR(VLOOKUP(C$4&"-"&B7,Rackup_Name,4,FALSE)),IF(ISERROR(VLOOKUP( C8,-Rackup_Device_Name,1,FALSE)),"",IF(VLOOKUP(C8,Rack up_Device_Name,2,FALSE)+V-LOOKUP(C8,Rackup_Device_Name,6,FALSE)B7,C8,"")),V LOOKUP(C$4&"-"&B7,Rackup_-Name,4,FALSE))

Regards,
Steve



"Susan" wrote:
AFAIK, you can't do it without code.

BUT, keep in mind that VB absolutely HATES merged cells.... makes
codes screw up. can't you just use additional columns?

how are you currently having it populate? via data validation?
susan

On Jul 9, 9:38 am, SteveMax
wrote:
Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this....as
well as for aesthetics.

Any advice...has anybody else tried this before?- Hide quoted text -

- Show quoted text -

i got it...... reviewing........ NOW i understand what you mean.
thinking about it.
:)
susan


On Jul 10, 9:00 am, SteveMax
wrote:
File sent...let me know if there are any problems with the conversion.

Thanks,
Steve



"Susan" wrote:
well, i'm one of those kinesthetic "hands-on" learners, & i can't
visualize what you're trying to describe. although i'm sure you're
saying it perfectly understandably!
i'd be happy to take a look at it via e-mail, but i don't have excel
07 (have 2k). send it if you wish.
:)
susan

On Jul 9, 2:16 pm, SteveMax
wrote:
Thanks for the response Susan. I may not have painted the picture well
enough, or possibly misunderstanding the benefit of additional columns in
this context.

Basically I have created a worksheet tab which generates Equipment Rack
Drawings, from another worksheet that contains the flat data & named ranges
to pull & populate from automatically. So I have Columns B, C, & D sized at
2.5, 16.75, & 2.5 respectively...skip Column E, rinse & repeat the sizes.
This give an appearance of an Equipment rack(cabinet) with Rack Unit(RU)
identifier numbers(Columns B & D) surrounding Devices which are racked
up(Column C). These are 44 RU cabinets so I begin in Row 7, and I end in Row
50 in descending order.

I have included the formula below from Cell C7, which I use to drag down to
C50. This renders my Devices which are 1 RU in height as repeated text. This
works ok for me as the data used is a unique Device Name...but naturally, if
I could have the sheet, or range of cells, automatically merge & unmerge
(with Borders & Color potentially) for certain reasons (i.e. contiguous cells
with identical results), then it would be a more aesthetically pleasing
drawing to look at...rather than C7 & C8 repeating the same information.

I hope I articulated the scenario correctly...but I believe you & Dave have
both confirmed my initial assumption that it would require some VB code....and
even then would not be very easy...relatively speaking. One more note....this
workbook has been done in 2007, but I dont believe the functions used are
unique to 2007.

C$4 is just the Rack Number which is used to help find it in the
"Rackup_Name" range.

=IF(ISERROR(VLOOKUP(C$4&"-"&B7,Rackup_Name,4,FALSE)),IF(ISERROR(VLOOKUP( C8,*-Rackup_Device_Name,1,FALSE)),"",IF(VLOOKUP(C8,Rack up_Device_Name,2,FALSE)+*V-LOOKUP(C8,Rackup_Device_Name,6,FALSE)B7,C8,"")),V LOOKUP(C$4&"-"&B7,Racku*p_-Name,4,FALSE))

Regards,
Steve

"Susan" wrote:
AFAIK, you can't do it without code.

BUT, keep in mind that VB absolutely HATES merged cells.... makes
codes screw up. can't you just use additional columns?

how are you currently having it populate? via data validation?
susan

On Jul 9, 9:38 am, SteveMax
wrote:
Is there any way to accomplish this? Preferrably without VB code(although it
would seem unlikely to me any other way).

Basically what I have is a worksheet which imitates an Equipment Rack Facial
drawing(say "Rack Profile"). It dynamically populates the data from the
"Devices" worksheet and displays the corresponding equipment rackup RU &
name. It also populates the Power requirements, and is selectable by Row, in
11 Rack increments.

The device names currently populate, but I have to repeat the device name
anytime a device is 1 RU. I was hoping to use Conditional Formatting for
device color & outlining...but I thought that if I could get the cells to
merge/unmerge dynamically, I could use a more simple formula for this....as
well as for aesthetics.

Any advice...has anybody else tried this before?- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -