Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
Hi there,
I would like to create a Defined NAME: eg. a1:a10 =sige which is relative to the sheet. So when having the formula sum(sige) on sheet1 it will give me the result of Sheet1!a1:a10 & on sheet2 it will return me the result of sheet2!a1:a10 Creating the name as: !a1:a10 (without the sheet name) is not a solution is it? Thanks for your insights, Sige |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
Did you try it?
=!$A$1:$A$10 works for me. In article . com, "Sige" wrote: Hi there, I would like to create a Defined NAME: eg. a1:a10 =sige which is relative to the sheet. So when having the formula sum(sige) on sheet1 it will give me the result of Sheet1!a1:a10 & on sheet2 it will return me the result of sheet2!a1:a10 Creating the name as: !a1:a10 (without the sheet name) is not a solution is it? Thanks for your insights, Sige |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
Hi JE,
Yes I did as I thought it would be the way to go ...and it works as you said. But it does not recalculate!!! (the Calculation is on Automatic) But even on F9, Shift F9, Ctrl ShiftF9 it does not recalc. Only when I set my cursor in the formula and Enter. I doubt if it has anything to do with XL97 ... Hmmmmmm... what's the catch this time again? Sige |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
You must type in the full name of the sheet for the named range to be WS
specific. Sheet1!sige Sheet2!sige ...etc. However, if you plan ahead, you can create this named range in your first sheet, and then any sheet *copied* from this first sheet will, thereafter, contain this sheet specific named range. -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Sige" wrote in message s.com... Hi JE, Yes I did as I thought it would be the way to go ...and it works as you said. But it does not recalculate!!! (the Calculation is on Automatic) But even on F9, Shift F9, Ctrl ShiftF9 it does not recalc. Only when I set my cursor in the formula and Enter. I doubt if it has anything to do with XL97 ... Hmmmmmm... what's the catch this time again? Sige |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
Hi Ragdyer,
Thanks too! Though the sheets (a little 50...) do exist already. JE's solution is the way to go I am sure ... but it does NOT recalc for some mysterious reason! This behaviour occurs in all my wbks, even brand new ones! Sigh sigh |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
Insert|Name|Defind
Names in Workbook: Sige refers to: =indirect("a1:a10") may work for you. Sige wrote: Hi there, I would like to create a Defined NAME: eg. a1:a10 =sige which is relative to the sheet. So when having the formula sum(sige) on sheet1 it will give me the result of Sheet1!a1:a10 & on sheet2 it will return me the result of sheet2!a1:a10 Creating the name as: !a1:a10 (without the sheet name) is not a solution is it? Thanks for your insights, Sige -- Dave Peterson |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
I like that Dave!
Learn something new every day.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Dave Peterson" wrote in message ... Insert|Name|Defind Names in Workbook: Sige refers to: =indirect("a1:a10") may work for you. Sige wrote: Hi there, I would like to create a Defined NAME: eg. a1:a10 =sige which is relative to the sheet. So when having the formula sum(sige) on sheet1 it will give me the result of Sheet1!a1:a10 & on sheet2 it will return me the result of sheet2!a1:a10 Creating the name as: !a1:a10 (without the sheet name) is not a solution is it? Thanks for your insights, Sige -- Dave Peterson |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Defined name Relative to sheets
Hi Dave,
This one does it even for me :-) Thanks a lot Sige |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
insert Rows with Formulas in Place on Multiple Sheets? | Excel Discussion (Misc queries) | |||
Averaging using relative references across sheets | Excel Discussion (Misc queries) | |||
In 3 active sheets in wkbk, determine& display the # of sheets that have data | Excel Discussion (Misc queries) | |||
calculating excel spreadsheet files for pensions and life insurance (including age calculation sheets) | Excel Worksheet Functions | |||
how to copy sheets keeping defined column widths | Excel Worksheet Functions |