Formula referencing previous sheet

[chrismansell]

Hi there I am after a formula to copy the contents of a cell in the previous sheet in a workbook, so that if I was to copy the last sheet in a workbook the formula would automatically reference the cell from the copied worksheet and so on if I copied tht one.

Hope this makes sense I have a lot of formulas referencing the previous sheet and everytime I copy this sheet to create a new sheet I have to change the sheet number in the formulas.

eg, in sheet 8 this formula get info from sheet 7 cel J30 ='7'!J30 when i copy the sheet to create a new one (Sheet 9) I would like the new formula to automatically be ='8'!J30.

Thanks in advance Chris

 

[phxsportz]

You can referrence the previous sheet name easily enough..

PrevSN = "Sheet" & Val(Mid(activesheet.codename,6,(len(activesheet.codename)-5)))-1

 

[chrismansell]

Hi Again Im sorry but I am a bit of a beginner and I cant work out how to make this work could you perhaps put ='8'!J30 into it so I can see how it fits.

Cheers Chris

 

[phxsportz]

Gotcha, No problem... here's simple little UDF to handle what ya need..

Right click on any worksheet tab and click on "VIEW CODE"

Place the following code into MODULE 1. (if it's not there just click INSERT and MODULE)

Function oldsheet(rng As Variant)
    Prevsn = "Sheet" & Val(Mid(ActiveSheet.CodeName, 6, (Len(ActiveSheet.CodeName) - 5))) - 1
    With Sheets(Prevsn)
        oldsheet = .Range(rng)
    End With
End Function

Now.. in your worksheet (J30) just put the following formula

=oldsheet("J30")

<b>Sheet2</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:126px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >J</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >J30 from Sheet1</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >J30</td><td >=oldsheet("J30")</td></tr></table></td></tr></table>

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Arial,Arial; font-size:10pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:130px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >J</td></tr><tr style="height:17px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >30</td><td >J30 from Sheet1</td></tr></table>

 

[GTO]

You might be able to use a helper cell like:

Sheet 34

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Arial,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=1><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 64px"><COL style="WIDTH: 128px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="TEXT-ALIGN: right">34</TD><TD> </TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD> </TD><TD>Hi from Sheet 33</TD></TR></TBODY></TABLE>
<TABLE style="FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00; BORDER-BOTTOM-COLOR: #00ff00; COLOR: #000000; BORDER-TOP-STYLE: groove; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-RIGHT-STYLE: groove; BORDER-LEFT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-RIGHT-COLOR: #00ff00; BORDER-BOTTOM-STYLE: groove"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-SIZE: 9pt; FONT-FAMILY: Arial" cellSpacing=0 cellPadding=2 border=1><TBODY><TR style="FONT-SIZE: 10pt; BACKGROUND-COLOR: #cacaca"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>A1</TD><TD>=RIGHT(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256),LEN(MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256))-6)</TD></TR><TR><TD>B2</TD><TD>=INDIRECT("'Sheet "&$A$1-1&"'!J30",TRUE)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


Please note that the space needs included in the Indirect.
=INDIRECT("'Sheet "&$A$1-1&"'!J30",TRUE)

This all presumes your sheets to be named as: "Sheet 9", "Sheet 10", "Sheet 11" and so on, as we're returning the name minus the leftmost 6 characters.

Mark

 

[chrismansell]

hi Mark Thanks

I have started at sheet 3 as sheet 1 is input, sheet 2 has different formula and sheet 3 thru to sheet 130 take info off the previous sheet.
Would you like to see what i am doing it may make it easier?

Cheers Chris

 

[GTO]

hi Mark Thanks

I have started at sheet 3 as sheet 1 is input, sheet 2 has different formula and sheet 3 thru to sheet 130 take info off the previous sheet.
Would you like to see what i am doing it may make it easier?

Cheers Chris

Hi Again Im sorry but I am a bit of a beginner and I cant work out how to make this work could you perhaps put ='8'!J30 into it so I can see how it fits.

Cheers Chris

Hi Chris,

I take it that neither phxsportz nor my suggestions seem to be working at the moment.

If you subscribe to a filesharing service such as 4shared.com or one of the many others, you could upload an example file there, of course stripped of any private/company info.

That may not be necessary though. What are the actual sheet names?

Are they:

Sheet 2
Sheet 3
Sheet 4
(and so on...)

Or, are they:

2
3
4
(And so on...)

I ask because ='8'J30 makes it appear that they may be named using just the numbers, which is fine of course, but we need to know accurately as to the naming for anything to work.

Mark

 

[xenou]

If I can say this without sounding cheeky --

If '7'!J30 = '6'!J30
And '6'!J30 = '5'!J30
And '5'!J30 = '4'!J30
etc. etc.
Then wouldn't all the cells have the same value?
(if a = b and b = c then a = c)

Hence, confused.

 

[chrismansell]

Hi There
I think I may have caused some confusion.
Sheet names are
Entry Page,1,2,3,4.....etc
sheet label 2 Cell J6 will have the formula = '1'!J30
J30 is the total of the J column on 1
phxsportz, I cant seem to make your sugestion work I have entered =oldsheet("J30") in 2 cell J6 and enered the function in a module as sugested but nothing seemed to work.
Mark I only have a basic knowledge of excel and I have difficulty putting your sugestion to work.(My lack of experience)

Regards Chris

 

[phxsportz]

Not sure why the function wouldn't work, however, it seems that Alexanders observation makes perfect sense, in as much as every single cell for all worksheets would be identical. Personally, I though perhaps I had consumed too much peppermint schnapps when I saw it, but... oops... lol

Anyway.. I'm sure that's not what you meant.. so perhaps a slightly different scenerio would be in order.....