A mix of Merged/Split Cells in a row to reflect a "family" of separate actions relatint to a "parent" cell?

R

RichieA

New Member
Joined
Aug 20, 2015
Messages
16
Hi all - I'm hoping this is something that can be fixedin Excel to be achieved in one single sheet.

I'm building a worksheet to hold client account plan summaries with multipleentries of a client, by my involved offices/teams, with text summaries but cruciallyincorporating a list of actions with due dates that can be tracked (usingconditional formatting that I already have tested). A basic example can be seenin Example 1 below.

However, I need to represent multiple actions by office/team per client. Asthese due dates will be tracked with conditional formatting I THINK I need toenter each action in its own row so the date can be tracked, and actions sortedaccordingly. I have shown a new action added in Example 2 below in red. Howeverto achieve this I had to enter a new row, enter the new action and date, andthen merge down the relevant "parent" cell with general text to theleft, whilst keeping the columns distinct. This is very labour intensiveespecially as there may be 200+ clients with 500+ actions to update regularly.

So is there a way to either:
a) automate the insertion of a new row to allow the relevant action to beentered, but then merge all the cells, keeping their sep columns, to the leftof the actions area or
b) include multiple actions with dates in one cell, where the dates can stillbe tracked and conditionally formatted, negating the ned to enter a new roweach time?

I'm thinking it may be a scenario for macros - but I know nothing about them.

If not, I'll need to have a second sheet purely to reflect the relevant actionsper client, with the main sheet being the client account plan text.

Many thanks in advance

R


[TABLE="width: 615"]
<colgroup><col style="width: 138pt; mso-width-source: userset; mso-width-alt: 6729;" width="184"> <col style="width: 87pt; mso-width-source: userset; mso-width-alt: 4242;" width="116"> <col style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;" width="101"> <col style="width: 86pt; mso-width-source: userset; mso-width-alt: 4205;" width="115"> <col style="width: 101pt; mso-width-source: userset; mso-width-alt: 4937;" width="135"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;" width="81"> <tbody>[TR]
[TD="class: xl414, width: 184, bgcolor: transparent"]Example 1[/TD]
[TD="width: 116, bgcolor: transparent"][/TD]
[TD="width: 101, bgcolor: transparent"][/TD]
[TD="width: 115, bgcolor: transparent"][/TD]
[TD="width: 135, bgcolor: transparent"][/TD]
[TD="width: 88, bgcolor: transparent"][/TD]
[TD="width: 81, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #CCC0DA"]Client[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Office[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Value[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Latest Event[/TD]
[TD="class: xl427, bgcolor: #CCC0DA"]Next Action[/TD]
[TD="class: xl430, bgcolor: #CCC0DA"]Who[/TD]
[TD="class: xl430, bgcolor: #CCC0DA"]When[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl415, bgcolor: #F2F2F2"]Austria[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£25[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Sold 1000 units[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Arrange meet[/TD]
[TD="class: xl431, bgcolor: transparent"]JP[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]25-Aug[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Belgium[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£63[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Visit complete[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Supply goods[/TD]
[TD="class: xl431, bgcolor: transparent"]RA[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]14-Sep[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Canada[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£564[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Visit complete[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Supply goods[/TD]
[TD="class: xl431, bgcolor: transparent"]MS[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]14-Sep[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Denmark[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£34[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Shut down[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Shut down[/TD]
[TD="class: xl431, bgcolor: transparent"]NK[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]12-Dec[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Egypt[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£67[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl415, bgcolor: #F2F2F2"]Austria[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£42[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Belgium[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£40[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Canada[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£32[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Lunch[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Rugby[/TD]
[TD="class: xl431, bgcolor: transparent"]FG[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]14-Oct[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Denmark[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£56[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Egypt[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£40[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl414, bgcolor: transparent"]Example 2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl413, bgcolor: #CCC0DA"]Client[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Office[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Value[/TD]
[TD="class: xl413, bgcolor: #CCC0DA"]Latest Event[/TD]
[TD="class: xl427, bgcolor: #CCC0DA"]Next Action[/TD]
[TD="class: xl430, bgcolor: #CCC0DA"]Who[/TD]
[TD="class: xl430, bgcolor: #CCC0DA"]When[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl415, bgcolor: #F2F2F2"]Austria[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£25[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Sold 1000 units[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Arrange meet[/TD]
[TD="class: xl431, bgcolor: transparent"]JP[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]25-Aug[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Belgium[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£63[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Visit complete[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Supply goods[/TD]
[TD="class: xl431, bgcolor: transparent"]RA[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]14-Sep[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Canada[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£564[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Visit complete[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Supply goods[/TD]
[TD="class: xl431, bgcolor: transparent"]MS[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]14-Sep[/TD]
[/TR]
[TR]
[TD="class: xl419, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl421, width: 116, bgcolor: #F2F2F2"]Denmark[/TD]
[TD="class: xl423, bgcolor: #F2F2F2"]£34[/TD]
[TD="class: xl425, bgcolor: #F2F2F2"]Shut down[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Shut down[/TD]
[TD="class: xl431, bgcolor: transparent"]NK[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]12-Dec[/TD]
[/TR]
[TR]
[TD="class: xl429, bgcolor: #F2F2F2"]Review[/TD]
[TD="class: xl433, bgcolor: transparent"]NK[/TD]
[TD="class: xl434, bgcolor: transparent, align: right"]01-Sep[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Alpha Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Egypt[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£67[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl415, bgcolor: #F2F2F2"]Austria[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£42[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Belgium[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£40[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Canada[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£32[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"]Lunch[/TD]
[TD="class: xl428, bgcolor: #F2F2F2"]Rugby[/TD]
[TD="class: xl431, bgcolor: transparent"]FG[/TD]
[TD="class: xl432, bgcolor: transparent, align: right"]14-Oct[/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Denmark[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£56[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl415, bgcolor: #F2F2F2"]Bravo Inc[/TD]
[TD="class: xl418, width: 116, bgcolor: #F2F2F2"]Egypt[/TD]
[TD="class: xl416, bgcolor: #F2F2F2"]£40[/TD]
[TD="class: xl417, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl428, bgcolor: #F2F2F2"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[TD="class: xl431, bgcolor: transparent"] [/TD]
[/TR]
</tbody>[/TABLE]



 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
Apologies - the example I copied in displayed incorrectly making the requirement less clear. Hopefully the below, shows the red text in the right location:

[TABLE="width: 884"]
<tbody>[TR]
[TD]Example 2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Client
[/TD]
[TD]Office
[/TD]
[TD]Value
[/TD]
[TD]Latest Event
[/TD]
[TD]Next Action
[/TD]
[TD]Who
[/TD]
[TD]When
[/TD]
[/TR]
[TR]
[TD]Alpha Inc
[/TD]
[TD]Austria
[/TD]
[TD]£25
[/TD]
[TD]Sold 1000 units
[/TD]
[TD]Arrange meet
[/TD]
[TD]JP
[/TD]
[TD]25-Aug
[/TD]
[/TR]
[TR]
[TD]Alpha Inc
[/TD]
[TD]Belgium
[/TD]
[TD]£63
[/TD]
[TD]Visit complete
[/TD]
[TD]Supply goods
[/TD]
[TD]RA
[/TD]
[TD]14-Sep
[/TD]
[/TR]
[TR]
[TD]Alpha Inc
[/TD]
[TD]Canada
[/TD]
[TD]£564
[/TD]
[TD]Visit complete
[/TD]
[TD]Supply goods
[/TD]
[TD]MS
[/TD]
[TD]14-Sep
[/TD]
[/TR]
[TR]
[TD]Alpha Inc
[/TD]
[TD]Denmark
[/TD]
[TD]£34
[/TD]
[TD]Shut down
[/TD]
[TD]Shut down
[/TD]
[TD]NK
[/TD]
[TD]12-Dec
[/TD]
[/TR]
[TR]
[TD]Review
[/TD]
[TD]NK
[/TD]
[TD]01-Sep
[/TD]
[/TR]
[TR]
[TD]Alpha Inc
[/TD]
[TD]Egypt
[/TD]
[TD]£67
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bravo Inc
[/TD]
[TD]Austria
[/TD]
[TD]£42
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bravo Inc
[/TD]
[TD]Belgium
[/TD]
[TD]£40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bravo Inc
[/TD]
[TD]Canada
[/TD]
[TD]£32
[/TD]
[TD]Lunch
[/TD]
[TD]Rugby
[/TD]
[TD]FG
[/TD]
[TD]14-Oct
[/TD]
[/TR]
[TR]
[TD]Bravo Inc
[/TD]
[TD]Denmark
[/TD]
[TD]£56
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]Bravo Inc
[/TD]
[TD]Egypt
[/TD]
[TD]£40
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
N
  • Question Question
excel macro to split data into multiple worksheets based on conditions worksheet
Replies
4
Views
4K
Fluff
Fluff
D
  • Question Question
Use macro to paste new info into a cell location found by another formula
Replies
2
Views
595
Dayne
D
M
  • Question Question
RANK(SUMPRODUCT Unique Ranking With Tie Breaker Problem
Replies
1
Views
2K
MrJoshua
M
D
  • Question Question
Maintaining Historical Data
Replies
1
Views
592
dragonmouse
D

Forum statistics

Threads
1,223,230
Messages
6,170,883
Members
452,364
Latest member
springate

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top