Named Formula across multiple worksheets

[DSMessenger]

Working in Excel 2003. Using Named Formulas for use repeatedly across 7000
rows and in multiple columns. Trying to get them to work across multiple
sheets (ie use common formula across entire workbook).

Simple example (not the formula I am using)

Named formula "for_multiply" =Sheet1!A1*2 uses relative references
So if I have A1 = 2 and enter B1=for_multiply I get 4
Alternatively if I have A2 =4 and copy formula down to B2=for_multiply I get 8

Works on any Cell across entire Worksheet Sheet1.

However if I use this Named formula on another worksheet (Sheet2) it keeps
referring back to Sheet1. I assume there is an obvious fix I am missing ?

I can solve this easily by creating functions using Macros but I want to
avoid this as I don't want to have to explain how to maintain this to my
novice user.

Thanks in advance.

 

[DSMessenger]

I probably should have posted this under "Excel Worksheet Functions" but I so
commonly come to the "Excel Programming" heading I posted it here by pure
reflex.

 

[Arvi Laanemets]

Hi

Use UDF instead for Name. An example:
Create an UDF Test()
Public Function Test(parDouble As Double) As Double
Test = 2 * parDouble
End Function

Into some cell on any sheet enter a formula:
=Test(A1)


Arvi Laanemets

 

[Peter T]

You could use a similar worksheet level (aka local) name on sheet. When you
copy the named formula between sheets you'll get a self explanatory if
annoying message each time.

As you are here (programming) here's a simple macro to make your similar
local names. I separated the loop so you can call the AddSheetName routine
in the workbook's sheet-activate event, to ensure the name exists on any
newly added sheets.


Sub AddAllLocalNames()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
AddSheetName ws
Next

End Sub

Sub AddSheetName(ws As Worksheet)
Dim sF As String

sF = "='" & ws.Name & "'!RC[-1]*2"
ws.Names.Add "for_multiply", sF

End Sub

Note that with named formulas that refer to cells, if you move cells after
using as a worksheet formula, the references do not update (as they do with
non-named formulas).

Regards,
Peter T

 

[DSMessenger]

Thanks for the two suggestions. Trying to avoid macros in this case so I
should have posted under the other heading.

What I have tried this morning is I have changed
for_Multiply to =for_Cell*2
ie have a named formula calling another named formula

Then made for_Cell =INDIRECT("R"&ROW(Sheet1!A1) & "C"
&COLUMN(Sheet1!A1),FALSE)
(probably a neater formula for this but I just woken up)

That way when, on the original worksheet, I copy for_Multiply down across
rows, or across columns it still works. However if I copy for_Multiply across
to different Worksheets it also works (on the other Worksheet) as for_Cell
removes the original Worksheet reference.

I will think about it more this morning and attempt to find a neater function.