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.