Worksheet management

I'm struggling with worksheet and workbook management.  I'm hopeful
that someobody has a solution that I can't seem to come up with.  I've
searched and can't find any mention of this particular issue.

I have a workbook with 10 worksheets in it.  I have 2 copies of this
workbook (one for each of my two clients ;-)).  Worksheets 2 through
10 are identical in both workbooks.  Worksheet 1 has the "data" for
the individual client.

The formulas in worksheets 2 through 10 reference the data on
worksheet 1, as well as varioious other cells on other worksheets.
That is, some of the worksheets will not only refer to worksheet 1,
but, for example, worksheet 5 might also pull some information from
worksheet 2.

So far, so good.

But let's say that I make a whole bunch of updates to worksheet 2 (one
of the worksheets that does "work", as opposed to worksheet 1 which is
the "data input" worksheet) in the first of my two workbooks.  I now
want to make those same updates to worksheet 2 of my second workbook.

Is there a "most efficient" way to do this?

I have tried copying worksheet 2 from workbook 1 into workbook 2 and
then deleting the old worksheet 2 from workbook2 and then renaming
"worksheet 2 (2)" back to "worksheet 2".  It doesn't work because it
leaves all of the formulas in worksheets 3 through 10 in tatters, to
the extent that those workbooks refer to "worksheet 2" as it is trying
to refer to a worksheet that was deleted, thereby giving me heaps of
"REF" errors.

The same kind of problem takes place if I reverse my solution and
attempt to make a new copy of workbook 1 (which would then be named
workbook 3) and then copy the data worksheet from workbook 2 into
workbook 3, deleting the old data worksheet and renaming the newly
copied in data worksheet.  This is actually worse, because every
single one of the "work" worksheets gets platered with REF errors.

I hope this explains my problem.

Thanks for your time.

Jim
0
anon7218 (27)
3/21/2007 10:17:48 PM
excel 39879 articles. 2 followers. Follow

2 Replies
643 Views

Similar Articles

[PageSpeed] 12

Jim,

One thing you might try after copying "Sheet2" to the new workbook and
deleting the original "Sheet2" there is to break the links between the
two workbooks, a common source of the REF error. To do this in Excel
2003, go to "Edit / Links" then hit the "Change Source" button and
choose the new (current) workbook.

/ Tyla /

On Mar 21, 3:17 pm, anon <a...@anon.invalid.net> wrote:
> I'm struggling with worksheet and workbook management.  I'm hopeful
> that someobody has a solution that I can't seem to come up with.  I've
> searched and can't find any mention of this particular issue.
>
> I have a workbook with 10 worksheets in it.  I have 2 copies of this
> workbook (one for each of my two clients ;-)).  Worksheets 2 through
> 10 are identical in both workbooks.  Worksheet 1 has the "data" for
> the individual client.
>
> The formulas in worksheets 2 through 10 reference the data on
> worksheet 1, as well as varioious other cells on other worksheets.
> That is, some of the worksheets will not only refer to worksheet 1,
> but, for example, worksheet 5 might also pull some information from
> worksheet 2.
>
> So far, so good.
>
> But let's say that I make a whole bunch of updates to worksheet 2 (one
> of the worksheets that does "work", as opposed to worksheet 1 which is
> the "data input" worksheet) in the first of my two workbooks.  I now
> want to make those same updates to worksheet 2 of my second workbook.
>
> Is there a "most efficient" way to do this?
>
> I have tried copying worksheet 2 from workbook 1 into workbook 2 and
> then deleting the old worksheet 2 from workbook2 and then renaming
> "worksheet 2 (2)" back to "worksheet 2".  It doesn't work because it
> leaves all of the formulas in worksheets 3 through 10 in tatters, to
> the extent that those workbooks refer to "worksheet 2" as it is trying
> to refer to a worksheet that was deleted, thereby giving me heaps of
> "REF" errors.
>
> The same kind of problem takes place if I reverse my solution and
> attempt to make a new copy of workbook 1 (which would then be named
> workbook 3) and then copy the data worksheet from workbook 2 into
> workbook 3, deleting the old data worksheet and renaming the newly
> copied in data worksheet.  This is actually worse, because every
> single one of the "work" worksheets gets platered with REF errors.
>
> I hope this explains my problem.
>
> Thanks for your time.
>
> Jim


0
tylagusim (7)
3/22/2007 2:58:08 AM
On 21 Mar 2007 19:58:08 -0700, "Tyla" <tylagusim@hotmail.com> wrote:

>Jim,
>
>One thing you might try after copying "Sheet2" to the new workbook and
>deleting the original "Sheet2" there is to break the links between the
>two workbooks, a common source of the REF error. To do this in Excel
>2003, go to "Edit / Links" then hit the "Change Source" button and
>choose the new (current) workbook.
>
>/ Tyla /

Tyla,

Thanks.  While it doesn't do exactly what I want, I can modify my
approach and it will do just fine.  If I split the DATA from the LOGIC
into separate workborks, then I can maintain a single LOGIC workbook
and replace the DATA workbook with the trick that you described.  Once
the DATA is segregated from the LOGIC, I can update the LOGIC workbook
at will (with appropriate versioning, etc.).

Many thanks.

Jim



>
>On Mar 21, 3:17 pm, anon <a...@anon.invalid.net> wrote:
>> I'm struggling with worksheet and workbook management.  I'm hopeful
>> that someobody has a solution that I can't seem to come up with.  I've
>> searched and can't find any mention of this particular issue.
>>
>> I have a workbook with 10 worksheets in it.  I have 2 copies of this
>> workbook (one for each of my two clients ;-)).  Worksheets 2 through
>> 10 are identical in both workbooks.  Worksheet 1 has the "data" for
>> the individual client.
>>
>> The formulas in worksheets 2 through 10 reference the data on
>> worksheet 1, as well as varioious other cells on other worksheets.
>> That is, some of the worksheets will not only refer to worksheet 1,
>> but, for example, worksheet 5 might also pull some information from
>> worksheet 2.
>>
>> So far, so good.
>>
>> But let's say that I make a whole bunch of updates to worksheet 2 (one
>> of the worksheets that does "work", as opposed to worksheet 1 which is
>> the "data input" worksheet) in the first of my two workbooks.  I now
>> want to make those same updates to worksheet 2 of my second workbook.
>>
>> Is there a "most efficient" way to do this?
>>
>> I have tried copying worksheet 2 from workbook 1 into workbook 2 and
>> then deleting the old worksheet 2 from workbook2 and then renaming
>> "worksheet 2 (2)" back to "worksheet 2".  It doesn't work because it
>> leaves all of the formulas in worksheets 3 through 10 in tatters, to
>> the extent that those workbooks refer to "worksheet 2" as it is trying
>> to refer to a worksheet that was deleted, thereby giving me heaps of
>> "REF" errors.
>>
>> The same kind of problem takes place if I reverse my solution and
>> attempt to make a new copy of workbook 1 (which would then be named
>> workbook 3) and then copy the data worksheet from workbook 2 into
>> workbook 3, deleting the old data worksheet and renaming the newly
>> copied in data worksheet.  This is actually worse, because every
>> single one of the "work" worksheets gets platered with REF errors.
>>
>> I hope this explains my problem.
>>
>> Thanks for your time.
>>
>> Jim
>

0
anon7218 (27)
3/22/2007 1:07:14 PM
Reply:

Similar Artilces:

print a worksheet out from another worksheet
Hi all, I have a validation list that shows all the worksheets in the workbook, I want to beable to select the a worksheet from the list and print it out without having to go to the sheet. Can this be done if so how? Cheers --------- Message sent via www.excelforums.com Hi With the list in A1 on Sheet1 for example you can use this macro Sub printtest() Dim str As String str = Sheets("Sheet1").Range("A1") Sheets(str).PrintOut End Sub -- Regards Ron de Bruin http://www.rondebruin.nl "rutima - ExcelForums.com" <a.caton@ntlworld-dot-com.no-spam.invalid&g...

Excel data in one cell, need to copy specific values to another worksheet in different cells
I am attempting to copy financial data from one worksheet to another. Unfortunately, the worksheet that I am copying from is formatted as one column of data in column A. The data appears as follows in column A: DISTRICT: Adams County/Ohio Valley Local COUNTY: Adams IRN: 061903 ADM, VALUATION & MILLAGE (1983-1984) SOURCE: SF12 Line 3 Basic ADM 4,916 Assessed value 480,468,317 Total ADM 5,769 Valuation/Basic ADM 97,736 Voted millage (Incl JVS) 20.80 Class1 eff tax rate 20.14 BASIC STATE AID (1983-...

filtration of worksheets tallying 2 million seperate phone numbers #2
Hi, I am a loan officer at a mortgage brokerage. We have recently put the new nationwide no-call list on an excel spreadsheet. I have 2 questions for you. First: is there a way to make excel have more rows than 65,536 (preferably like 2 million) as the colorado part of the national no-call list is about 2 million. And second;can you tell me how to filter one workshet against a second with 30 columns and 65,536 rows(the filter feature I use only takes into consideration the left-most column) ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com...

Sorting multiple worksheets simultaneously
Hi!! Is there any way to sort multiple worksheets in a workbook simultaneously (ie instead of sorting each sheet individually)? I have a file with 30+ sheets, each sheet set up identically (ie the same data type in the same column on each sheet), but (obviously) containing different data. There's a complication as well - each sheet has 50 rows, comprising links to other files. Not all of the rows actually contain relevant data (ie in some rows the result of the link is 'zero'), but the rows that contain data come before any rows that do not. For example, on sheet 1 (even th...

Merging worksheets #2
I would like to merge the data on two worksheets, can I and how do I do that? -- Ajtorrence Have it all on one sheet? or add it together on a 3rd sheet? Please provide more info. :) ************ Anne Troy www.OfficeArticles.com "Ann" <Ann@discussions.microsoft.com> wrote in message news:81C2F7CF-ED32-4A6C-BE36-1512B18FDA5D@microsoft.com... >I would like to merge the data on two worksheets, can I and how do I do >that? > -- > Ajtorrence ...

how can i get calendar wizard in excel worksheet
i want to now how i can a calendar design in excel worksheet without doing it my self Hi see: http://j-walk.com/ss/excel/files/calarray.exe -- Regards Frank Kabel Frankfurt, Germany "samuel" <samuel@discussions.microsoft.com> schrieb im Newsbeitrag news:BDB21156-C0C1-4E27-8C39-17CB38F26064@microsoft.com... > i want to now how i can a calendar design in excel worksheet without doing > it my self ...

Copy a Worksheet problem
Hi. I am trying to copy a worksheet by using CTRL and drag. When I click on the tab I am getting a 'stop' symbol (circle with a diagonal line through it). I have used this procedure many times before. What has changed? What can I do? Thanks, Dave Moore Perhaps you have protected the workbook under Tools>Protection Gord Dibben MS Excel MVP On Sat, 5 Jul 2008 08:31:20 -0700 (PDT), david.moore@uuplc.co.uk wrote: >Hi. >I am trying to copy a worksheet by using CTRL and drag. >When I click on the tab I am getting a 'stop' symbol (circle with a >diagonal line t...

Worksheet in a worksheet?
Is there any way to insert a worksheet in an Excel worksheet, like you can insert a Word document in an Excel worksheet? Regards Philip Have you tried Insert>Object>Create from File - enter the filename and check the Display as icon checkbox "Philip Reece-Heal" wrote: > Is there any way to insert a worksheet in an Excel worksheet, like you can > insert a Word document in an Excel worksheet? > > Regards > Philip > > > I don't think so. You can insert a workbook into a worksheet, though. Just like a .doc file (insert|object). Philip ...

Worksheet within a Worksheet
Does anyone know if there is a way to have a worksheet within a worksheet? I need to reference an extensive list of account numbers within a worksheet and I don't want the account numbers running all the way down the sheet. What do you mean by "reference"? What exactly are you trying to do? -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- <michaelburkett@gmail.com> wrote in message news:1120850412.61...

Conditional Formatting on Multiple Worksheets
I want to apply conditional formatting to the same cell on 12 worksheets within one workbook in Excel 2007. As soon as I select multiple worksheets, the conditional formatting option is greyed out. The workbook is not shared or protected and I used to be able to do this in Excel 2003. Please help! Hi, That is how it seems to work in 2007, this could be intentional or it could be a bug, I will file it as a bug report. Your solution: Format the first cell or range on one sheet. Click the Format Painter Click the tab for the first sheet you want formatted the same way, either Shift c...

pivot table: problem with multiple groupings in different worksheets
Hello. I'm having a bit of trouble the group function in a pivot table. Let's say we've got a table with columns "district" and sales (just an example), and I create two different pivot tables with the same origin. In one of the pivot tables I want to group regions in some way, and define different sets in the second pivot table. Here comes the issue: when I right click the selection...-> group, excel defines a new property on the origin data, a new field called "district2". That field also appears on the second pivot table. If I try to group differently the ...

how to merge data from numerous worksheets into one sheet.
Hi,I'm trying to merge data from numerous worksheets, with varying numbers of rows, but the same column headings, into the one worksheet. i've been cutting and pasting to get the desired results but it takes at least 15 minutes per workbook. Does anyone know if this is feasible? (Skill level?, I would say I'm no more than a casual user) Thanks in advance. What specifically is meant by 'merge data' To do complete rows, filter the book to be copied and Select, Copy and Paste as one selection, To do matching cell data, use VLookup for the columns required. Any further...

Protected Worksheet Tab Order
I have a protected worksheet in Excel. After I protected the sheet I selected a range of cells that users could edit. When the sheet is protected the tab order for the range of cells that the user could edit will not work, I can nagivate using the arrows but not by tabbing. Does anyone know how to set the tab order on a protected worksheet? You must select the range of editable cells BEFORE you protect the sheet. Format these cells to "unlocked" then protect the sheet. Tab order will be left to right then down, left to right. If you have some other Tab order you wi...

saving individual worksheet
When I save in Excel, how do I save individual worksheets separately? Even when I save Worksheet 3, when I open it, it shows up as Worksheet 1. Any clues what I'm doing wrong? Thanks for any help. I'm a newbie (shocker!). bob Bob, This is not typical, so how are you saving the individual worksheet? What steps do you take? -- HTH Bob Phillips "bb" <bb@earthlink.net> wrote in message news:Om%5b.110$Yt.17@newsread4.news.pas.earthlink.net... > When I save in Excel, how do I save individual worksheets separately? Even > when I save Worksheet 3, when I open ...

Integration manager out of stack space error
has anyone seen the below error? Beginning integration... DOC 668 ERROR: Out of stack space DOC 669 ERROR: Macro execution error, Dynamics.DEXTERITY_IM_MACRO_INACTIVE_HIT (error 2048): MoveTo field 'SOP Number' Thanks for any response Try the following: Error "Out of Stack Space" is generated if you run a SOP Return integration when the Item.Quantity value is not equal to the Item.ReturnedQuantities.Returned value. Hope this helps, rc "2020" wrote: > has anyone seen the below error? > > Beginning integration... > DOC 668 ERROR: Out of stack...

Is there a keystoke to navigate between worksheets in a workbook?
Ctrl-PageUp and Ctrl-PageDown -- HTH Bob Phillips "Mmarv" <Mmarv@discussions.microsoft.com> wrote in message news:F4193031-FE15-479E-ACD7-09AD409BCEA9@microsoft.com... > ...

Sorting one worksheet from another worksheet
I am wanting to sort data in a worksheet from a command button on a different worksheet. I want the focus to stay (or at least return) to the worksheet containing the command button. Is this even possible? If so, could someone supply a sample of code? Both worksheets are in the same workbook. Using excel 2003 . Yes. The trick is to not select the other sheet. Option Explicit Private Sub CommandButton1_Click() Dim wks As Worksheet Dim myRng As Range Set wks = Me.Parent.Worksheets("Sheet2") 'some other sheet With wks Set my...

Heps to design Locked/Unlocked cells in protected worksheet
Download it from http://www.addintools.com The Cell Lens Of Locked&Unlocked render and change the background color of cells according to their lock/unlock state: the locked cells to gray, the unlocked cells to blue. Assist has also offered a Quick Lock button to lock the selected range at once and a Quick Unlock button to unlock immediately. Now you are very clear about which cells are locked and which cells are unlocked. Just cancel this Cell Lens, the background color will be restored. Cell Lens Of Data Type render and change the background color of cells according to their data ...

Linked Table Manager
I am using the following code to launch the Linked Table Manager in Access 2007: DoCmd.RunCommand acCmdLinkedTableManager My question is: Is there a simple way to tell if the linking was Successful, Failed, or if the user Canceled the process? Thanks in advance Mark -- Message posted via http://www.accessmonster.com Your code simply open the Linked Table Manager console. It will return messages based on the process. It will tell you if it was successful or not. The only other option would be to create your own table linking form/routine. -- Hope this helps, D...

Embedding worksheet into Word
Has anyone had the experience where when they embed a worksheet into Microsoft Word, a portion of the worksheet gets cut off? Is there an easy fix to this? ...

Working with a worksheet that is not the active worksheet
How can I make this code work on my worksheet named February, when it is not the active worksheet? Sub BlankWeeks() ' ' Macro2 Macro ' ' If Range("C184").Value = "" Then Rows("184:228").Hidden = True End If If Range("C184").Value <> "" Then Rows("184:228").Hidden = False End If If Range("C229").Value = "" Then Rows("229:273").Hidden = True End If If Range("C229").Value <> "" The...

Protected worksheet, shared workbook problems
I'm in quite the fix. I'm using XL2000 and 1. I have a shared workbook 2. ...that I want certain data protected 3. ..but I also want users to be able to insert rows. 2 and 3 together are not a problem because thanks to someone's suggestion, I insert rows via a macro where the macro first unprotects the data, then reprotects the correct data. 1, 2, and 3 is a problem since when a workbook is shared, you can't unprotect data and thus I can't do 2 and 3 if the workbook is shared. Is there anyway I can use my insert rows macro without having to unshare the workbo...

Unhide worksheet?
Other than fm Format > Sheet > Unhide to reveal hidden worksheets, wat other methods is used to hide a worksheet? Hi Macro code: Sub ShowSummary() Sheets("Summary").Visible = True End Sub -- HTH. Best wishes Harald Followup to newsgroup only please "crapit" <littlecramp@yahoo.com> skrev i melding news:ORkzl0MuDHA.3496@TK2MSFTNGP11.phx.gbl... > Other than fm Format > Sheet > Unhide to reveal hidden worksheets, wat other > methods is used to hide a worksheet? > > Hi You can set the worksheet's Visible property to xlSheetVeryHidden ...

Copying Conditional Formating to another worksheet
I am using conditional formating to designate a player in a league and their opponent. Since there are several leagues, I wanted to be able to copy the conditional formating from one sheet to another. When I copy the worksheet, I go to the new sheet, right click on the upper left worksheet button, selecting the entire sheet, select paste special, then select All. Everything works perfectly, accept the conditional formats. It appears to add more conditional formats from the copy / paste procedure. I have selected the new worksheet, selected conditional formating, then cleared ex...

Integration Manager only running for some integrations
Setup is GP 10/IM 10 running on Citrix for the clients. I'm the IT person, running GP on my laptop. For some reason, only one of the integrations is not working - the one that updates our chart of accounts. All the other integrations work fine - SOP entry and cash receipts entry. They use IM on a daily basis, mostly for the SOP and cash receipts; the COA is used only rarely. When the client chooses the COA integration, then Run, nothing happens. I tried running the integration myself, and got a "you don't have permissions to these tables" error. So, wha...