A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Automatically update spreadsheet from another spreadsheet



 
 
Thread Tools Display Modes
  #1  
Old January 9th 08, 06:21 PM posted to microsoft.public.excel.worksheet.functions
CINDY
external usenet poster
 
Posts: 130
Default Automatically update spreadsheet from another spreadsheet

I have 8 sheets on one spreadsheet and I need to create individual
spreadsheets for each of the 8 sheets. The problem is I need the information
I put into the spreadsheet with all 8 sheets attached to update on the
individual spreadsheets.

How can I do this? I feel like I’m just missing a step in the normal
formulas that I use.


Ads
  #2  
Old January 9th 08, 06:48 PM posted to microsoft.public.excel.worksheet.functions
ryguy7272
external usenet poster
 
Posts: 2,836
Default Automatically update spreadsheet from another spreadsheet

I'm not sure what 8 sheets on one spreadsheet means, but the macro below will
parse all data into rows of 10s (change to suit your needs) and create new
sheets for the results of each parse:

Sub PaserIntoHundreds()
Set sh = ActiveSheet
For i = 1 To 991 Step 10
Set rng = sh.Cells(i, 1).Resize(10, 1).EntireRow
Set sh1 = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
rng.Copy sh1.Range("A1")
' optional
'sh1.move
Next
End Sub


I may be way off target here. Post back if you need something different.


Regards,
Ryan--

--
RyGuy


"Cindy" wrote:

> I have 8 sheets on one spreadsheet and I need to create individual
> spreadsheets for each of the 8 sheets. The problem is I need the information
> I put into the spreadsheet with all 8 sheets attached to update on the
> individual spreadsheets.
>
> How can I do this? I feel like I’m just missing a step in the normal
> formulas that I use.
>
>

  #3  
Old January 9th 08, 06:56 PM posted to microsoft.public.excel.worksheet.functions
Bernard Liengme
external usenet poster
 
Posts: 4,393
Default Automatically update spreadsheet from another spreadsheet

Assume the 8-sheet book is called My Book.XLS
In cell A1 of sheet1 of the first new workbook, enter
=IF('[My Book.xls]Sheet1'!A1="","",'[My Book.xls]Sheet1'!A1)
There are two sets of double quotes in the formula: double-quote
double-quote comma double-quote double-quote
Copy this to the right as far as needed
Now copy row 1 down as far as needed.
Every time you open the new workbook it will be updated to show what is in
My Book

Note that if you add new data to My Book beyond the columns/row for formulas
in the other books, the new stuff will not appear. It is generally
considered poor practice to store the same data in two places (other than
for backup, of course) There may be a better way if we knew the purpose of
this exercise.
best wishes

--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Cindy" > wrote in message
...
>I have 8 sheets on one spreadsheet and I need to create individual
> spreadsheets for each of the 8 sheets. The problem is I need the
> information
> I put into the spreadsheet with all 8 sheets attached to update on the
> individual spreadsheets.
>
> How can I do this? I feel like I'm just missing a step in the normal
> formulas that I use.
>
>



  #4  
Old January 9th 08, 07:05 PM posted to microsoft.public.excel.worksheet.functions
CINDY
external usenet poster
 
Posts: 130
Default Automatically update spreadsheet from another spreadsheet

Sorry I wasn't clear - it's hard to explain in writing (or for me).
I have an 8 worksheets in 1 excel workbook. I would like to have 8 separate
workbooks but have them update automatically when I enter information into
the workbook with all 8 worksheets.

I figured out how to update by using Edit / Paste Link.

My problem now is that any cell that wasn't filled in from the main document
- shows up with zeros and now I am trying to figure out how to update the
separate workbooks without the zeros.

If NONE of this makes sense, I do apologize. I'm more of a talker than
typer.

"ryguy7272" wrote:

> I'm not sure what 8 sheets on one spreadsheet means, but the macro below will
> parse all data into rows of 10s (change to suit your needs) and create new
> sheets for the results of each parse:
>
> Sub PaserIntoHundreds()
> Set sh = ActiveSheet
> For i = 1 To 991 Step 10
> Set rng = sh.Cells(i, 1).Resize(10, 1).EntireRow
> Set sh1 = Worksheets.Add(after:=Worksheets(Worksheets.Count) )
> rng.Copy sh1.Range("A1")
> ' optional
> 'sh1.move
> Next
> End Sub
>
>
> I may be way off target here. Post back if you need something different.
>
>
> Regards,
> Ryan--
>
> --
> RyGuy
>
>
> "Cindy" wrote:
>
> > I have 8 sheets on one spreadsheet and I need to create individual
> > spreadsheets for each of the 8 sheets. The problem is I need the information
> > I put into the spreadsheet with all 8 sheets attached to update on the
> > individual spreadsheets.
> >
> > How can I do this? I feel like I’m just missing a step in the normal
> > formulas that I use.
> >
> >

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
automatically update a link without opening the other spreadsheet LaKaroT Excel Discussion (Misc queries) 2 May 8th 07 01:49 AM
Have date automatically update each time I open the spreadsheet Beth1415 Excel Discussion (Misc queries) 3 November 6th 06 10:14 PM
How do I set up a spreadsheet where it will automatically update? Rhoda Excel Worksheet Functions 1 May 16th 06 08:11 PM
Update one spreadsheet and reflect on another Acctgbean Excel Worksheet Functions 0 November 3rd 05 09:36 PM
Is there a way to insert a formula, password or macro in an excel spreadsheet that will automatically delete the spreadsheet? oil_driller Excel Discussion (Misc queries) 1 February 8th 05 10:34 AM


All times are GMT +1. The time now is 12:47 AM.


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