Auto insert Text into empty row

**cindy7** · 08-02-2007, 12:44 PM

Hi,

I am try to auto insert "Text" into all the empty cell A and insert SUM into cell B, here is my spread sheet:
1 Col A ColB ColC
2 Book 1 4/25/07
3 Paper 2 4/27/07
4
5 Pen 1 5/5/07
6 Booka 2 5/15/07
7 Bookb 1 5/21/07
8
9 Papera 1 8/2/07
10 Paperb 1 8/24/07
11

In the ColA cell (1,4) and ColC the month is "4" then I would like to insert "Total 4"
In the ColA cell (1,8) and ColC the month is "5" then I would like to insert "Total 5" and so on....

In the ColB cell (2,4) then I would like to insert "SUM=3" fomular in each empty cell on B.

Any help is very appreciate.
Thanks

**royUK** · 08-02-2007, 12:56 PM

I can't follow your layout, attach a small zipped example workbook, but I would consider using a pivotTable

**Ron Coderre** · 08-02-2007, 01:19 PM

Hi, Cindy7

This isn't exactly what you asked for but....
Have you considered letting Automatic Subtotals do the heavy lifting for you?

Example (using your data in A1:C10)

1)Delete all the blank rows
2)D1: Month
3)D2: ="Mth: "&TEXT(C2,"mm")
4)Copy D2 down as far as you need

5)Select the data range (now A1:D8)

6)From the Excel Main Menu:
<data><subtotals>
At each change in: Month
Use function: SUM
Add subtotal to: ColB
Click [OK]

The end result will look like this:

ColA	ColB	ColC		Month
Book	1	04/25/2007	Mth: 04
Paper	2	04/27/2007	Mth: 04
	3			Mth: 04 Total
Pen	1	05/05/2007	Mth: 05
Booka	2	05/15/2007	Mth: 05
Bookb	1	05/21/2007	Mth: 05
	4			Mth: 05 Total
Papera	1	08/02/2007	Mth: 08
Paperb	1	08/24/2007	Mth: 08
	2			Mth: 08 Total
	9			Grand Total

Is that something you can work with?

**cindy7** · 08-06-2007, 03:48 PM

Thanks Ron,
It doesn't seam like it work, but I would like to be able to using macro to program for this, because I have a lot the row and column also a lot of different files need to do the same way.
Please see attachment