Linking Cells from different worksheets

[Guest]

Thank you in advance to anyone who attempts to answer this post. I have a
total of 6 excel spreadsheets that I would like to link to each other. 5 of
them could be considered 'child' sheets and one 'parent' or master sheet. On
the 5 child sheets, each will contain text and numbers and a sum at the end.
On the parent sheet I would like to have 6 tabs. One tab for each child
sheet that is a constantly updating copy of changes made in child sheet.
After those 5 tabs I would like to have a 6th tab that is a grand total of
the previous 5 tabs. It should contain copies of all the other 5 tabs, one
on top of the other, and then a sum total at the end.

I would then like to have a copy of that 'total tab' on 2nd tab on each of
the child spread sheets. Is something like this possible?

 

[Dave Peterson]

If you try something like this, I think you're setting yourself up for failure.
Way too many things can go wrong.

I would take a different approach.

Put all your data in one worksheet (the parent worksheet). Do all your data
entry there. If you need to view different "slices" of your data, maybe you can
use data|filter|autofilter to view the stuff you want to see.

If you absolutely must have multiple sheets, then I would still keep the data in
one worksheet (and update it there), but use a macro to split the parent's data
into each of the child worksheets.

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks for the insight Dave. The reason I wanted 5 different 'child'
spreadsheets is because we have 5 different people that would be updating
these sheets through out the day. Unless I am mistaken, if one person has
the master sheet open then the other people can not make changes?

Is this something Excel can handle?

 

[Dave Peterson]

If you want simultaneous updates, then maybe excel is not the right application
for you.

You could use a shared workbook, but lots of knowledgeable people stay away from
them because of all the limitations (and possible corruption that comes with
it).

If you wanted, you could keep 5 different workbooks/worksheets and then create a
new worksheet based on those.

Essentially, you'd open each and copy|paste to a new workbook each time you
wanted to get that summary file.

Ron de Bruin shares lots of sampe code here:
http://www.rondebruin.nl/tips.htm

(Maybe using a real database program (like Access) would be better????)

 

[Guest]

I agree completely. Thank you Dave

If you want simultaneous updates, then maybe excel is not the right application
for you.

You could use a shared workbook, but lots of knowledgeable people stay away from
them because of all the limitations (and possible corruption that comes with
it).

If you wanted, you could keep 5 different workbooks/worksheets and then create a
new worksheet based on those.

Essentially, you'd open each and copy|paste to a new workbook each time you
wanted to get that summary file.

Ron de Bruin shares lots of sampe code here:
http://www.rondebruin.nl/tips.htm

(Maybe using a real database program (like Access) would be better????)

 

[Guest]

Hi Dave,

I took your advice and decided to create on master sheet. I tried using one
of the sample spreadsheets from Debra Dalgleish's site. It's the "Update
Sheets From Master" sample. I used this macro in my spreadsheets and made a
few minor adjustments to make it match my worsheets and it works very nicely.
I really appreciate your help.

The only problem I am running into is that her sample only copies the first
13 Columns (A-N). Could you tell me how I could change the code to copy all
coulmns on to the other weeksheets? Also, it seems to not want to copy cells
that have dates, for example 5/10/2007. Could it be the format or is there
something about the third column?

This is all very interesting stuff. I never knew excel had so much
functionality when paried with VB. Thanks again for your assistance!

 

[Dave Peterson]

#1. My first guess is that when you defined the range DataBase, you didn't
include all the columns you needed. If you really want all 256 (16384???)
columns copied, then include all those columns in the range name.

#2. I just ran Debra's code and she has dates in it that were copied ok. I
don't have a guess why you're not getting those rows with dates copied.