Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using SUM, ADDRESS, INDIRECT in an array not working
Hi,
Here's the problem (I simplified the formula a little to make it easier to understand.) =SUM(IF(AreaLookup_RD=1,INDIRECT("Area"&AreaLookup &"!B16"),0)) I am trying to add values in different worksheets if they are assigned to an area #. I want the values in: Area1!B16 (assigned to Region 1) Area2!B16 (assigned to Region 1) Area3!B16 (assigned to Region 2) Area4!B16 (assigned to Region 2) Area5!B16 (assigned to Region 3) Area6!B16 (assigned to Region 3) Area7!B16 (assigned to Region 3) So I have a lookup table: Column 1 = Region # Column 2 = Area # The formula above uses the lookup table to figure out which Area #'s to sum up. For some reason Excel is only doing the Array on the first worksheet and not doing the Array in the INDIRECT function. So it basically adds up a bunch of Area1!B16 instead of using the correct Area # to go to the right worksheet I also tried rewriting it to: =SUM(IF(Lookup!$I2:$I$30=1,"=INDIRECT("&ADDRESS(16 ,2,1,TRUE,"Area"&Lookup!$J$2:$J$30)&")",0)) - and - =SUM(IF(Lookup!$I2:$I$30=1,ADDRESS(16,2,1,TRUE,"Ar ea"&Lookup!$J$2:$J$30),0)) Any help would be greatly appreciated! Thanks, Bradley |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Allow EXCEL INDIRECT(ADDRESS()) and INDIRECT(RANGE()) functions | Excel Worksheet Functions | |||
indirect with address list | Excel Worksheet Functions | |||
using INDIRECT(ADDRESS(...)) | Excel Discussion (Misc queries) | |||
Indirect(Address(... | Excel Discussion (Misc queries) | |||
Array reference using indirect address | Excel Worksheet Functions |