| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Name Ranges...
I have one workbook where I have named ranges.
Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
P.S. It's not just using the same name. I need to use the same ranges as the
two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
The below would refer to the first cell of named range Name1 from saved
workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
Hi Jacob, thanks but I can't seem to work it.
I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
You need to mention the workbook name. Names collection represents all the
names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
I'm feeling thicker than two bricks. Can you put it simply?
The workbook that contains the names is called "Pillars" and the sheet the data is in is called "Jan". Can you use the following formula to stick those in? =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) Sorry - I am still learning excel. Let me know if I need to explain better? "Jacob Skaria" wrote: You need to mention the workbook name. Names collection represents all the names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
Try
'if open =SUMPRODUCT((Pillars.xls!date_Jan=B15)* (Pillars.xls!date_Jan<=B16),Amount_Jan) 'if Pillars.xls is closed =SUMPRODUCT(('D:\Pillars.xls'!date_Jan=B15)* ('D:\Pillars.xls'!date_Jan<=B16),Amount_Jan) PS: Make sure Amount_Jan is of the same dimension as the named range Date_Jan -- Jacob "Vixter" wrote: I'm feeling thicker than two bricks. Can you put it simply? The workbook that contains the names is called "Pillars" and the sheet the data is in is called "Jan". Can you use the following formula to stick those in? =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) Sorry - I am still learning excel. Let me know if I need to explain better? "Jacob Skaria" wrote: You need to mention the workbook name. Names collection represents all the names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
Thanks for all your input and time. Nothing is working - and because I'm on a
network the location of the file is long. What I've done is calculate the formula without the names (just as a range - while Pillars.xls was open) and then I simply typed in the range names over the cell ranges - seemed to have worked. I'm still learning. Thanks again. "Jacob Skaria" wrote: Try 'if open =SUMPRODUCT((Pillars.xls!date_Jan=B15)* (Pillars.xls!date_Jan<=B16),Amount_Jan) 'if Pillars.xls is closed =SUMPRODUCT(('D:\Pillars.xls'!date_Jan=B15)* ('D:\Pillars.xls'!date_Jan<=B16),Amount_Jan) PS: Make sure Amount_Jan is of the same dimension as the named range Date_Jan -- Jacob "Vixter" wrote: I'm feeling thicker than two bricks. Can you put it simply? The workbook that contains the names is called "Pillars" and the sheet the data is in is called "Jan". Can you use the following formula to stick those in? =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) Sorry - I am still learning excel. Let me know if I need to explain better? "Jacob Skaria" wrote: You need to mention the workbook name. Names collection represents all the names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
Try whether the below is returning the first item from the named range
'with file open =INDEX(Pillars.xls!date_Jan,1) -- Jacob "Vixter" wrote: Thanks for all your input and time. Nothing is working - and because I'm on a network the location of the file is long. What I've done is calculate the formula without the names (just as a range - while Pillars.xls was open) and then I simply typed in the range names over the cell ranges - seemed to have worked. I'm still learning. Thanks again. "Jacob Skaria" wrote: Try 'if open =SUMPRODUCT((Pillars.xls!date_Jan=B15)* (Pillars.xls!date_Jan<=B16),Amount_Jan) 'if Pillars.xls is closed =SUMPRODUCT(('D:\Pillars.xls'!date_Jan=B15)* ('D:\Pillars.xls'!date_Jan<=B16),Amount_Jan) PS: Make sure Amount_Jan is of the same dimension as the named range Date_Jan -- Jacob "Vixter" wrote: I'm feeling thicker than two bricks. Can you put it simply? The workbook that contains the names is called "Pillars" and the sheet the data is in is called "Jan". Can you use the following formula to stick those in? =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) Sorry - I am still learning excel. Let me know if I need to explain better? "Jacob Skaria" wrote: You need to mention the workbook name. Names collection represents all the names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
It's returning a number item but not the right amount.
Don't worry. Even though I'm using a long-winded formula it should be ok. Thanks anyway. Vixter "Jacob Skaria" wrote: Try whether the below is returning the first item from the named range 'with file open =INDEX(Pillars.xls!date_Jan,1) -- Jacob "Vixter" wrote: Thanks for all your input and time. Nothing is working - and because I'm on a network the location of the file is long. What I've done is calculate the formula without the names (just as a range - while Pillars.xls was open) and then I simply typed in the range names over the cell ranges - seemed to have worked. I'm still learning. Thanks again. "Jacob Skaria" wrote: Try 'if open =SUMPRODUCT((Pillars.xls!date_Jan=B15)* (Pillars.xls!date_Jan<=B16),Amount_Jan) 'if Pillars.xls is closed =SUMPRODUCT(('D:\Pillars.xls'!date_Jan=B15)* ('D:\Pillars.xls'!date_Jan<=B16),Amount_Jan) PS: Make sure Amount_Jan is of the same dimension as the named range Date_Jan -- Jacob "Vixter" wrote: I'm feeling thicker than two bricks. Can you put it simply? The workbook that contains the names is called "Pillars" and the sheet the data is in is called "Jan". Can you use the following formula to stick those in? =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) Sorry - I am still learning excel. Let me know if I need to explain better? "Jacob Skaria" wrote: You need to mention the workbook name. Names collection represents all the names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Name Ranges...
You can also assign a drive letter to a network directory and all your
users access that network directory through that drive letter assignment. An alias. On Mon, 25 Jan 2010 04:07:01 -0800, Vixter wrote: Thanks for all your input and time. Nothing is working - and because I'm on a network the location of the file is long. What I've done is calculate the formula without the names (just as a range - while Pillars.xls was open) and then I simply typed in the range names over the cell ranges - seemed to have worked. I'm still learning. Thanks again. "Jacob Skaria" wrote: Try 'if open =SUMPRODUCT((Pillars.xls!date_Jan=B15)* (Pillars.xls!date_Jan<=B16),Amount_Jan) 'if Pillars.xls is closed =SUMPRODUCT(('D:\Pillars.xls'!date_Jan=B15)* ('D:\Pillars.xls'!date_Jan<=B16),Amount_Jan) PS: Make sure Amount_Jan is of the same dimension as the named range Date_Jan -- Jacob "Vixter" wrote: I'm feeling thicker than two bricks. Can you put it simply? The workbook that contains the names is called "Pillars" and the sheet the data is in is called "Jan". Can you use the following formula to stick those in? =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) Sorry - I am still learning excel. Let me know if I need to explain better? "Jacob Skaria" wrote: You need to mention the workbook name. Names collection represents all the names in the active workbook. (names defined with the Workbookname and "WorksheetName!" as prefix). -- Jacob "Vixter" wrote: Hi Jacob, thanks but I can't seem to work it. I basically need a sumproduct of "Amount" from the other workbook. (I'm doing it between dates). In my workbook with the names I use the following formula: =SUMPRODUCT((Date_Jan=B15)*(Date_Jan<=B16),Amount _Jan) I need to use the same formula but without just copying it from one workbook to another. Is this possible? Thanks. "Jacob Skaria" wrote: The below would refer to the first cell of named range Name1 from saved workbook Mybook.xls =INDEX(MyBook.xls!Name1,1) -- Jacob "Vixter" wrote: P.S. It's not just using the same name. I need to use the same ranges as the two workbooks are connected. "Vixter" wrote: I have one workbook where I have named ranges. Is it possible to somehow use those names in formulas in a completely different workbook? Excel 2003. Any help is appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how copy formula that contains ranges so ranges do not overlap | Excel Worksheet Functions | |||
| Ranges | Excel Worksheet Functions | |||
| Like 123, allow named ranges, and print named ranges | Excel Discussion (Misc queries) | |||
| Ranges | Excel Discussion (Misc queries) | |||
| Sum ranges | Excel Worksheet Functions | |||
Linear Mode
