Posts by skye9

    Re: Multiple Criteria Report

    wow, thanks a lot PCI!

    Advanced Filter, was in fact quite sufficient for this problem (which I over-estimated the complexity of).

    As to your question (for the VBA), yes those entries having a 'Start' but no 'End' entry (or vice-versa) should also be included in the results list. I wouldn't know what to change in the code to allow for this, but if you have a moment and can let me know, that would be great.

    I truly appreciate all your help. Thanks again. :) :)

    (btw StephenR, the data format was not my choice but is what I have to work with)

    Re: Multiple Criteria Report

    the mouse-hovering feature is obvious, but the tutorials they point to are not suitable for this particular situation, though if it's that easy then can you please post an actual example of their application - to filter the list by the criteria I mentioned in my first post using whichever of your suggested approaches.

    As far as I know, this problem requires a VBA or formula solution, but I'm open to whatever will truly work. Thanks a lot!

    Re: 3-criteria Query

    thanks jhenderson,
    but I don't think you understood the nature of the problem. I can't see how you could solve it with a simple filter (which filter?) or pivot table, though I'm very curious to learn. Can you please show me?

    I think only a VBA or formula solution would apply here.

    I have a 7-column 'Task' list that I need to query, and extract (preferably to a separate sheet) only those tasks that start 'ON' or 'BEFORE' the queried Date (or date range), AND/OR end 'ON' or 'AFTER' that date.

    Column 1 = Dates
    Columns 2-4 = Task IDs (ID1, ID2, ID3 - must match as a group)
    Column 5 = indicates either Start or End of Task ('S' or 'E')
    (each Task has 2 such listings - a Start [S] and an End [E])
    columns 6 & 7 = Misc. & Notes (unimportant as identifiers)

    So, if a Task (identified collectively by col. 2-4) starts On or Before and ends On or After (col 5) the date (col 1) queried, then that Task should be included in the results list. The tricky thing is that a task that starts long before the queried date and/or ends long after the queried date needs to be included in the results list - therefore 'S' and 'E' (Start/End, col 5) and the 3 Task ID (col. 2-4) must be used along with the date for the query. And, if a task starts (or ends) on the queried date, then its counterpart (S/E) should also be included in the results list (if available).

    Tasks List:

    Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes

    Jan 1 2008 | AA | def | XX | S | B-11 | notes
    Jan 1 2008 | FF | xyz | ZZ | S | C-44 | notes
    Jan 2 2008 | DD | def | YY | E | J-55 | notes
    Jan 2 2008 | GG | abc | CC | S | C-22 | notes
    Jan 2 2008 | BB | xyz | DD | S | M-33 | notes
    Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
    Jan 3 2008 | BB | def | ZZ | E | A-99 | notes
    Jan 4 2008 | GG | abc | CC | E | C-22 | notes
    Jan 5 2008 | AA | def | XX | E | B-11 | notes
    Jan 6 2008 | BB | xyz | FF | S | J-55 | notes
    Jan 6 2008 | DD | abc | AA | S | A-99 | notes
    Jan 7 2008 | ZZ | abc | XX | S | B-11 | notes

    Query Date: Jan 3 2008

    Results List:

    Date | Task-ID1 | Task-ID2 | Task-ID3 | S/E | Misc. | Notes

    Jan 1 2008 | AA | def | XX | S | B-11 | notes
    Jan 4 2008 | AA | def | XX | E | B-11 | notes
    Jan 2 2008 | GG | abc | CC | S | C-22 | notes
    Jan 5 2008 | GG | abc | CC | E | C-22 | notes
    Jan 3 2008 | AA | xyz | CC | S | S-77 | notes
    Jan 3 2008 | BB | def | ZZ | E | A-99 | notes

    (the results order is not really important, as they can be sorted as desired later)

    Re: Lookup In Closed Workbook

    FINALLY WORKS!!! : D : D

    Fin Fan Foom, that’s a really fabulous way around the file cell reference limitation. Thanks for the lesson. Very long formula, but it seems to work great.

    Why do you suggest turning Auto-calc off? Not sure if I should do that since there are other formulas that depend on auto-calc. Btw, the calc function doesn’t seem to respond with either F9 or Ctrl +F9, but that’s not a problem.

    Oh, and Main file size increase is very minor - only 100 KB. :)

    THANK YOU so much for your patience in helping me with this. I really appreciate it. I'm a big fan of yours (and Domenic's too)! :yourock:


    Re: Lookup In Closed Workbook

    Fin Fan Foom, the last formula works just like the other ones.

    The strange thing is with no formula in the cell (or with a " ' " in front of the "=", the workbook drops down in size by about 2MB. But as soon as I put the formula back in (or remove the " ' " and then save, the workbook jumps up by 2MB again, without doing anything else. It’s only that ONE instance which is causing or triggering this. It even does this if I use a simple straight lookup formula. An even stranger thing is that I have no problems with any of my other lookups, which also access closed files.

    So again, the last alterative is to divide the large data file into separate smaller ones (by "Contact" name), but this would require using a cell to reference the particular files.

    I tried substituting [DataFile2.xls] for " & "[" & A2 & ".xls]" but of course, the contents of “A2” does not get recognized and written into the formula. This limitation in Excel makes no sense to me. :confused:

    Anyway, thank you for your patience.

    Re: Lookup In Closed Workbook

    I think you can probably just insert the cell reference into your last code, rather than requiring the one I posted above. The current sample files will work fine for our purposes. Just make A2 the cell reference for the data file(s). Fingers crossed!

    (btw, I use the free 7-zip and IZArc zip utilities - 7-zip has the greatest compression for super-compacting any files:


    Re: Lookup In Closed Workbook

    Good article Dave,
    I already tried those suggestions but it didn't make much difference. There’s one last thing I can try, then I give up.

    I could divide the data files up into separate files to attempt to reduce the accumulating file size of the main file, but then I would need to use a dynamic cell reference to select the particular data file.

    Fin Fan Foom, can the code you gave me a while back to dynamically select files through a cell reference somehow be incorporated here? If so, I' sure that will solve the problem

    Re: Lookup In Closed Workbook

    Thanks a lot, Fin Fan Foom!

    I saw your previous (pre-edited) solution last night, which I thought was quite clever, though a bit complex, but today I see you've edited it and posted a simpler solution, which works great. :)

    But even with this new solution though, my workbook still grew in size by about 2MB (data file is about 5MB). :confused: The only reason I can think of is that the additional bulk is being added to the file size due to the many cell references in the workbook which are all pulling on the data linked to the other file. I guess I'll have to live with that.

    Anyway, I really appreciate all your efforts in helping me with this.

    Thanks again :)

    Re: Lookup In Closed Workbook

    The INDIRECT function stopped working which is what prompted this thread. Perhaps it'll work now with FFF's new formula.

    Here's what I tried, but it needs fixing:

    1. =INDEX(INDIRECT("'C:\[DataFile2.xls]c3'!$C$2:$E$100"),MATCH(D2,INDIRECT("'C:\[DataFile2.xls]C3'!$B$2:$B$100",0),MATCH(C2,INDIRECT("'C:\[DataFile2.xls]C3'!$C$1:$E$1",0))))

    Re: Lookup In Closed Workbook

    Fin Fan Foom,

    THANK YOU, THANK YOU! :yourock:

    I’ve been struggling with this for weeks but I knew it could be done.

    One thing though - can you change the sheet name to a cell reference (ie. B2)?

    I truly appreciate your help


    Re: Choosing Named Ranges For Matches In A Closed Workbook

    Thanks Batman.

    Your formula didn’t work, but this slightly different version does, but only if the DataFile is open and it stops working if the DataFile is closed.

    1. =IF(ISNUMBER(MATCH(D2,Date,0)),INDEX(Contact,MATCH(D2,Date,0)),"")

    Is there any way to replicate the accessing a closed file as in my other wb where it actually works?

    One alternative is this “PULL” function (from Harlan Grove:…&ie=UTF-8&c2coff=1&rnum=7 ) which supposedly allows accessing Data from closed workbooks, but its beyond me.

    Would you know how to incorporate Harlan’s code into my workbook?

    I almost have this working and need just some final bit of help with it.

    Column D = Dates to be used in each of the matches.
    Cell C2 = data-validation cell, for choosing the specific Named Range that contains the data to be returned from the closed wb.

    VBA code automatically opens the closed data file and then closes it after returning the data to the main file.

    Can anyone find the error in the cell formula (or named range formula) that’s preventing this from working correctly? (see atttached files)

    I would appreciate any help whatsoever.


    • QueryFile.xls

      (25.09 kB, downloaded 88 times, last: )
    • DataFile2.xls

      (17.41 kB, downloaded 92 times, last: )

    Re: Formula Stops Working After Performing Certain Tasks

    I'm just trying to salvage a formula that works with the INDIRECT function, but simply it by removing the TIME Match. Everything else is the same. I need it to work just like it does in the 'DatesTimesList' file, but with the Date match only, without the TIME variable.

    So take the Date in cell D2 and find the matching date in the table (in the separate wb) - and return the corresponding 'Contact' data.

    I can't use a straight lookup link to that wb, since for some very strange reason, whenever I attempt to do it that way, the main file grows immensly huge. There's nothing particularly special about the data or its structure, or that I may have set it up incorrectly, as Dave suggested. It's pretty straightforward, as you can see from that file.

    Thanks for your patience!

    Re: Formula Stops Working After Performing Certain Tasks

    this is the original working formula you gave me, in the 'DatesTimesList_2' file (Post #11) - which uses INDIRECT - can you please simplify it and remove the TIME factor, so it only uses the Date for the MATCH.

    1. =IF(ISNUMBER(MATCH(MIN(IF(Date=D2,IF(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),IF(Date=D2,ABS(Time-E2)),0)),INDEX(Contact,MATCH(MIN(IF(Date=D2,IF(ABS(Time-E2)<"0:30:30"+0,ABS(Time-E2)))),IF(Date=D2,ABS(Time-E2)),0)),"")

    Thanks a lot

    Re: Formula Stops Working After Performing Certain Tasks

    Thank you all for responding.


    I can't imagine this being any simpler, or using any other application, but I welcome and value your suggestions.

    If you look at my current sample files in Post #9, you’ll see that the data is in the most basic structure possible: a Date column, Company Name columns, and values at the intersect points. I’ve gotten rid of the array formulas - but, is there an alternative to using the INDIRECT function to access a closed data file?

    What’s perplexing is why in the original, more complex files (in Post #11), the formula & VBA works perfectly to allow access to the closed data file, but the same VBA, with simpler and less demanding formulas and data structure in another file will not work, even considering the reduced complexity of the newer files.

    :confused: :confused:

    Re: Formula Stops Working After Performing Certain Tasks

    but in regards to the previous sample files (MainFile & DataFile) that I attached in Post #9, the only thing that’s different is the formula - the vba code is the same as the other sample where it works.

    So all I need is help with a lookup or Index/Match formula for that file. I tried the following with no luck:

    1. =INDEX(ID1_table,MATCH($D$2,Date1,MATCH($A$2,Dept1)),0)

    Can you perhaps help out with that?

    Thanks a lot.

    Re: Formula Stops Working After Performing Certain Tasks

    there's hardly any data in the DataFile, so you won't notice any difference in file size when directly linking to it. But if you expand the data in the table down to about 20,000 rows and then re-save the MainFile, you'll see that the file size will grow a lot.

    As an example of accessing data in a closed wb, I've attached the original working sample files which you (and Fin Fan Foom) helped me with from the other thread. You'll see that the "DataTimesList_2" file will access the closed "NY_05" data file with no problem.

    But the formula somehow no longer works, probably because the data table is too large. So I've rearranged and simplified the data and lookup parameters as in the previous attachment above (I no longer need the TIME lookup), and am hoping to have this working again with this new arrangement. (Btw, change the filepath in the vba to whichever directory you download the files to)

    Thanks for your help.