Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
computed indirect cell refererces across workbooks
Some time ago, with help from this group, I learned how to use named
references, with names contained in worksheet cells. (=indirect (A1) or =indirect(NamedCell) etc.) I now wish to use this general technicique to create a reference across spreadsheets. For example, I have the link ='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN This works just fine. However, I want to retrieve cells based on an indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN etc, essentially replacing the text 'FebruaryTCN' with something like indirect(A1) or concatenate(A1,"TCN"). The references will be computed based on other information in the sheet containing the computed indirect cell references. Again, any help will be greatly appreciated. -- Bill Wehrmacher |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
computed indirect cell refererces across workbooks
Hi all,
Unfortunately, this question has been greeted with deafining silence. So, I thought I would try again with a slightly different tack, and perhaps a different problem. I have two spreadsheets. One spreadsheet has a number of cells that can be referenced, while in the spreadsheet, with commands like =september sales, or = sum(sales), or =sum(september). My problem now is to reference those same same named cells or groups of cells, from another spreadsheet. While I can refer to a cell with formulas like =='K:\DistPlan\2008crs\Costs 2008.xls'!$G$9, I would like to be able to refer to them with a formula something like ='K:\DistPlan\2008crs\Costs 2008.xls'!September Sales. I have not been able to find a tutorial on how to accomplish this either online or in either of the Excel books we have. As always, I don't think I am the first person who would do this, and so I belive there must be a way. Again, I would be very grateful for anyone's help on this matter. Thanks -- Bill Wehrmacher "Wehrmacher" wrote: Some time ago, with help from this group, I learned how to use named references, with names contained in worksheet cells. (=indirect (A1) or =indirect(NamedCell) etc.) I now wish to use this general technicique to create a reference across spreadsheets. For example, I have the link ='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN This works just fine. However, I want to retrieve cells based on an indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN etc, essentially replacing the text 'FebruaryTCN' with something like indirect(A1) or concatenate(A1,"TCN"). The references will be computed based on other information in the sheet containing the computed indirect cell references. Again, any help will be greatly appreciated. -- Bill Wehrmacher |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
computed indirect cell refererces across workbooks
You seem to understand the naming procedures in XL, so I don't understand
your problem. In your example, if you assign the name "September Sales" to G9, you can access it, or make reference to it, by that name. What am I missing in your question? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wehrmacher" wrote in message ... Hi all, Unfortunately, this question has been greeted with deafining silence. So, I thought I would try again with a slightly different tack, and perhaps a different problem. I have two spreadsheets. One spreadsheet has a number of cells that can be referenced, while in the spreadsheet, with commands like =september sales, or = sum(sales), or =sum(september). My problem now is to reference those same same named cells or groups of cells, from another spreadsheet. While I can refer to a cell with formulas like =='K:\DistPlan\2008crs\Costs 2008.xls'!$G$9, I would like to be able to refer to them with a formula something like ='K:\DistPlan\2008crs\Costs 2008.xls'!September Sales. I have not been able to find a tutorial on how to accomplish this either online or in either of the Excel books we have. As always, I don't think I am the first person who would do this, and so I belive there must be a way. Again, I would be very grateful for anyone's help on this matter. Thanks -- Bill Wehrmacher "Wehrmacher" wrote: Some time ago, with help from this group, I learned how to use named references, with names contained in worksheet cells. (=indirect (A1) or =indirect(NamedCell) etc.) I now wish to use this general technicique to create a reference across spreadsheets. For example, I have the link ='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN This works just fine. However, I want to retrieve cells based on an indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN etc, essentially replacing the text 'FebruaryTCN' with something like indirect(A1) or concatenate(A1,"TCN"). The references will be computed based on other information in the sheet containing the computed indirect cell references. Again, any help will be greatly appreciated. -- Bill Wehrmacher |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
computed indirect cell refererces across workbooks
Hi,
Sorry about the long delay in replying to your post. Let me try to be a little more clear. Let us assume that one has two spreadsheets, workbook#1, and workbook#2. Each of these sheets have rows and columns named. With the previous example, one of the two spreadsheeds (workbook#1)assigns name "September Sales" to G9 because G9 resides at the intersection of a column named September, and a row named Sales. Adding rows or columns may move the intersection to some other cell, but within that workbook, =September Sales will return the information desired regardless of the actual cell (G9, H13,etc... ) So, I would like to be able to extract the September Sales value from workbook #1 and put it into workbook #2. The problem I have is that I can not find some function that will extract "workbook#1!September Sales" and put it into a cell in workbook#2. I can explicitly name G9 as September_Sales in workbook#1, then I can reach out from workbook#2 and get workbook#1!September_Sales. However, I don't know a way to label what could be hundreds or thousands of individual cells in workbook#1 without going through them one at a time and using a insertNameDefine... process. The plot thickens. Suppose, I want to compute the name of the cell I want to retrieve in workbook#1 by looking at certain row and column names in workbook#2. Indirect references work just fine within a workbook, but I don't know how to use a cell name computed in workbook#2 to reference a named cell in workbook#1. For example, workbook#2 may also have a row named Sales and a column named September, and I might want to put the September Sales value from workbook#1 into the September Sales cell in workbook#2. Sorry to be so long winded, but the inability to reliably link workbooks together without having several workbooks crash every time one workbook is altered is getting to be a real problem. Again, thanks for your thoughts on the subject. Best of everything Bill -- Bill Wehrmacher "RagDyer" wrote: You seem to understand the naming procedures in XL, so I don't understand your problem. In your example, if you assign the name "September Sales" to G9, you can access it, or make reference to it, by that name. What am I missing in your question? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wehrmacher" wrote in message ... Hi all, Unfortunately, this question has been greeted with deafining silence. So, I thought I would try again with a slightly different tack, and perhaps a different problem. I have two spreadsheets. One spreadsheet has a number of cells that can be referenced, while in the spreadsheet, with commands like =september sales, or = sum(sales), or =sum(september). My problem now is to reference those same same named cells or groups of cells, from another spreadsheet. While I can refer to a cell with formulas like =='K:\DistPlan\2008crs\Costs 2008.xls'!$G$9, I would like to be able to refer to them with a formula something like ='K:\DistPlan\2008crs\Costs 2008.xls'!September Sales. I have not been able to find a tutorial on how to accomplish this either online or in either of the Excel books we have. As always, I don't think I am the first person who would do this, and so I belive there must be a way. Again, I would be very grateful for anyone's help on this matter. Thanks -- Bill Wehrmacher "Wehrmacher" wrote: Some time ago, with help from this group, I learned how to use named references, with names contained in worksheet cells. (=indirect (A1) or =indirect(NamedCell) etc.) I now wish to use this general technicique to create a reference across spreadsheets. For example, I have the link ='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN This works just fine. However, I want to retrieve cells based on an indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN etc, essentially replacing the text 'FebruaryTCN' with something like indirect(A1) or concatenate(A1,"TCN"). The references will be computed based on other information in the sheet containing the computed indirect cell references. Again, any help will be greatly appreciated. -- Bill Wehrmacher |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
computed indirect cell refererces across workbooks
I would venture to say that your main problem is your *inconsistency* in WB
to WB configuration. If you could construct a template, and then create all the WBs from that template, what I hear you saying here, would not exist as any sort of a problem. If you have numerous WBs already in existence with "non-standard" configurations, what I'm saying here is too little, too late!<g BTW, are you using the intersection operator within your individual WBs? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wehrmacher" wrote in message ... Hi, Sorry about the long delay in replying to your post. Let me try to be a little more clear. Let us assume that one has two spreadsheets, workbook#1, and workbook#2. Each of these sheets have rows and columns named. With the previous example, one of the two spreadsheeds (workbook#1)assigns name "September Sales" to G9 because G9 resides at the intersection of a column named September, and a row named Sales. Adding rows or columns may move the intersection to some other cell, but within that workbook, =September Sales will return the information desired regardless of the actual cell (G9, H13,etc... ) So, I would like to be able to extract the September Sales value from workbook #1 and put it into workbook #2. The problem I have is that I can not find some function that will extract "workbook#1!September Sales" and put it into a cell in workbook#2. I can explicitly name G9 as September_Sales in workbook#1, then I can reach out from workbook#2 and get workbook#1!September_Sales. However, I don't know a way to label what could be hundreds or thousands of individual cells in workbook#1 without going through them one at a time and using a insertNameDefine... process. The plot thickens. Suppose, I want to compute the name of the cell I want to retrieve in workbook#1 by looking at certain row and column names in workbook#2. Indirect references work just fine within a workbook, but I don't know how to use a cell name computed in workbook#2 to reference a named cell in workbook#1. For example, workbook#2 may also have a row named Sales and a column named September, and I might want to put the September Sales value from workbook#1 into the September Sales cell in workbook#2. Sorry to be so long winded, but the inability to reliably link workbooks together without having several workbooks crash every time one workbook is altered is getting to be a real problem. Again, thanks for your thoughts on the subject. Best of everything Bill -- Bill Wehrmacher "RagDyer" wrote: You seem to understand the naming procedures in XL, so I don't understand your problem. In your example, if you assign the name "September Sales" to G9, you can access it, or make reference to it, by that name. What am I missing in your question? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wehrmacher" wrote in message ... Hi all, Unfortunately, this question has been greeted with deafining silence. So, I thought I would try again with a slightly different tack, and perhaps a different problem. I have two spreadsheets. One spreadsheet has a number of cells that can be referenced, while in the spreadsheet, with commands like =september sales, or = sum(sales), or =sum(september). My problem now is to reference those same same named cells or groups of cells, from another spreadsheet. While I can refer to a cell with formulas like =='K:\DistPlan\2008crs\Costs 2008.xls'!$G$9, I would like to be able to refer to them with a formula something like ='K:\DistPlan\2008crs\Costs 2008.xls'!September Sales. I have not been able to find a tutorial on how to accomplish this either online or in either of the Excel books we have. As always, I don't think I am the first person who would do this, and so I belive there must be a way. Again, I would be very grateful for anyone's help on this matter. Thanks -- Bill Wehrmacher "Wehrmacher" wrote: Some time ago, with help from this group, I learned how to use named references, with names contained in worksheet cells. (=indirect (A1) or =indirect(NamedCell) etc.) I now wish to use this general technicique to create a reference across spreadsheets. For example, I have the link ='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN This works just fine. However, I want to retrieve cells based on an indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN etc, essentially replacing the text 'FebruaryTCN' with something like indirect(A1) or concatenate(A1,"TCN"). The references will be computed based on other information in the sheet containing the computed indirect cell references. Again, any help will be greatly appreciated. -- Bill Wehrmacher |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
computed indirect cell refererces across workbooks
You are absolutely correct about our workbooks. They do suffer from
considerable "terra non-firma". I would guess my situation is not unusual in business in that workbooks are maintained by various people without much concern about the impact on others workbooks when they modify theirs. As for the intersection operator. Put name rows in column A for example: RowFirst, RowSecond, RowAnother.... Name Columnsin row 1 for example: ColFirst, ColSecond, ColAnother... A B C D 1 ColFirst ColSecond ColAnother 2 RowFirst 12 43 .016 3 RowSecond 55 12 0.25 4 RowAnother 20 8 0.86 Select the array and from the menu insertnamecreate and check TopRow and Left Column. The formula =RowSecond ColFirst (there is a space between RowSecond and ColFirst) will return the value at the intersection of that row column (55). This would initially be the same as =B3. However, if someone were to add a row between 2 and 3, then =RowSecond ColFirst would return the value at B4. One can alternatively write the expression =indirect(A2) indirect(D1) with tells XL to retrieve the value at RowFirst ColAnother or .016. The advantage to using indirect addressing is that one can copy and paste that form into large arrays that may do arithmatic based on the contents of cells in several other arrays. I often do this beacuse in my line of work, I have costs and quantities over months and I wish to compute average unit costs per month etc. Sorry, long winded again. -- Bill Wehrmacher "RagDyer" wrote: I would venture to say that your main problem is your *inconsistency* in WB to WB configuration. If you could construct a template, and then create all the WBs from that template, what I hear you saying here, would not exist as any sort of a problem. If you have numerous WBs already in existence with "non-standard" configurations, what I'm saying here is too little, too late!<g BTW, are you using the intersection operator within your individual WBs? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wehrmacher" wrote in message ... Hi, Sorry about the long delay in replying to your post. Let me try to be a little more clear. Let us assume that one has two spreadsheets, workbook#1, and workbook#2. Each of these sheets have rows and columns named. With the previous example, one of the two spreadsheeds (workbook#1)assigns name "September Sales" to G9 because G9 resides at the intersection of a column named September, and a row named Sales. Adding rows or columns may move the intersection to some other cell, but within that workbook, =September Sales will return the information desired regardless of the actual cell (G9, H13,etc... ) So, I would like to be able to extract the September Sales value from workbook #1 and put it into workbook #2. The problem I have is that I can not find some function that will extract "workbook#1!September Sales" and put it into a cell in workbook#2. I can explicitly name G9 as September_Sales in workbook#1, then I can reach out from workbook#2 and get workbook#1!September_Sales. However, I don't know a way to label what could be hundreds or thousands of individual cells in workbook#1 without going through them one at a time and using a insertNameDefine... process. The plot thickens. Suppose, I want to compute the name of the cell I want to retrieve in workbook#1 by looking at certain row and column names in workbook#2. Indirect references work just fine within a workbook, but I don't know how to use a cell name computed in workbook#2 to reference a named cell in workbook#1. For example, workbook#2 may also have a row named Sales and a column named September, and I might want to put the September Sales value from workbook#1 into the September Sales cell in workbook#2. Sorry to be so long winded, but the inability to reliably link workbooks together without having several workbooks crash every time one workbook is altered is getting to be a real problem. Again, thanks for your thoughts on the subject. Best of everything Bill -- Bill Wehrmacher "RagDyer" wrote: You seem to understand the naming procedures in XL, so I don't understand your problem. In your example, if you assign the name "September Sales" to G9, you can access it, or make reference to it, by that name. What am I missing in your question? -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Wehrmacher" wrote in message ... Hi all, Unfortunately, this question has been greeted with deafining silence. So, I thought I would try again with a slightly different tack, and perhaps a different problem. I have two spreadsheets. One spreadsheet has a number of cells that can be referenced, while in the spreadsheet, with commands like =september sales, or = sum(sales), or =sum(september). My problem now is to reference those same same named cells or groups of cells, from another spreadsheet. While I can refer to a cell with formulas like =='K:\DistPlan\2008crs\Costs 2008.xls'!$G$9, I would like to be able to refer to them with a formula something like ='K:\DistPlan\2008crs\Costs 2008.xls'!September Sales. I have not been able to find a tutorial on how to accomplish this either online or in either of the Excel books we have. As always, I don't think I am the first person who would do this, and so I belive there must be a way. Again, I would be very grateful for anyone's help on this matter. Thanks -- Bill Wehrmacher "Wehrmacher" wrote: Some time ago, with help from this group, I learned how to use named references, with names contained in worksheet cells. (=indirect (A1) or =indirect(NamedCell) etc.) I now wish to use this general technicique to create a reference across spreadsheets. For example, I have the link ='K:\DistPlan\2008crs\Costs 2008.xls'!FebruaryTCN This works just fine. However, I want to retrieve cells based on an indirect referenct to the equivalent of FebruaryTCN... MarchTCN... AprilTCN etc, essentially replacing the text 'FebruaryTCN' with something like indirect(A1) or concatenate(A1,"TCN"). The references will be computed based on other information in the sheet containing the computed indirect cell references. Again, any help will be greatly appreciated. -- Bill Wehrmacher |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
computed named cell references | New Users to Excel | |||
INDIRECT should be updated to work on closed workbooks. | Excel Worksheet Functions | |||
cell values being computed | Excel Worksheet Functions | |||
How delete formula bar and retain the computed number in a cell? | Excel Worksheet Functions | |||
Using INDIRECT to refer to different workbooks | Excel Worksheet Functions |