convert column headings

[anderson2606]

Hi, everyone... I've got a problem here, and I'm not smart enough to
figure this stuff out...

I've got a worksheet that contains about 4000 entries, one per row,
labeled "TaxDeductibleAmounts". The first four columns in each row
contain "boilerplate" info, such as time, date, etc... Columns 5-40
contain various pieces of information (in this case, tax-deductible
amounts).

I placed the column labels/titles vertically in Column A of a separate
worksheet, labeled "Totals". A corresponding SUM formula in the cell
next to each column label/title ("=SUM('TaxDeductibleAmounts'!
E2:E65536)") reflects the totals of each tax-deductible category/
column from the "TaxDeductibleAmounts" worksheet.

I need a way to make the vertical list of column labels/titles in the
"Totals" worksheet dynamic, so that when I add a column in the
original ("TaxDeductibleAmounts") worksheet, the addition is reflected
in the list on the "Totals" worksheet. Currently, I'm clicking back
and forth between worksheets.

Any way to do this? (If my explanation is as clear as mud, send me an
email at anderson2606nospam at mailnospam.com. (Be sure to remove the
"nospam")

 

[Max]

Something like this, using INDIRECT & OFFSET, can provide the flexibility
that you're after
Assuming that A2 contains the sheetname: TaxDeductibleAmounts
you could place this in B2:
=SUM(OFFSET(INDIRECT("'"&$A2&"'!E:E"),,COLUMNS($A:A)-1))
to return the same as: =SUM(TaxDeductibleAmounts!E:E)
B2 can then be copied across to return correspondingly the sums of cols F,
G, H, etc from TaxDeductibleAmounts, and be filled down as well to return
the same kind of sums from the other sheetnames in A3, A4, A5, etc

 

[anderson2606]

Something like this, using INDIRECT & OFFSET, can provide the flexibility
that you're after
Assuming that A2 contains the sheetname: TaxDeductibleAmounts
you could place this in B2:
=SUM(OFFSET(INDIRECT("'"&$A2&"'!E:E"),,COLUMNS($A:A)-1))
to return the same as: =SUM(TaxDeductibleAmounts!E:E)
B2 can then be copied across to return correspondingly the sums of cols F,
G, H, etc from TaxDeductibleAmounts, and be filled down as well to return
the same kind of sums from the other sheetnames in A3, A4, A5, etc

My apologies, Max. I didn't explain everything quite right. (By way of
excuse, I once heard that I wasn't the brightest crayon in the box...)

No cell in either worksheet contains any worksheet or workbook name. I
failed to explain that Row 1 (in the "TaxDeductibleAmounts"
worksheet) contains column label headings stretching horizontally from
Column E to Column AN (36 labels).

I need to place these column labels (from the top horizontal row of
the "TaxDeductibleAmounts" worksheet) into the first vertical column
(Cell A1, A2, A3 ...) of a second worksheet (called the "Totals"
worksheet). The second column of the "Totals" worksheet (Cell B1, B2,
B3 ...) should display the result of adding all the amounts in a
specified column (from the "TaxDeductibleAmounts" worksheet).

This way, when my boss (the company accountant) needs a list of the
total tax-deductible expenses, I can simply print the "Totals"
worksheet for him.

If I understand your solution (and I'm not sure that I do), then your
solution assumes that I'm trying to put everything on ONE worksheet.
Actually, I've got TWO worksheets. (Then, of course, I might've missed
something very obvious, too. I might be trying to make a mountain out
of a molehill)

 

[Roger Govier]

Hi

You could insert a new row 1 on TaxDeductibleAmounts Sheet, and place the
formula in E1
= SUM(E3:E65536)
Copy across through columns F:AN

Then on your New Summary Sheet in cell A1 enter
=IF(INDEX(Sheet1!$1:$65536,2,ROW())=0,"",INDEX(Sheet1!$1:$65536,2,ROW()))
and in B1 enter
=IF(INDEX(Sheet1!$1:$65536,21ROW())=0,"",INDEX(Sheet1!$1:$65536,1,ROW()))

Copy both formulae down the sheet as far as required.

 

[Max]

Apparently, my reading of your original posting was off. That's all there is
to it. But with your clarification, Roger's response should deliver what you
seek. Cheers.

---