Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old April 12th 06, 10:27 AM posted to microsoft.public.excel.worksheet.functions
Ellymoo
 
Posts: n/a
Default Linking sheets into one major sheet?

I have a large workbook, split into sheets, with very complex formulas in it.
I want one sheet at the end of this workbook to reflect all the data from the
other sheets, if you know what I mean - so it is ALL the data from the
different sheets, in one "all info" sheet. I also want it to update
automatically so I don't have to re-enter lots of data when updating the
separate sheets. Is there any way of doing this? I have looked at some of the
answers on here but I got very lost. Thank you

  #2   Report Post  
Old April 12th 06, 02:18 PM posted to microsoft.public.excel.worksheet.functions
John James
 
Posts: n/a
Default Linking sheets into one major sheet?


Hi Ellymoo,

Seems a bit back the front to me.

You don't link data from multiple sheets into one datasheet. That's a
waste of time. You initially collect all of your data into one
datasheet or multiple datasheets and then analyse & report from that
source.

All data which shares common fields is collected into one datasheet,
with headings for each column (field). That is one table. You may
have multiple tables in which case you may want multiple datasheets.

When you have all of your data in structured datasheets like this,
reporting is greatly simplified. Excel has a multitude of tools for
analysing and reporting data when the data is stored in this datasheet
format.

I suspect a lot of your complex formulae may disappear with sound
structuring and reporting methods.

You should never - never - have to re-enter data on separate sheets.
That is bad practice.

So the short answer is to initially collect your data in the datasheet
rather than trying to link or move it there later.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195

  #3   Report Post  
Old April 12th 06, 02:49 PM posted to microsoft.public.excel.worksheet.functions
Ellymoo
 
Posts: n/a
Default Linking sheets into one major sheet?

Thanks very much for your reply.

I work for a company with various bases. This workbook is their training
record, so they hop on and look at the sheet showing their base only, and
none of the others, hence it is separate. However, assuming that the best
way to go about it is work from the main sheet and allow the separate sheets
to update automatically when I enter data into the main sheet, how could I
'rejig' the whole workbook? Are there formulae I should use?

"John James" wrote:


Hi Ellymoo,

Seems a bit back the front to me.

You don't link data from multiple sheets into one datasheet. That's a
waste of time. You initially collect all of your data into one
datasheet or multiple datasheets and then analyse & report from that
source.

All data which shares common fields is collected into one datasheet,
with headings for each column (field). That is one table. You may
have multiple tables in which case you may want multiple datasheets.

When you have all of your data in structured datasheets like this,
reporting is greatly simplified. Excel has a multitude of tools for
analysing and reporting data when the data is stored in this datasheet
format.

I suspect a lot of your complex formulae may disappear with sound
structuring and reporting methods.

You should never - never - have to re-enter data on separate sheets.
That is bad practice.

So the short answer is to initially collect your data in the datasheet
rather than trying to link or move it there later.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195


  #4   Report Post  
Old April 12th 06, 04:20 PM posted to microsoft.public.excel.worksheet.functions
Ellymoo
 
Posts: n/a
Default Linking sheets into one major sheet?

Thank you for that advice, I will look into it

"Duke Carey" wrote:

You may want to look into pivot tables. When structured properly you can use
the 'base' as a page field (essentially a filter) so that the users can
choose their base from a list and the pivot table updates automatically.

See Debra Dallgliesh's site for pivot table guidance

http://www.contextures.com/tiptech.html



"Ellymoo" wrote:

Thanks very much for your reply.

I work for a company with various bases. This workbook is their training
record, so they hop on and look at the sheet showing their base only, and
none of the others, hence it is separate. However, assuming that the best
way to go about it is work from the main sheet and allow the separate sheets
to update automatically when I enter data into the main sheet, how could I
'rejig' the whole workbook? Are there formulae I should use?

"John James" wrote:


Hi Ellymoo,

Seems a bit back the front to me.

You don't link data from multiple sheets into one datasheet. That's a
waste of time. You initially collect all of your data into one
datasheet or multiple datasheets and then analyse & report from that
source.

All data which shares common fields is collected into one datasheet,
with headings for each column (field). That is one table. You may
have multiple tables in which case you may want multiple datasheets.

When you have all of your data in structured datasheets like this,
reporting is greatly simplified. Excel has a multitude of tools for
analysing and reporting data when the data is stored in this datasheet
format.

I suspect a lot of your complex formulae may disappear with sound
structuring and reporting methods.

You should never - never - have to re-enter data on separate sheets.
That is bad practice.

So the short answer is to initially collect your data in the datasheet
rather than trying to link or move it there later.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195


  #5   Report Post  
Old April 12th 06, 04:47 PM posted to microsoft.public.excel.worksheet.functions
Duke Carey
 
Posts: n/a
Default Linking sheets into one major sheet?

You may want to look into pivot tables. When structured properly you can use
the 'base' as a page field (essentially a filter) so that the users can
choose their base from a list and the pivot table updates automatically.

See Debra Dallgliesh's site for pivot table guidance

http://www.contextures.com/tiptech.html



"Ellymoo" wrote:

Thanks very much for your reply.

I work for a company with various bases. This workbook is their training
record, so they hop on and look at the sheet showing their base only, and
none of the others, hence it is separate. However, assuming that the best
way to go about it is work from the main sheet and allow the separate sheets
to update automatically when I enter data into the main sheet, how could I
'rejig' the whole workbook? Are there formulae I should use?

"John James" wrote:


Hi Ellymoo,

Seems a bit back the front to me.

You don't link data from multiple sheets into one datasheet. That's a
waste of time. You initially collect all of your data into one
datasheet or multiple datasheets and then analyse & report from that
source.

All data which shares common fields is collected into one datasheet,
with headings for each column (field). That is one table. You may
have multiple tables in which case you may want multiple datasheets.

When you have all of your data in structured datasheets like this,
reporting is greatly simplified. Excel has a multitude of tools for
analysing and reporting data when the data is stored in this datasheet
format.

I suspect a lot of your complex formulae may disappear with sound
structuring and reporting methods.

You should never - never - have to re-enter data on separate sheets.
That is bad practice.

So the short answer is to initially collect your data in the datasheet
rather than trying to link or move it there later.


--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195




  #6   Report Post  
Old April 13th 06, 11:53 AM posted to microsoft.public.excel.worksheet.functions
John James
 
Posts: n/a
Default Linking sheets into one major sheet?


Hi Ellymoo,

I fully support Duke's suggestion. This is a great way to go - and
involves essentially no maintenance apart from refreshing the
pivottable when the source data changes (one mouse click). There are
other methods but this is the simplest.

Cheers,


Ellymoo Wrote:[color=blue]
Thank you for that advice, I will look into it

"Duke Carey" wrote:

You may want to look into pivot tables. When structured properly you

can use
the 'base' as a page field (essentially a filter) so that the users

can
choose their base from a list and the pivot table updates

automatically.

See Debra Dallgliesh's site for pivot table guidance

http://www.contextures.com/tiptech.html




--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195

  #7   Report Post  
Old April 13th 06, 02:21 PM posted to microsoft.public.excel.worksheet.functions
Ellymoo
 
Posts: n/a
Default Linking sheets into one major sheet?

can this be done in Excel with data currently inputted? I had a look at the
website and was no much the wiser.
  #8   Report Post  
Old April 14th 06, 04:03 AM posted to microsoft.public.excel.worksheet.functions
John James
 
Posts: n/a
Default Linking sheets into one major sheet?


Absolutely. Here's an introduction to pivottables. They take a bit of
getting used to but it's well worth the effort!

http://www.cpearson.com/excel/pivots.htm

Ellymoo Wrote:
can this be done in Excel with data currently inputted? I had a look at
the
website and was no much the wiser.



--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195

  #9   Report Post  
Old April 24th 06, 11:22 AM posted to microsoft.public.excel.worksheet.functions
Ellymoo
 
Posts: n/a
Default Linking sheets into one major sheet?

Thanks very much! I'll take a look at that.

"John James" wrote:


Absolutely. Here's an introduction to pivottables. They take a bit of
getting used to but it's well worth the effort!

http://www.cpearson.com/excel/pivots.htm

Ellymoo Wrote:
can this be done in Excel with data currently inputted? I had a look at
the
website and was no much the wiser.



--
John James
------------------------------------------------------------------------
John James's Profile: http://www.excelforum.com/member.php...o&userid=32690
View this thread: http://www.excelforum.com/showthread...hreadid=532195




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Linking formulas in new sheets based on a template? Olle Svensson Excel Worksheet Functions 1 February 28th 06 09:43 AM
Linking References from Multiple Sheets to One Summary Sheet Kim Setting up and Configuration of Excel 3 May 5th 05 04:56 PM
Linking 2 Excel Sheets, Anticipating Lines. Matthew Excel Worksheet Functions 1 December 17th 04 02:55 PM
Linking sheets to a summary sheet in workbook gambinijr Excel Discussion (Misc queries) 4 December 16th 04 09:13 PM
linking multiple sheets to a summary sheet greg g Excel Discussion (Misc queries) 1 December 16th 04 08:43 AM


All times are GMT +1. The time now is 10:42 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017