Jump to content

DRAGONMOUNT

A WHEEL OF TIME COMMUNITY

Are there any Excel experts around?


Elgee
 Share

Recommended Posts

I'm trying to create a big file where all my tenant details are in, from which I'd then like to create automatically updating statements.

 

Is such a thing possible? If need be, it can be the other way round too, though the above way would be preferable.

Link to comment
Share on other sites

It should definitely be possible, when using the right formulas. You can even set a single cell to receive data from a completely different spreadsheet, as long as it is stored somewhere on the same pc. 

 

I'm no expert, and without seeing the actual data set, I can't help all that much either. If you google the specific terms you need, you should be able to find what is required. Good luck!

Link to comment
Share on other sites

It should definitely be possible, when using the right formulas. You can even set a single cell to receive data from a completely different spreadsheet, as long as it is stored somewhere on the same pc. 

 

 

That's exactly what I want to do - have a large database in one spreadsheet, then have different spreadsheets for each tenant and owner, with just some of the data appearing in there.

 

I'm no expert, and without seeing the actual data set, I can't help all that much either. If you google the specific terms you need, you should be able to find what is required. Good luck!

 

I've googled for hours, but not found anything exactly like that.

 

Yeah, without seeing the actual dataset, it would be hard to determine.

I work a lot with Excel, so if you want to PM me with details, you can :)

 

Thank you, Pralaya! I'll definitely PM you just as soon as I've done my GL report :)

Link to comment
Share on other sites

 

It should definitely be possible, when using the right formulas. You can even set a single cell to receive data from a completely different spreadsheet, as long as it is stored somewhere on the same pc. 

 

 

That's exactly what I want to do - have a large database in one spreadsheet, then have different spreadsheets for each tenant and owner, with just some of the data appearing in there.

 

 

 

Ah. There are two ways to do that (that I use, so there might be different ways):

 

* The STS must be saved somewhere on the pc.

* Every time you update the STS, it will automatically update in the LD too.

 

1) Simply copy the cell (from the single tenant spreadsheet [sTS]) that you want in the large database [LD]. 

    When you right click to paste this, the last of the paste options is Paste Link - it has a blue little (almost an infinity) sign.

 

2) Another way is to manually type the link into the cell (the link that is provided by Paste Link).

 

Example: Let's say that the STS file is named "Tenant Apple and Tea 265.xlsx" (that is the name you saved it as on your pc).

You will have to check the STS file to see on which Sheet you have placed the data you want in the LD (that is the little tabs at the bottom of the screen). For now, we'll assume the Sheet is named "January".

You will additionally have to check in which cell the data is, for example, cell P26.

 

You will now go to the cell in the LD that you want the data from the STS to be in. Click on the cell, and type:

   ='[Tenant Apple and Tea 265.xlsx]January'!P26

 

And then the cell will be filled with the data from the STS named "Tenant Apple and Tea 265", on Sheet January, in cell P26.

 

The manual method is easy when the data you want in the LD is always in the same place in every STS.

For example, copying that formula into the cell below it, but then changing "January" to "February" (assuming that Sheet 2 is named February) will fill the cell with the value that is present in cell P26, on the second sheet of the "Tenant Apple and Tea 265" file - thus providing data for 2 months. 

Another example is to copy the formula into a different cell but changing the file name, and it will update the cells in the LD accordingly - it will provide the value of the cell that is present in that file, on the sheet and cell you specified.

Edited by Guest
Link to comment
Share on other sites

I don't have Access, unfortunately, and the boss won't buy me a proper, new version of Office.

 

Blommie, you're confussing me! lol

 

I'm pretty sure I don't have any STD's :P

 

Could you possibly translate the above for me into my terms? My files would be as follows:

 

A) Main file, containing the information pertaining to all tenants and owners

 

containing as an example, the following columns (these would be column headings):

Invoice Date

Date Paid

Property

Tenant Name

Owner Name

For

Month

Owed

Paid

Balance

 

 

B) child files, namely

B 1) Tenant Statements

B 2) Owner Statements

 

containing only some of those columns, and specific to those tenants and owners

Edited by Elgee
Link to comment
Share on other sites

Yeah, if the Tenant and Owner statement are continuously increasing, Blom's method may not necessarily work. As you would need to update the Main file every time the tenant and owner files have additional rows.

 

Elgee, if you can send me sample sheets, I can whip up something quick.

Link to comment
Share on other sites

 Share

×
×
  • Create New...