Posts by skye9

skye9 · Jan 30th 2008

Re: Multiple Criteria Report

PCI,
absolutely brilliant!!! :party:

THANKS YOU SO MUCH !!!!!!

skye9 · Jan 30th 2008

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)

skye9 · Jan 29th 2008

Re: Multiple Criteria Report

StephenR,
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!

skye9 · Jan 28th 2008

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.

skye9 · Jan 28th 2008

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:

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:

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)

skye9 · Dec 10th 2006

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:

:cheers:

skye9 · Dec 9th 2006

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.

skye9 · Dec 9th 2006

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: http://www.7-zip.org/)

skye9 · Dec 9th 2006

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

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("C2")) Is Nothing Or Target.Cells.Count > 1 Then Exit Sub
    ActiveSheet.EnableCalculation = True
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationAutomatic
     
    With Application.FileSearch
        .NewSearch
        .LookIn = "C:\"
        .Filename = [A2] & ".XLS"
         
        If .Execute(msoSortByFileName, msoSortOrderDescending, True) > 0 Then
            Workbooks.OpenText .FoundFiles(1), xlWindows
            ActiveWorkbook.Close
             
        End If
    End With
     
    ActiveSheet.EnableCalculation = False
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
     
End Sub

Display More

skye9 · Dec 8th 2006

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

skye9 · Dec 7th 2006

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:

Code

=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))))

skye9 · Dec 7th 2006

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

:cheers:

skye9 · Dec 5th 2006

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.

Code

=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: http://groups.google.com/group…&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?

Code

Dim xlapp As Object, xlwb As Workbook 
  Dim b As String, r As Range, C As Range, n As Long 


  '** begin 2004-05-30 changes ** 
  '** begin 2004-05-28 changes ** 
  '** begin 2004-03-25 changes ** 
  n = InStrRev(xref, "\") 


  If n > 0 Then 
    If Mid(xref, n, 2) = "\[" Then 
      b = Left(xref, n) 
      n = InStr(n + 2, xref, "]") - n - 2 
      If n > 0 Then b = b & Mid(xref, Len(b) + 2, n) 


    Else 
      n = InStrRev(Len(xref), xref, "!") 
      If n > 0 Then b = Left(xref, n - 1) 


    End If 


    '** key 2004-05-28 addition ** 
    If Left(b, 1) = "'" Then b = Mid(b, 2) 


    On Error Resume Next 
    If n > 0 Then If Dir(b) = "" Then n = 0 
    Err.Clear 
    On Error GoTo 0 


  End If 


  If n <= 0 Then 
    pull = CVErr(xlErrRef) 
    Exit Function 
  End If 
  '** end 2004-03-25 changes ** 
  '** end 2004-05-28 changes ** 


  pull = Evaluate(xref) 


  '** key 2004-05-30 addition ** 
  If IsArray(pull) Then Exit Function 
  '** end 2004-05-30 changes ** 


  If CStr(pull) = CStr(CVErr(xlErrRef)) Then 
    On Error GoTo CleanUp   'immediate clean-up at this point 


    Set xlapp = CreateObject("Excel.Application") 
    Set xlwb = xlapp.Workbooks.Add  'needed by .ExecuteExcel4Macro 


    On Error Resume Next    'now clean-up can wait 


    n = InStr(InStr(1, xref, "]") + 1, xref, "!") 
    b = Mid(xref, 1, n) 


    Set r = xlwb.Sheets(1).Range(Mid(xref, n + 1)) 


    If r Is Nothing Then 
      pull = xlapp.ExecuteExcel4Macro(xref) 


    Else 
      For Each C In r 
        C.Value = xlapp.ExecuteExcel4Macro(b & C.Address(1, 1, xlR1C1)) 
      Next C 


      pull = r.Value 


    End If 


CleanUp: 
    If Not xlwb Is Nothing Then xlwb.Close 0 
    If Not xlapp Is Nothing Then xlapp.Quit 
    Set xlapp = Nothing 


  End If 


End Function 
'-----  end VBA  -----

Display More

skye9 · Dec 4th 2006

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.
Thanks

skye9 · Dec 1st 2006

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!

skye9 · Dec 1st 2006

Re: Formula Stops Working After Performing Certain Tasks

the dates would be exact matches

skye9 · Dec 1st 2006

Re: Formula Stops Working After Performing Certain Tasks

Domenic,
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.

Code

=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

skye9 · Nov 29th 2006

Re: Formula Stops Working After Performing Certain Tasks

Thank you all for responding.

Dave,

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:

skye9 · Nov 28th 2006

Re: Formula Stops Working After Performing Certain Tasks

Ok,
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:

Code

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

Can you perhaps help out with that?

Thanks a lot.

skye9 · Nov 28th 2006

Re: Formula Stops Working After Performing Certain Tasks

Domenic,
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.