+ Reply to Thread
Results 1 to 4 of 4

indirect function within sumif to reference other sheets

  1. #1

    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. #2
    Bob Phillips
    Guest

    Re: indirect function within sumif to reference other sheets

    =SUMIF(INDIRECT("'"&$a$2&"'!G:G"),Sheet1!$A$1,INDIRECT("'"&$a$2&"'!I:I"))


    --

    HTH

    Bob Phillips

    (replace xxxx in the email address with gmail if mailing direct)

    <[email protected]> wrote in message
    news:[email protected]...
    > 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. #3

    Re: 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. #4
    Bob Phillips
    Guest

    Re: 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)

    <[email protected]> wrote in message
    news:[email protected]...
    > 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 to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1