Copy relative formula across sheets

  • Thread starter Thread starter mmwilcox
  • Start date Start date
M

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
 
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
 
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
 
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
 
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
 
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
 
Back
Top