+ Reply to Thread
Results 1 to 9 of 9

*SumIF HELP!*

  1. #1
    Registered User
    Join Date
    02-11-2005
    Posts
    7

    *SumIF HELP!*

    I am trying to make a sumif formula look up a value of a cell within the formula and is not working for me. I need one worksheet to reference over 250 other workbooks for specific values. The formula is always the same except the workbook name changes. The formula I have is as follows and want to have the workbook name in bold a reference to another cell as I already have a list of all the excel workbook names:

    =SUM(IF(('http://mywebsite/[ReferencedWorkbook.xls]Topic List'!$N$4:$N$299="Low")*('http://mywebsite/[ReferencedWorkbook.xls]Topic List'!$J$4:$J$299="Beta 2"),1,0))

    I have the list of the workbook names and would like the create the formula and drag it down. So the title in bold would look at cell A1 to find the workbook name instead of me having to manually type all that stuff in.

    Thanks in advance

  2. #2
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    Try...

    =SUM(IF((INDIRECT("'http://mywebsite/["&A1&"]Topic List'!$N$4:$N$299")="Low")*(INDIRECT("'http://mywebsite/["&A1&"]Topic List'!$J$4:$J$299")="Beta 2"),1,0))

    ...confirmed with CONTROL+SHIFT+ENTER. The problem with this is that your workbooks must be opened. But if you download Morefunc.xll , a free add-in, you can use INDIRECT.EXT which should work when workbooks are closed. If you have problems with this function, you can always try the PULL function. For the PULL function, search Google.

    Hope this helps!

  3. #3
    Registered User
    Join Date
    02-11-2005
    Posts
    7
    thanks for the reply but I need this to work with workbooks that arent open. Thats the way the users want it. There has got to be an easy way to pass the variable in there.

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    If you download Morefunc, as I've already mentioned, you'll be able to use the INDIRECT.EXT function, which works with closed workbooks.

  5. #5
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Here is a summary of how to reference external cells:
    http://www.*****-blog.com/archives/2...sed-workbooks/
    Frank Kabel (th author) was a very famous Excel-experts, before he passed on.

    I you want to tie this action to a specific Worksheet I think - as Domenic mentioned - Harlan Groves Pull function is the best bet (see above)

    Another Option is to use the Pivottable. It can also pull data from closed worksheet - but it depends on the situation, if it is practical.

    Hope it's something
    Ola Sandström


    Note:
    Morefunc. is a very good add-in (I use it myself) - if you want the functions yourself.
    http://xcell05.free.fr/ (select English and it's the second picture)

  6. #6
    Registered User
    Join Date
    02-11-2005
    Posts
    7
    if i download the excel add in and use the formula above, if this is copied to local machines will the functionality follow the workbook or does every machine need that add in?

  7. #7
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    Generally, One download(add-in)/machine. However maybe a server solution is possible, but I could be dead wrong!
    You can ask in three places unless someone here know (maybe the Programming forum):
    Laurent (Morefunc homepage) has a Forum page. Ask him (click the Forum button on the left side)
    Daily Dose of Excel: see above link
    And at Microsoft: http://support.microsoft.com/newsgro...ons&sloc=en-us
    or http://support.microsoft.com/newsgroups/

    But the Pull function - that goes with the Worksheet - sound like the best solution if your in a hurry (even though I have not used it).


    Ola Sandström
    Last edited by olasa; 05-09-2005 at 05:33 PM.

  8. #8
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474
    I just took a look at the website referred to by Ola regarding INDIRECT.EXT. It seems that it can only return a single cell reference. So I don't think that this function is going to help you. Maybe Ola can confirm whether this is true.

  9. #9
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256
    This conversation should be of interest (I just saw it): http://www.excelforum.com/showthread...hreadid=369328

    And I think your right Domenic. I can't get Internal.ext to work on arrays.

    Ola Sandström
    (he ;-)

+ 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