Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect with address list
I tried searching, but didn't find my solution so:
I have a series of formulas that operate on a list of cells, but the list is variable. I thought to replace all references to the cell list with indirect, but it appears to fail on an address list. For simplicity, I'll use only the average function as example rather than multiple formulas: Cell I45 contains =average(I30,I37,I44) There is an empty sell at H46, so I tried: H46: I27,I34,I41 I45: =average(indirect(H46)) which fails. Is there a way to accomplish this? The reason is (for instance) when the list is I27,I34 instead of I27,I34,I41 I don't wish to change several formulas. Thanks in advance! --Clif McIrvin |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect with address list
Here is a neater trick than INDIRECT()!
1. Select a pile of cells (click the first one and CNTRL-click the rest) 2. Insert Name Define clif 3. Then use: =AVERAGE(clif) You can use clif across many formulas. You only need to change the Defined Name from the menu rather than change all the cells that use it. -- Gary''s Student - gsnu2007f "Clif McIrvin" wrote: I tried searching, but didn't find my solution so: I have a series of formulas that operate on a list of cells, but the list is variable. I thought to replace all references to the cell list with indirect, but it appears to fail on an address list. For simplicity, I'll use only the average function as example rather than multiple formulas: Cell I45 contains =average(I30,I37,I44) There is an empty sell at H46, so I tried: H46: I27,I34,I41 I45: =average(indirect(H46)) which fails. Is there a way to accomplish this? The reason is (for instance) when the list is I27,I34 instead of I27,I34,I41 I don't wish to change several formulas. Thanks in advance! --Clif McIrvin |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect with address list
On Mar 11, 2:42*pm, Gary''s Student
wrote: Here is a neater trick than INDIRECT()! 1. Select a pile of cells (click the first one and CNTRL-click the rest) 2. Insert Name Define clif 3. Then use: * * =AVERAGE(clif) You can use clif across many formulas. *You only need to change the Defined Name from the menu rather than change all the cells that use it. -- Gary''s Student - gsnu2007f But, the plot thickens. I debated how much detail to include, I see I left out a critical bit: The workbook contains a pile of sheets of identical format, and this situation exists on each sheet (in multiple workbooks.) this "named range" must be able to be different on each sheet. In the meanwhile, I have devised a work-around utilizing space "off to the side" (that is, outside of the print view.) In column M I have created a stack of formulas, for instance: M42: =I30 M43: =I37 M44: =I44 M45: M42:M44 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect with address list
On Mar 11, 3:01*pm, Clif McIrvin wrote:
On Mar 11, 2:42*pm, Gary''s Student wrote: Here is a neater trick than INDIRECT()! 1. Select a pile of cells (click the first one and CNTRL-click the rest) 2. Insert Name Define clif 3. Then use: * * =AVERAGE(clif) You can use clif across many formulas. *You only need to change the Defined Name from the menu rather than change all the cells that use it. -- Gary''s Student - gsnu2007f But, the plot thickens. *I debated how much detail to include, I see I left out a critical bit: The workbook contains a pile of sheets of identical format, and this situation exists on each sheet (in multiple workbooks.) this "named range" must be able to be different on each sheet. In the meanwhile, I have devised a work-around utilizing space "off to the side" (that is, outside of the print view.) In column M I have created a stack of formulas, for instance: M42: * =I30 M43: * =I37 M44: * =I44 M45: * M42:M44 aarggh! Fat-finger error! :( and now I45: =average(indirect(M45)) works as I expect it to. When the data on a new sheet has different requirements, I can simply change M45 to, say, M42:M43 and the formulas behave as I expect. (I am dealing with test data, and each sheet represents a different sample. The more I learn about what Excel (and Access) can do the more I believe I would have set things up differently, but - alas! - this is an inherited system, so for now, at least, I'm stuck with the layout I have.) --Clif |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect with address list
Your workaround is a good one. Many functions have problems handling a
disjoint collects of cells; COUNTIF() is one example. A perfectly valid solution to to removed "disjointness" by creating a nice, compact block of linked cells and let the fussy function operate on them instead. -- Gary''s Student - gsnu200773 "Clif McIrvin" wrote: On Mar 11, 2:42 pm, Gary''s Student wrote: Here is a neater trick than INDIRECT()! 1. Select a pile of cells (click the first one and CNTRL-click the rest) 2. Insert Name Define clif 3. Then use: =AVERAGE(clif) You can use clif across many formulas. You only need to change the Defined Name from the menu rather than change all the cells that use it. -- Gary''s Student - gsnu2007f But, the plot thickens. I debated how much detail to include, I see I left out a critical bit: The workbook contains a pile of sheets of identical format, and this situation exists on each sheet (in multiple workbooks.) this "named range" must be able to be different on each sheet. In the meanwhile, I have devised a work-around utilizing space "off to the side" (that is, outside of the print view.) In column M I have created a stack of formulas, for instance: M42: =I30 M43: =I37 M44: =I44 M45: M42:M44 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect with address list
On Mar 11, 3:17*pm, Gary''s Student
wrote: Your workaround is a good one. *Many functions have problems handling a disjoint collects of cells; COUNTIF() is one example. A perfectly valid solution to to removed "disjointness" by creating a nice, compact block of linked cells and let the fussy function operate on them instead. Thanks! that feels good! I've noticed your sig under good solutions before --- Thanks for your helpfulness! -- Clif |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
using INDIRECT(ADDRESS(...)) | Excel Discussion (Misc queries) | |||
Indirect(Address(... | Excel Discussion (Misc queries) | |||
INDIRECT(ADDRESS... Across worksheets | Excel Worksheet Functions | |||
Indirect and Address in Reference to other sheets | Excel Worksheet Functions |