[Solved] Additional field information on subtotal lines

[snail]

I am looking to get additional field information appearing on subtotal lines in addition to the value from the 'Group By' field. I have a sheet with Invoice Number, Invoice Desc., Item Desc., PO num., PO Ref. etc. I want to generate subtotals by Invoice Number, but want to also see the values in the descriptive fields on the subtotal line when I compress the sheet to only show subtotals, also I want the subtotaled values to have the same formatting as the values they are totalling ie number with 2 decimal places.

In excel when I have applied the subtotals I can select the columns I want to show in the subtotal line, then select GOTO>Special>Blanks which highlights all blank fields in the respective columns (the newly created subtotal lines), I can fill all these fields with the a copy of the field immediately above them by entering "=; Up Arrow; Control Enter".

Is there either something I can do when creating the subtotal lines or subsequently as with excel?

Thanks in advance,
Snail

[TerryE]

You can't currently paste to a multiple selection as you've no doubt found out, so you need to resort to botches.

Lets say that you want to replicate col B into the blanks in rows 1:4123.

• First open a temporary sheet or workbook.
• Put =B1 in B2 and copy (Ctl-C).
• Type B3:B4123 in the range selector box and Paste (Ctl-V).
• Goto your invoice Sheet and likewise select B1:B4123 and copy.
• Go back to the temporary sheet; select B1 and paste special (Ctl-Shift-V). Uncheck Copy Blank Cells and hit enter.
• Now copy this entire column back into the invoice sheet.
• You can now trash the temporary sheet.

What you have done in this sequence is to replace all blank cells by ={above cell}. Tacky and a few extra steps, but with practice this is less than a minute.

[snail]

Terry,

Many thanks for that, it is a help, though as you point out, still clunky. I'm sure a couple of months ago when I last researched this, I came accross a different solution based, I think, on copying and pasting within the same column, possibly using Paste Special options, could just be my memory playing tricks though.

I'll go with what you have suggested unless someone comes up with a slicker alternative.

Thanks again,
Snail

[TerryE]

I would use a simple macro to do this. It's about dozen lines long. If you really want to get into Basic Macro programming then I'll give you a copy as a starter, but its not worth it unless you are into this sort of thing.