Copy Formula with Reference Change

I have a workbook that will have 100+ sheets in it.  I 
want to use a beginning number in a cell on the first 
sheet and then on the following sheets in the same cell 
have a formula that adds 1 to the number in the same cell 
on the previous sheet.  For example, the first sheet has 
497 in cell B2.  In each successive sheet, I would have a 
formula in cell B2 that would be '=sheet1!B2+1' to give me 
498, then 499, then 500, etc.  I want to copy the formula 
to each successive sheet and have it automatically adjust 
the sheet it is referencing to be the previous sheet.  
However, when I copy the formula, it does not change the 
sheet reference from the original sheet.  I do not want to 
have to manually change 100+ formulas.  This used to work 
in Lotus.  Is there any easy way to do this?

Also, is there any way to automatically change the sheet 
tab name for the same reason?  When inserting sheets in 
Lotus, it used to insert the names based on the previous 
sheet.  That way I had a sheet name of 497 and the same in 
cell B2.

I have this problem at the beginning of every year.

Thank you for any help,
Jennifer

0
anonymous (74722)
1/6/2004 1:15:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
298 Views

Similar Articles

[PageSpeed] 47

Jennifer,

Not a great solution, but add these two UDFs to your workbook.

Function prevSheet(rng As Range)
    With ActiveSheet
        If .Index <> 1 Then
            prevSheet = Worksheets(.Index - 1).Range(rng.Address).Value
        Else
            prevSheet = CVErr(xlErrRef)
        End If
    End With
End Function

Function nextSheet(rng As Range)
    With ActiveSheet
        If .Index <> Worksheets.Count Then
            nextSheet = Worksheets(.Index + 1).Range(rng.Address).Value
        Else
            nextSheet = CVErr(xlErrRef)
        End If
    End With
End Function


Then this formula does what you want

=prevsheet(A1)+1


Your worksheets need to be in the correct order to look right.

> I have this problem at the beginning of every year.

Why not create a template?

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Jennifer" <anonymous@discussions.microsoft.com> wrote in message
news:093801c3d457$17198ea0$a401280a@phx.gbl...
> I have a workbook that will have 100+ sheets in it.  I
> want to use a beginning number in a cell on the first
> sheet and then on the following sheets in the same cell
> have a formula that adds 1 to the number in the same cell
> on the previous sheet.  For example, the first sheet has
> 497 in cell B2.  In each successive sheet, I would have a
> formula in cell B2 that would be '=sheet1!B2+1' to give me
> 498, then 499, then 500, etc.  I want to copy the formula
> to each successive sheet and have it automatically adjust
> the sheet it is referencing to be the previous sheet.
> However, when I copy the formula, it does not change the
> sheet reference from the original sheet.  I do not want to
> have to manually change 100+ formulas.  This used to work
> in Lotus.  Is there any easy way to do this?
>
> Also, is there any way to automatically change the sheet
> tab name for the same reason?  When inserting sheets in
> Lotus, it used to insert the names based on the previous
> sheet.  That way I had a sheet name of 497 and the same in
> cell B2.
>
> I have this problem at the beginning of every year.
>
> Thank you for any help,
> Jennifer
>


0
bob.phillips1 (6510)
1/6/2004 1:47:41 PM
Reply:

Similar Artilces:

publisher using wrong email client how to change default?
Ok - MS Pub 2007 is trying to send through windows mail I have switched to Windows Live mail. How do I tell publisher to use windows live mail and not windows mail? Robin1only wrote: > Ok - MS Pub 2007 is trying to send through windows mail I have > switched to Windows Live mail. How do I tell publisher to use > windows live mail and not windows mail? =================================== Have a look at the following articles: Windows Vista - Setting the Default Email Client http://tinyurl.com/2ygtyt or... http://freeemailtutorials.com/microsoftWindows/setDefaultEmailClientOnWi...

Copying sheets
I have a template that has some controls on it with associated code. It is necessary for my task to dynamically create worksheets based o this template. My problem is that the names of the controls chang when a sheet is copied and therefore when used Excel cannot find an code to execute and the control does not perform its function. I coul set up a workbook with, for example, 100 templates and delete 90 if th user only needs 10, but this seems really messy. Any suggestions gratefully received. Thanks -- Message posted from http://www.ExcelForum.com Sorry, I hope nobody minds me bumping...

How To Change Fonts In Multiple Charts in a Workbook?
I have a workbook with approx 120 charts spread out over 30 worksheets. Each worksheet has 4 charts on it. Each of the 4 charts has different data and Values. I would like them all to have the same fonts (made the mistake of no checkin when I produced the charts originaly ) but the X axis an coloring is different in each of the 4 charts. I have tried copying th "formats" but this changes everying in the chart but the data itself. Is there a macro to help with this or possibly something easier that am over looking? Thank -- Cwee ------------------------------------------------...

How to make a flexible cell reference?
Does anyone know how to make a reference to a cell flexible. I.e. I want a formula to get data from a cell, but want the cell to be dependant on some other value. E.g. =C"x+1" where "x+1" is a column number dependant on the value of x. I sure hope anyone can help me with this! Mark =INDIRECT("C"&x+1) -- HTH Bob Phillips "Mar Vernooy" <Mar Vernooy@discussions.microsoft.com> wrote in message news:40D873BD-1E6A-4412-BFCD-6A8B57B73E6F@microsoft.com... > Does anyone know how to make a reference to a cell flexible. I.e. I want a >...

colour formula
I have two column where I want to apply a formula. I want=20 to check A1 if there is a specific colour in a1 then it=20 should automatically put person=E8s name with who is going=20 to do the report. I have give different colours to=20 different people eg. light yellow to A, green to B etc. I=20 want if client XYZ is there an if its in green colour it=20 should automatically put B with green colour in b1. Any help appretiated. Hetal You'll need a UDF like the one on Chip Pearson's site: http://www.cpearson.com/excel/colors.htm If you're new to macros, you may want to read David...

Change report Text to show Negatives in ()
Can't seem to find the option. Currently negatives are being displayed as -123 Want them to show up as (123) Anyone know how to set this for a report? You can use an IIF statement like this -- My_Cost: IIF([YourField] <0, "(" & Abs([YourField]) & ")", [YourField]) -- KARL DEWEY Build a little - Test a little "James" wrote: > Can't seem to find the option. Currently negatives are being displayed > as -123 > Want them to show up as (123) > Anyone know how to set this for a report? > > > Access includes built ...

changing category color for one appointment in a series
I have reoccuring weekly appointments with clients...at times one appointment will be cancelled. In 2003 I could change the category color/lable for the single occurance within the series without changing the category color in the whole series. IE: I could color code one Tuesday appointment as cancelled without changing the following Tuesday appointments on the calendar for the next 6 months. Is this still possible in 2007? When I try to do so...I get a prompt that says I can only change category colors within the series. Dana, I was replicating this using our companies t...

Copy from merge cell
When I copy (Cell B2) from a merge cell for example A1 to S1 the result is ######### When I delete the range and copy from A1 it work, but when merge cell (A1 to S1) is blank "0" appear is B2. How to get rid of the "O" Try formatting that receiving cell (B2?) as General. Cells formatted as Text have problems when the length of the string is between 255 and 1024. MK wrote: > > When I copy (Cell B2) from a merge cell for example A1 to S1 the result is > ######### > When I delete the range and copy from A1 it work, but when merge cell (A1 to > S1) is blan...

Copy/Cut/Paste of Merged Cells in Shared Protected Workbooks
Hi I have a shared protected workbook into which comments and data are input on a daily basis to merged cells. In some cases the same information just has to be copied and pasted into a new position in the same worksheet. When the cell is copied and pasted the cell de-merges in the new position. Is there anyway of preventing this? The only alternative I can currently see is to copy or cut the text in a cell from the formula bar and then paste this into the new cell. Is this my only option? Any advice much appreciated Dave ...

Rule copies message
Several rules, in Outlook 2003, copy the message instead of moving it. For example, a simple rule states "Apply this rule after message arrives from Kristi and move it to the Kristi folder." The messages are copied to the Kristi folder, and a copy retained in my inbox. This happens for several rules. Why doesn't "move" mean just that to Outlook? Does it work when you disale all the other rules? When more than one rule applies they will all be executed. To prevent this add the action "stop processing rules" to the rule and sort your rules correctly. -...

how can I get a cell to change color by date to indicate expiery t
I'm trying to get the cell color to change from green to yellow to red as a date entered in the cell gets closer to that date plus 12 months or 36 months. ie some task was completed on 1 Jan 08 and will last for 24 months so I want the cell to read green if the date is still good turn yellow a month or 2 before it expires and turn red and clear the date if expired. So if the cell is empty it should show red. But I want to be able to update the date the task was completed ie it was completed again on the 15 Dec 10 before it expired so I want the countdown to expiration to restart f...

Change sheet tabs to different colors
Hello, I have Excel 2000. I noticed in 2003 version you can change the sheet tabs to different colors. Is there a way to do it in 2000 version? Thanks Mare Hi Mare, No, not in Excel 2000, colored sheet tabs was not included until Excel 2002.. But there are other things that you can do to organize your sheets. See the second half of Changing the Colors of Worksheet Tabs (#tabs) http://www.mvps.org/dmcritchie/excel/colors.htm#tabs You can sort your worksheet tabs and you can add small prefixes to help you sort them into an order that makes sense to you. --- HTH, David McRitchie,...

help with range copy
HI all, This is what I am trying to do, look in range Column A in active sheet, copy upto whatever range is filled down and paste in sheet 2. the code below is copying only the last cell and pasting in sheet 2. Range("A65536").End(xlUp).Copy _ Worksheets("sheet2").Range("a65536").End(xlUp) Appreciate your help. Thanks Syed -- saziz ------------------------------------------------------------------------ saziz's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=6350 View this thread: http://www.excelforum.com/showthread.php?thread...

How to change default autoarchive run time
How to change default autoarchive run time, say if I only want autoarchive to be run at 12:00 PM? ...

Paste Formula #2
I am looking for a keyboard equivalent to Paste Special Formula and Paste Special Value Alt-e,s,v for Paste Special>Values Alt-e,s,f for Paste Special>Formulas "Spikesmom" wrote: > I am looking for a keyboard equivalent to Paste Special Formula and Paste > Special Value ...

Help with formulae
BHi All, I am new to excel, so any help would be great :) I want to automatically insert a set text string for instance, ABC into A1 only if there is a number inserted into cell B1 for instance 12345. So if I insert 12345 into cell B1, A1 would then show ABC, if I insert 6789 into B2, A2 would then again return ABC. However if there is no value in B1 or B2 then I need A1 and A2 to be blank and not return N/A# etc =IF(B1=12345,"ABC","") put that formula in A1 and copy to A2 -- HTH Bob Phillips (remove nothere from the email address if mailing direct) "Racing...

formula help.. text
I am trying to get a series of cells to fill with a color if the word "insert" is inserted into the first cell. I.E. cells A2-A5 and cell A2 has "insert" typed in so up to A5 will change color. How do I go about doing this? Try Format | Conditional Formatting - after selecting cells A2:a5 Then use "Formula is" and type in =$A2 = "insert" Then click on the Format button and select the color you want from the Patterns tab "Murph" wrote: > I am trying to get a series of cells to fill with a color if the word > "insert&qu...

Excel Formula to colourfill cells when condition met
I daily populate a sheet sent to me with answers (time & dates) of 1,2 or 3 star items, the 3 star items I manually fill with a certain colour. Is there any way of filling a row that contains a cell with 3 stars automatically with a designated colour. In this example colour filling the whole row because one of the cells contains *** 15-May bx3008 aaaaaaaaaaaa *** bbbbbbbbbbbbbbbb ccccccc Select all the data - I will assume each row foes fro A to G and that the data starts on row 1 In the conditional formatting dialog use this formula =COUNTIF($A1$:G1,&qu...

Get/Change first operation error
I am getting this error message when entering MBS: A get/change first operation on table 'PA_Timesheet_SETP' failed accessing SQL Data. More Info: Could not find stored procedure 'TEST.dbo.zDP_PA41801F_1' Invalid column name 'PA_Update_Periodic_Opt' Version 8.0 thanks -- Doug Hi Its looks like you have not run the PAUtils to Install PA for Great Plains. The Project Accounting module in Great Plains cannot find any tables - stored procs on the Test Company / Database. -- Regards James[MVP] Visit MBS Blog Central http://mbscentral.blogs.com "Doug&quo...

Can I change the default front from Tahoma to Arial
-- Karen Click on Tools>Options>General then change the standard font If that doesn;t work try Format>Style make sure the style is normal, then click modify and change the font. -- _______________________ Naz, London "Karen" wrote: > > -- > Karen I am assuming you are referring to the spreadsheet in general. If so, go to Tools / Options and select the General Tab. Midway down is the standard font. Let me know if you have more questions. thanks, Chad -- cvolkert ------------------------------------------------------------------------ cvolkert&...

Copying selected rows to a new workbook
I'm trying to look at all rows in a worksheet. If the value in column C has the word case in it, I want to copy it to a new workbook. The macro does create a new workbook, but nothing gets copied over. I could use some help. Thanks! Jeff Sub JustcaseWorkbook() ' ' JustcaseWorkbook Macro Dim i As Integer, shName As String Dim rowData As Range, rowXAxis As Range Dim chtTitle As Range Workbooks.Add Windows("LABELINVENTORY7-20-2011 (1).xls").Activate ' shName = "Sheet1" Set rowData = Sheets(shName).Range("a5:c5") For i = 5 To 194 If Range...

Leaving "Change transaction matching" screen freezes Money 2007 Deluxe
Whenever I leave the "Change transaction matching" screen (either by pressing OK or Cancel) Money 2007 Deeluxe (Version 16.0.120.1303) freezes up and I have to kill the process. Whenever I restart Money, the file gets repaired and whatever change I made is reflected in the account register. Anybody has seen this an/or can provide a solution? :: Marcus ...

Copies of documents on my desktop
Hello, When I open web documents (such as PDFs), a copy of the document automaticaly goes onto my desktop. I don't want to save these documents to my desktop when I open them, I just want to view them. Is there a way to prevent this from happening? Any help would be greatly appreciated. Thank you. In article <444257df.0406030754.7e1117ff@posting.google.com>, adewing@princeton.edu (Andrew) wrote: > Hello, > > When I open web documents (such as PDFs), a copy of the document > automaticaly goes onto my desktop. I don't want to save these > documents to my de...

any way to select multiple folder for properties change?
I would like to apply the same properties change to a number of subfolders of a main folder. Is there any way to do this, except selecting each folder one-at-a-time to make the same propertiies change? I Live On Outlook No. -- Sue Mosher, Outlook MVP Author of Microsoft Outlook Programming - Jumpstart for Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx "I live on Quicken and Outlook" <Private@privacy.org> wrote in message news:vof6l09v3g4bdm119pjo7ujk0j02kur08e@4ax.com... >I would like to apply the same properties ...

change default report
Is there a way to change the default report that opens when you select Format = Blank Paper? If so, please share with me how this is done. Someone modified our Picking Ticket and instead of opening up "SOP Blank Picking Ticket Form" it opens "SOP Blank Picking Ticket Order Entered" that they modified. It looks like instead of modifying (again) the first one, they duplicated it, did more changes, then changed the default to this new version. Also, in the Report Resource, it shows that SOP Blank Picking Ticket Order Entered is Id #1205 while SOP Blank Picking Ticket...