Dynamically Megre/Unmerge Cells
 

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?

Dynamically Megre/Unmerge Cells
 

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

Dynamically Megre/Unmerge Cells
 

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?

Dynamically Megre/Unmerge Cells
 

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?

Dynamically Megre/Unmerge Cells
 

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 -

Dynamically Megre/Unmerge Cells
 

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 -

Dynamically Megre/Unmerge Cells
 

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 -

Dynamically Megre/Unmerge Cells
 

okie dokie. the short answer is: yes, it can be done via code.

the long answer is: yes, it can be done, but it's going to be very
complicated. it's going to require the coding to loop through each
device name grouping (rows 7-50) & look for duplicates. then it's
going to have to make the value of those duplicates a variable, erase
the formulas, color the duplicates, remove the lines between (so it
looks like it's one cell), & enter the variable in the center cell.
(trust me, you don't want to merge them all because then you'd have to
unmerge them before you could go to the next rack group.)

then, after you've got it looking so nice & wonderful, you're going to
have to have a combobox_change code attached to the rack group
dropdown that will UNDO everything you've just done above, including
repopulating the cells with the correct formulas, BEFORE it tries to
load a new rack group.

of course, although my codings work, i have a horrible habit of making
things much more complicated than they need to be! so it would take
quite some time to work this all out, and then pass it into the
excel.programming newsgroup to see how it can be shortened up.

you might try starting a new thread in the .programming group &
perhaps somebody more experienced than i will have a (better)
suggestion.
:)
susan



On Jul 10, 10:11 am, Susan wrote:
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

<clipped text

Dynamically Megre/Unmerge Cells
 

Thank you very much for looking at this Susan. Unfortunately, for a couple of
reasons, I dont think I can use any code for this. Long story short, the same
reasons I cannot use code, are the same reasons I cant have my software
developers develop an actual application. :( Gotta love bureaucracy.

In any event...your insight & expertise is very much appreciated.


Regards,
Steve

"Susan" wrote:

okie dokie. the short answer is: yes, it can be done via code.

the long answer is: yes, it can be done, but it's going to be very
complicated. it's going to require the coding to loop through each
device name grouping (rows 7-50) & look for duplicates. then it's
going to have to make the value of those duplicates a variable, erase
the formulas, color the duplicates, remove the lines between (so it
looks like it's one cell), & enter the variable in the center cell.
(trust me, you don't want to merge them all because then you'd have to
unmerge them before you could go to the next rack group.)

then, after you've got it looking so nice & wonderful, you're going to
have to have a combobox_change code attached to the rack group
dropdown that will UNDO everything you've just done above, including
repopulating the cells with the correct formulas, BEFORE it tries to
load a new rack group.

of course, although my codings work, i have a horrible habit of making
things much more complicated than they need to be! so it would take
quite some time to work this all out, and then pass it into the
excel.programming newsgroup to see how it can be shortened up.

you might try starting a new thread in the .programming group &
perhaps somebody more experienced than i will have a (better)
suggestion.
:)
susan



On Jul 10, 10:11 am, Susan wrote:
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

<clipped text