Hi Ronnie

The only way I can think to do this requires a little VBA. Basically
wherever you need to reference your "third" sheet in the first or second
sheets you use the "Activesheet" and "Range" properties to put the correct
numbers in their respective places.

Here's my simple example. I have two workbooks called "MyWB1.xls" and
"MyWB2.xls". In MyWB1 I want cell A1 to have the same value as A1 in MyWB2
and I want cell A3 to have the same value as A2 in MyWB2. So here's the code
that is written to MyWB2 (which would be your "third" sheet).

Sub UpdateValues()
With Workbooks("MyWB1.xlsx").Worksheets(1)
.Range("A1").Value = ThisWorkbook.ActiveSheet.Range("A1").Value
.Range("A3").Value = ThisWorkbook.ActiveSheet.Range("A2").Value
End With
End Sub

Now all you do on your third sheet is insert a shape (anything you like),
right click and then "Assign Macro". You assign the above macro. Now when
someone copies this sheet, they copy the button as well. They can modify the
values and then simply click the button to do the calculation.

If this is helpful click yes.
Rick

"Ronnie" wrote:

Hi,

How can I make a cell on a worksheet always accept input from a certain cell
on any new copy of another worksheet?

In Excel 2003 I have made a workbook which I could use as a template, but I
would like to just have one book for multiple transactions, each on a newly
created sheet.

Base figures and calculations are on two sheets. One is protected with the
user allwed to modify one unlocked cell only. The other is protected with no
edits allowed. These sheets accept information from and display results on a
third sheet.

The third sheet is also protected, but the user can enter a variety of
required information in certain cells, which pass the user input back to the
two sheets containing the base calculations and formulae, and obtains the
results from them. Results are displayed in non editable cells. I intend
this sheet to be a template sheet so that using the Move or Copy context menu
the user can create and name a new sheet for a new transaction. The
information is passed about by Copy Paste Special Paste Link.

The problem is that when I make a new copy of the third woksheet, the input
is not passed back to the base sheets, as those sheets only reference the
original third sheet.

I have looked at 3d references, but a predetermined number of worksheets
must exist for that to work.

I'd be grateful for any advice on this.

Regards