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]

PS....

**** Schnapps.. !DOH

 

[Legacy 68668]

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

UDF
Use in cell like
=Prev(J30)

Function Prev(ByRef r As Range) As Variant
    Application.Volatile
    Prev = r.Parent.Previous.Range(r.Address).Value
End Function

 

[GTO]

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
Regards Chris

Okay, so J6 continues a running total from the last sheet. Seiya's UDF would seem to fit the bill easily; so this is just to clarify the formula, as the sheet names just being an integer actually would simplify it.

If you were wanting a formula, I believe this would work.

From sheet "2" and thereafter, in J6:

=INDIRECT("'" & MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)-1 & "'!J30",TRUE)

Please note that "$A$1" can be any cell from my (limited) understanding of the formula, but the cell picked cannot return an error.

In any case, presuming macros are enabled, Seiya's UDF sure seems like a good ticket.

Hope that helps,

Mark

 

[chrismansell]

Hi Guys

Thanks Mark your formula worked a treat on all of the options I needed, I tried Seiya's UDF But there are 130 sheets and excel spent too much time calculating cells so much appreciated here is an example of what one of the formulas looked like with your additions
=SUMPRODUCT(($B$7:$B$29='2'!A35)*($J$7:$M$29))+INDIRECT("'" & MID(CELL("filename",$A$1),FIND("]",CELL("filename",$A$1))+1,256)-1 & "'!B35",TRUE)
There are about 100 per page so it ends up being quite large.
Again thanks for all your help its made the undoable doable.

Cheers Chris

 

[wsjackman]

if you have a hundred cells where you need to pull data from the previous sheet, it would be worthwhile to have one cell with the current sheet name say A1

in your reference cells, say j30, then use

= XXXXindirect("'"&a1-1&"'!J30")YYYY
where XXXX YYYY represents what you want to do wtih the data