How do I...?

[KrisPM]

I have set up 16 worksheets for individual tenants and have rolled all the
pertinent info into a summary worksheet to make a rent roll while keeping
individual ledger sheets on each individual. But, every mo. I need to make a
new summary worksheet taking the info from the next line on each individual's
worksheet. When I copy the summary sheet for say January to make February's
summary, i.e. for the formatting, how do I get the cell addresses to change
to pull from the next line on the individual worksheets? Is there a way to
do this globally?

 

[Carl Witthoft]

I have set up 16 worksheets for individual tenants and have rolled all the
pertinent info into a summary worksheet to make a rent roll while keeping
individual ledger sheets on each individual. But, every mo. I need to make a
new summary worksheet taking the info from the next line on each individual's
worksheet. When I copy the summary sheet for say January to make February's
summary, i.e. for the formatting, how do I get the cell addresses to change
to pull from the next line on the individual worksheets? Is there a way to
do this globally?

One more reason not to use Excel :-(

But it's not all that hard. Lets say your rollup sheet gets data from
teh other sheets with some formulas that look like

=tenantone!a35 (where 'tenantone' is the name of the worksheet)

and

=tenanttwo!b35 and so on.

Then doing a Replace '35' with '36' might do it for you.

A fancier way would be to have a reference cell at the top of your
rollup sheet. Let's say it's cell C1 . Type '35' (no quotes) in
that cell.

Use this formula in all the other cells:

=INDIRECT(CONCATENATE("tenantone!a",$c$1))

Now all you have to do is change the value of C1 and you're updated.

HTH

Carl

 

[Todd Aton]

It's great to hear you're using ledgers in a creative way like this. Here is
a suggestion from our expert function tester, Joe, that may help:

You can use INDEX and COUNTA together to get the last value in a given
column (A in this example) like this:

=INDEX(Sheet1!A:A,(COUNTA(Sheet1!A:A)))

COUNTA gives you the last cell in the range that has data, and INDEX
uses that to look up its value.

Substitute the ledger sheet name (on the tab at the bottom of the workbook
window) for "Sheet1" in the formula.

In the case of ledger sheets where row and column headers aren't visible,
the first column is A, the second is B, etc.

Todd Aton
Macintosh Business Unit
Microsoft Corporation

Microsoft makes no warranties, express, implied or statutory, as to the
information in this post.