Can I copy consecutive formulas to different worksheet

Hi

Here's one (non-VBA) way:

1. Define a name and call it ShtNm. In refers to:

=REPLACE(CELL("filename",!$A$1),1,FIND("]",CELL("filename",!$A$1)),"")

2. Define a second name and call it ShtNo. In refers to:

=GET.DOCUMENT(87,ShtNm)

3. In your sheets, in A1 type:

=Total!$A$1+ShtNo-1

Hi Richard, many thanks for your explanation.
However, I do not fully understand.

nothing you have done wrong I'm just new to the GET.DOCUMENT and in fact REPLACE commands. Could you just explain in slightly mopre detail what they refer to.

GET.DOCUMENT is an old XLM4 macro function which used to be used in Excel on a regular basis in xl95 (ie before the advent of the joy that is VBA ). They can still be used in workbooks but only in defined names, not in worksheet cells. The 87 in the first argument of GET.DOCUMENT tells the function we want the sheet count number, the second argument needs to be the sheet name of the sheet we want the count for. We get this from another defined name using Replace:

=CELL("filename",$A$1)

returns the full workbook name and path and sheet name of the referenced cell (ie A1). We only want the sheet name part, so we need to remove the path & workbook name. We do this using REPLACE. We know that a workbook name/path will be in the formta:

C:\myFolder\[myFile.xls]Sheet1

The closing square bracket will always be present (unless the workbook hasn't been saved ever), so we just need to locate the ] and use that as a marker. We use REPLACE to replace from the 1st character up to the position of the ] bracket with an empty string:

=REPLACE(CELL("filename",!$A$1),1,FIND("]",CELL("filename",!$A$1)),"")

which just leaves us with the sheet name, which we can use in our XLM4 name.

Make sense?

Wow Richard, thanks for taking the time to explain.
It does make sense. I appreciate it.

I was also wondering if it was possible to "drag" formulae over worksheets somehow.

I.e. Typing in consecutive cells 1, 2, 3... and then dragging the formula to autofill

can I autofill over multiple worksheets in some way without VBA?