How to link workbooks in Excel? Is it possible? 2

[JohnnyT]

Hi

I'm making an excel spreadsheet to help me fill in my quarterly tax return. I've designed a sheet that will cover one quarter (August through to October). I have several sheets in the workbook, AUG_OUT, AUG_IN, SEP_OUT, SEP_IN, OCT_OUT, OCT_IN, SUMMARY, VAT.

I'd like to expand the sheet to cover the whole year but think it would get too messy with too many sheets in the one workbook.

I wondered if it was possible to have a workbook that had four links on it that link to workbooks Nov-Jan, Feb-Apr, May-Jul and Aug-Oct?

Is there a better way of doing this?

I'm quite new to excel so I've been using this project to learn about the software but, yet again, I'm a bit stuck on the best way to proceed. Has anyone got any suggestions?

Many thanks

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[xlbo]

In the 1st instance, it is better to have ALL data on ONE sheet so AUG_OUT, AUG_IN, SEPT_OUT, SEPT_IN would be on 1 sheet

You would have a column that indicates the month and a column that indicates OUT or IN and a column that indicates the quarter. Against this you can hold all your transactions and then reporting becomes very easy

Sample layout:

Qtr | MONTH | IN/OUT | Amount


Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JohnnyT]

Hi Geoff

Thanks for the info but there is actually a lot of information within each sheet. AUG IN is a full page of info that covers all transactions 'IN' for the month of August. The same applies to AUG OUT and the other months etc.

It would help me to keep the info together as a quarter but I just wondered if there was a way to link all the quarters together somehow to have one central place to get access to all the info for that year.

Cheers

JT ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[xlbo]

but there is actually a lot of information within each sheet

More than 65000 lines ?

I am telling you how to get your data to link better. Have it all in 1 sheet. Unless you have a total of more than 65536 records in the year, there is no need to store the data on seperate sheets.

The way you are storing data currently makes it very hard to tie up the information for a year. Probably do-able but it is much MUCH easier to just store data in ONE place with appropriate tags for reporting. If the data is in one place, you can EASILY report on individual months, quarters or whatever else you want. Chopping data up into seperate sheets for storage is a cardinal sin IMO

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JohnnyT]

Thanks Geoff

I'd just be a bit worried that it would make data entry more prone to error if I'm scrolling through several very similar sheets in order to enter values.

I'll maybe give it a go though and just see how it looks.

Many thanks for your help

Cheers

JT ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[xlbo]

The whole point is that there are no similar sheets - just one sheet for all data entry.

This can create different sheets for reporting but for the actual data entry you really are best off keeping it on 1 sheet

As an example, if you use the layout I suggested earlier, you can create a monthly / quarterly summary using a pivot table in about 30 seconds flat...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[JohnnyT]

Okay.. I'll give it a bash.

Many thanks

John ;-)

I don't make mistakes, I'm merely beta-testing life.

 

[fneily]

To link across sheets or workbooks, click on the cell where the answer would go. Then type an equal(=) sign. Then click on the next sheet, click on the cell to add then in the formula bar type a plus(+) sign. Then click on the next sheet, etc. When you clicked on the last piece of data, hit the enter key. You'll see the answer. If you click back on the answer, you'll see the whole formula you created in the formula bar.

 

[JohnnyT]

Thanks fneily, much appreciated.

;-)

I don't make mistakes, I'm merely beta-testing life.