2004: moving rows below other data without changing calculationsin unmoved rows

[Norman R. Nager, Ph.D.]

BACKGROUND: In a spreadsheet of investments with columns representing
months, the first of three sections of rows contains the number of shares
for each stock, the second section contains the value of each share on the
1st of each month, the third section multiplies the number of shares. At
the bottom of the third section is the total value of stocks on the first of
each month.

Question: How can one move the three rows for each no-longer-held stock to
the very bottom of the spreadsheet without affecting the historical data in
the row that contains total portfolio values for each month?

Respectfully, Norm

 

[JE McGimpsey]

BACKGROUND: In a spreadsheet of investments with columns representing
months, the first of three sections of rows contains the number of shares
for each stock, the second section contains the value of each share on the
1st of each month, the third section multiplies the number of shares. At
the bottom of the third section is the total value of stocks on the first of
each month.

Question: How can one move the three rows for each no-longer-held stock to
the very bottom of the spreadsheet without affecting the historical data in
the row that contains total portfolio values for each month?

Norm -

You could do this with Dynamic Ranges:

http://cpearson.com/excel/excelF.htm#DynamicRanges

You could also do this using a macro.

However, I'd think the easiest way would be to move your total portfolio
value calculations to the top of the sheet, and sum all the cells below
- then order doesn't matter.

I don't have a good enough idea of your data layout to venture a macro.
Post back with more specifics if you want to pursue that option.