Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Formula across multiple worksheets
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Formula across multiple worksheets
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. "DSMessenger" wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Formula across multiple worksheets
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 "DSMessenger" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Formula across multiple worksheets
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" wrote in message ... 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. "DSMessenger" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named Formula across multiple worksheets
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. "Arvi Laanemets" wrote: 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 "DSMessenger" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum Multiple Worksheets, same named cell | Excel Worksheet Functions | |||
Named Ranges - Multiple Worksheets | Excel Discussion (Misc queries) | |||
same named range on multiple worksheets? | Excel Discussion (Misc queries) | |||
dynamic chart on multiple worksheets based on named formulas? | Excel Programming | |||
dealing with multiple named worksheets with one macro | Excel Programming |