Copying a Sheet and Incrementing a Forumla On the New Sheets

[jango_fetch]

I have tried searching through the forums to hopefully find an answer before posting, but I could not find anything. So please direct me to another thread if I simply failed to find it.


Sheet1
This sheet has data in cells B3 – B52
Ex.
B3 = 750 E
B4 = 750 F
…
B42 = 610 B
…
B52 = (Blank, Future Use)

The other sheet is a blank sheet:
Blank (I don’t need to namethese unless it is required for what I am trying to do)
This sheet contains a cell, P4, with a title referencing the masterlist in Sheet1
Ex.
Blank P4: =Sheet1!B3

So what I want to do is have the next blank sheet reference the nextmachine.
Ex.
Blank(1) P4 =Sheet1!B4

I need to do this for 50 sheets, and for 3 separate items, (Press,Plant, and Date). Is there a formula Iam missing or a known macro for such a task?
These machines, locations, and dates will change at times, be moved,removed, etc. so being able to change them all from the master will help savetime. Also, just learning how to do thiswill make me more efficient in the future for similar applications.

If I missed necessary information, please let me know.

Thanks,

jango

 

[Yongle]

You must save the workbook before entering the formula (it uses the saved file name)

Name your sheets 1,2,3,4 etc (the sheet name is a number, that number is used in the formula)

Place this formula in P4 in each sheet
=INDIRECT("Sheet1!"&ADDRESS(2 +MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),2,4))


If you want to adapt it for other cells or are curious and interested in puzzling out how it works, put these formula in any cell in "1"
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)
=ADDRESS(2 +MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255),2,4)
=ADDRESS(3,2,4)
=INDIRECT("Sheet1!"&ADDRESS(3,2,4))
=INDIRECT("Sheet1!B" & "3")