Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with Indirect function ? Richard Buttrey Excel Worksheet Functions 4 May 24th 06 11:27 PM
SumIF function ACDenver Excel Discussion (Misc queries) 2 August 17th 05 09:47 PM
Indirect and Address in Reference to other sheets MikeDH Excel Worksheet Functions 0 August 11th 05 09:53 PM
referencing named formula using INDIRECT function [email protected] Excel Worksheet Functions 19 May 11th 05 09:48 AM
Can I use a cell reference in the criteria for the sumif function. Number Cruncher Excel Worksheet Functions 2 November 4th 04 08:52 PM


All times are GMT +1. The time now is 06:38 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"