+ Reply to Thread
Results 1 to 11 of 11

[SOLVED] Trouble with a VLOOKUP formula

  1. #1
    WhytheQ
    Guest

    [SOLVED] Trouble with a VLOOKUP formula

    Originally the formula was:
    =VLOOKUP(A3,'I:\Fld\[Fil 01 01 06.xls]Pricing'!$A$1:$D$30,4,FALSE)

    But the date in the file name can change regularly and there are loads
    of formula like the above, so I thought to be able to change a date in
    single cell C1 and the formulas to dynamically change would be good.
    I've tried the 2 below formula without any success:

    1.=VLOOKUP(A3,'I:\Fld\[Fil " & C1 &".xls]Pricing'!$A$1:$D$30,4,FALSE)
    2.=VLOOKUP(A3,'I:\Fld\[Fil " & INDIRECT(C1)
    &".xls]Pricing'!$A$1:$D$30,4,FALSE)

    Any one got any ideas??
    Help greatly appreciated.

    Jason


  2. #2
    Vass
    Guest

    Re: Trouble with a VLOOKUP formula

    "WhytheQ" <[email protected]> wrote in message
    news:[email protected]...
    > Originally the formula was:
    > =VLOOKUP(A3,'I:\Fld\[Fil 01 01 06.xls]Pricing'!$A$1:$D$30,4,FALSE)
    >
    > But the date in the file name can change regularly and there are loads
    > of formula like the above, so I thought to be able to change a date in
    > single cell C1 and the formulas to dynamically change would be good.
    > I've tried the 2 below formula without any success:
    >
    > 1.=VLOOKUP(A3,'I:\Fld\[Fil " & C1 &".xls]Pricing'!$A$1:$D$30,4,FALSE)


    the concantenating &'s sit outside the "quotes" as will the cell reference
    +C1



  3. #3
    WhytheQ
    Guest

    Re: Trouble with a VLOOKUP formula

    Thanks for the help Vass.
    Where do the "quotes" go then? If I do the below, and just get rid of
    them altogether, then things still don't work!

    i.e.=VLOOKUP(A3,'I:\Fld\[Fil & C1 & .xls]Pricing'!A1:D30,4,FALSE)

    ........also if I just put the &'s outside the quotes, like below, then
    things are still not working:

    i.e.=VLOOKUP(A3,'I:\Fld\[Fil & "C1" & .xls]Pricing'!A1:D30,4,FALSE)

    I don't find setting this formula up intuitive at all, so any more help
    would be much appreciated.
    Thanks
    Jason


  4. #4
    Pete_UK
    Guest

    Re: Trouble with a VLOOKUP formula

    I think what you want is:

    ..=VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&C1&".xls]Pricing'!A1:D30"),4,FALSE)

    Basically, the inner part of the formula is built up as

    "'I:\Fld\[Fil" & C1 & ".xls]Pricing'!A1:D30"

    i.e. you are joining three strings together - the first and last are
    literal strings so must be enclosed in quotes, whereas the middle
    string is what is contained in cell C1. The INDIRECT( ) function allows
    you to combine addresses in this way.

    Hope this helps.

    Pete


  5. #5
    Pete_UK
    Guest

    Re: Trouble with a VLOOKUP formula

    Actually, instead of just C1 I think you will need to convert this into
    a specific format using the TEXT( ) function, as follows:

    =VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&TEXT(C1,"dd mm
    yy")&".xls]Pricing'!A1:D30"),4,FALSE)

    It's not clear if you use "dd mm yy" or "mm dd yy" format in your
    filenames - adjust to suit.

    Hope this helps.

    Pete


  6. #6
    Dave Peterson
    Guest

    Re: Trouble with a VLOOKUP formula

    And =indirect() won't work if that other workbook is closed.

    And if the workbook is open, the drive\folder stuff could be dropped.

    Pete_UK wrote:
    >
    > I think what you want is:
    >
    > .=VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&C1&".xls]Pricing'!A1:D30"),4,FALSE)
    >
    > Basically, the inner part of the formula is built up as
    >
    > "'I:\Fld\[Fil" & C1 & ".xls]Pricing'!A1:D30"
    >
    > i.e. you are joining three strings together - the first and last are
    > literal strings so must be enclosed in quotes, whereas the middle
    > string is what is contained in cell C1. The INDIRECT( ) function allows
    > you to combine addresses in this way.
    >
    > Hope this helps.
    >
    > Pete


    --

    Dave Peterson

  7. #7
    Pete_UK
    Guest

    Re: Trouble with a VLOOKUP formula

    Good points, Dave. I forgot!

    Pete


  8. #8
    WhytheQ
    Guest

    Re: Trouble with a VLOOKUP formula

    thanks for all the help lads.....even though Dave had to go and "p**s
    on my fire" a bit at the end there !!
    the other workbooks will be shut, so atleast I know that I need to find
    an alternative route - think I'll muck around with a bit of VBA

    thanks again
    Jason


  9. #9
    Dave Peterson
    Guest

    Re: Trouble with a VLOOKUP formula

    You could use a helper worksheet and a macro that populates that sheets with
    formulas that point at that closed workbook--rebuild the formulas when the
    workbook name would change.

    Then use your =vlookup() against that helper sheet's range.

    WhytheQ wrote:
    >
    > thanks for all the help lads.....even though Dave had to go and "p**s
    > on my fire" a bit at the end there !!
    > the other workbooks will be shut, so atleast I know that I need to find
    > an alternative route - think I'll muck around with a bit of VBA
    >
    > thanks again
    > Jason


    --

    Dave Peterson

  10. #10
    Forum Contributor
    Join Date
    09-25-2004
    Posts
    269
    You could try a worksheet event code.
    You said the date is in cell C1 so you save your files by date. Now right click at the bottom of the worksheet tab go to view code then paste this code in the white area. Now anytime you enter the file name in cell C1 it will open and close that file but you will still retain your vaules. Try it out see if it works for you. But you need to have the INDIRECT formula.

    =VLOOKUP(A3,INDIRECT("'I:\Fld\[Fil"&C1&".xls]Pricing'!A1:D30"),4,FALSE)


    Please Login or Register  to view this content.
    Last edited by vane0326; 03-16-2006 at 11:07 PM.

  11. #11
    Registered User
    Join Date
    10-18-2016
    Location
    UK
    MS-Off Ver
    2013
    Posts
    1

    Re: [SOLVED] Trouble with a VLOOKUP formula

    I know this is very old and I am dragging it up from the dead but would like to implement the above code but in Excel 2013...

    Here's what I have in the MAcro

    Please Login or Register  to view this content.
    However Application.FileSearch is a dead runner now in 2013. What is the alternative does anyone know?

    Cheers

+ 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