Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect function within sumif to reference other sheets
Hi everyone,
I have a workbook with many sheets, most of them being financial information, ie last year actual / budget, this year actual/budget etc. I then have a sheet which I am making a report on to summarise all of the other information. Each line is a sumif of all the relevant cost / income types. Ie office expenses would be a sumif on all the expense classified as office expenses. What i want (need) to do is have a drop down box where users can select the data they want the report to be populated with. Ie they can select last years budget numbers and all the sum if formulae will be calculated off last years budget sheet. I think the way to do this is with the indirect formula within the sum if, but I can't seem to get the syntax right. Normally I would just have =sumif(bud0506!$g$g,sheet1$a$1,bud0506!$i$i) so I want the sheet to be dynamic, so i have a drop down menu linked to cell a2, so cell a2 would have bud0506 in it, so i then would have =sumif(indirect($a$2&$g$g),sheet1$a$1,indirect($a$ 2&$i$i) But I just can't seem to get the syntax correct, any help would be greatly appreciated. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect function within sumif to reference other sheets
=SUMIF(INDIRECT("'"&$a$2&"'!G:G"),Sheet1!$A$1,INDI RECT("'"&$a$2&"'!I:I"))
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message ps.com... Hi everyone, I have a workbook with many sheets, most of them being financial information, ie last year actual / budget, this year actual/budget etc. I then have a sheet which I am making a report on to summarise all of the other information. Each line is a sumif of all the relevant cost / income types. Ie office expenses would be a sumif on all the expense classified as office expenses. What i want (need) to do is have a drop down box where users can select the data they want the report to be populated with. Ie they can select last years budget numbers and all the sum if formulae will be calculated off last years budget sheet. I think the way to do this is with the indirect formula within the sum if, but I can't seem to get the syntax right. Normally I would just have =sumif(bud0506!$g$g,sheet1$a$1,bud0506!$i$i) so I want the sheet to be dynamic, so i have a drop down menu linked to cell a2, so cell a2 would have bud0506 in it, so i then would have =sumif(indirect($a$2&$g$g),sheet1$a$1,indirect($a$ 2&$i$i) But I just can't seem to get the syntax correct, any help would be greatly appreciated. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect function within sumif to reference other sheets
Thanks Bob that works - although you have to pay very close attention
to the syntax - the " ' " stuff is really easy to get wrong I found. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
indirect function within sumif to reference other sheets
I know, that's why I did.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) wrote in message oups.com... Thanks Bob that works - although you have to pay very close attention to the syntax - the " ' " stuff is really easy to get wrong I found. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with Indirect function ? | Excel Worksheet Functions | |||
SumIF function | Excel Discussion (Misc queries) | |||
Indirect and Address in Reference to other sheets | Excel Worksheet Functions | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Can I use a cell reference in the criteria for the sumif function. | Excel Worksheet Functions |