Copy relative formula across sheets

[mmwilcox]

Can I copy this Sheet B formula: "SheetA!A1 + SheetB!A1 " from sheet
to sheet C and have the formula treat the sheet names in the formula a
relative addresses rather than absolute?

I can only get it to paste as "SheetA!A1 + SheetB!A1 " when I want i
to convert to ""SheetB!A1 + SheetC!A1"

This is very useful when setting up a running total formula ove
several sheets, say 12 sheets representing months of the year.

Thanks,

Mat

 

[AlfD]

Hi!

I don't believe there's a direct way.

However, it is possible to simulate it using the worksheets' name
rather like tags on VB objects.

Name the worksheets consecutively Q1,Q2 etc

Use a method of putting each sheet's name into a cell on the sheet.
In Q3 cell A1 put

="Q" & VALUE(RIGHT(SHEETNAME(),1))-2

This will read Q1.

Copy it and then paste in Q4 cell A1

This will read Q2.

There may be the bones of a solution there, I think.

What is this function SHEETNAME()? Well, it's from a free addin calle
MoreFunc from Laurent Longre (q.v.).
Alternatively, there are "native" Excel functions which will retriev
the name of a sheet: I've seen them in this forum but never used them
No way, when Sheetname() is so simple!

Al

 

[mmwilcox]

Thanks Alf

I found MoreFunc and was able to catch up to you. It does seem like i
ought to provide a foundation for a solution, but I can't quite ge
there. I can get it re-create the referred-to worksheet name, but
can't get that into a working formula. I end up with a cell that show
'+Q2!C2' instead of looking at that as a formula and retrieiving th
result of that worksheet/ cell reference. Any ideas here?

Also, if there was a way to retrieve the sheet number (outside of VB
to keep this simpler in day to day workbooks), that may also lead me t
a solution.

Thank you for the concept!


Mat

 

[AlfD]

Hi!

Better news.

While we've been threshing around, Frank Kabel has posted somethin
more useful on another board (worksheet.functions). He uses th
INDIRECT worksheet function thus:

=INDIRECT("'Sheet" & ROW(1:1) & "'!A1") (slightly edited)

to reproduce the formula in A1 from sheet to sheet.

You'll see it too strings together the Sheet and its index no. and th
cell name and a few separators.
The key is INDIRECT.

I found it through a Google search. It's 1 day old!

Al

 

[mmwilcox]

Yes...That does the trick. I'd never used the "indirect" functio
before. Its a good one to know.

Copying formulae relatively from one sheet to another was a simpl
default in Lotus 1-2-3. What a challenge in Excel!

I was also experimenting with simple VBA functions calle
"rightsheet()" and leftsheet() which would calculate the name of th
previous and next worksheets. Problem is, I can only get it to use th
active sheet as a reference, rather than the sheet in which the formul
resides. So, when I jump to another sheet, the results o
rightsheet() changes in other sheets.

Thanks much for your help!

Mat

 

[AlfD]

Matt:

You're welcome. I learned something on the way, too! Glad it's comin
together.

For a short time I used Quattro Pro which made a virtue of it
3-dimensionality.

Al