VLOOKUP formula inserted ONLY (dynamically) when I insert a
VBA Telemetry pings you when your VBA projects fail
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: VLOOKUP formula inserted ONLY (dynamically) when I insert a

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I have an excel spreadsheet that contains a sheet with a list of items with each item assigned a category and value. In that spreadsheet I have another sheet that does uses VLOOKUP formulas to find the associated category and value when I enter the item name.

    Is there a way to have the VLOOKUP formula inserted ONLY when I insert a value?

    One more question. Is there I way I can export this data into an MS ACCESS database and have the VLOOKUP formula in a query page? At first my sheet works fine, but it is taking longer and longer to calculate, as I now have close to 1400 items. Can the two be linked so that one is updated, the other is updated as well?

    THANKS!

    [ This Message was edited by: Cosmos75 on 2002-04-12 14:50 ]

  2. #2
    New Member
    Join Date
    Mar 2002
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    My suggestion to autofill vlookup formulas would be to go ahead and populate the specific area with the following formula:

    =if(isna( your vlookup formula ),""( your vlookup formula ))

    The formulas will remain blank until you add the reference data.

    Hope this works for you

    -Dave

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks, croweld89. Will try it out.

    Would that be faster than using VBA? Is there anything I can put in the sheet code, by right-clicking the sheet and choosing View Code?

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Ontario
    Posts
    52
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Great idea - will use this myself. I'm sure Cosmos deosn't need my help, but for any newbies, I think there should be a comma after the double quotes in the if stmt.

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    croweld89, Here's what I've used in my spreadsheet which is somewhat like yours.

    =IF(A1="","",IF(ISERROR(VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,FALSE)),"",IF(VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,FALSE)="","",VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,FALSE))))

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    yeah that's cool, but i think you maybe got carried away with the nesting. your deepest level nest is not required, as if the vlookup returns "" then it does any way, you do not need:

    if(FormulaX="","",FormulaX)

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

  7. #7
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-04-12 11:57, Cosmos75 wrote:
    croweld89, Here's what I've used in my spreadsheet which is somewhat like yours.

    =IF(A1="","",IF(ISERROR(VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,FALSE)),"",IF(VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,FALSE)="","",VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,FALSE))))
    This formula is too costly. You can shorten this to:

    =IF(AND(A1="",COUNTIF('Master List'!$A$5:$A$4004,A1)),VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,0),"")

    Aladin

  8. #8
    Board Regular
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    357
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    Why the COUNTIF('Master List'$A$5:$A$4004,A1)?

  9. #9
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-04-13 22:03, Cosmos75 wrote:
    Aladin,

    Why the COUNTIF('Master List'$A$5:$A$4004,A1)?
    The COUNTIF is part of the IF test condition. If it is <> 0 then true and do the vlookup, else false and return "".

    Very nice job. BTW, I think Aladin has a slight typo

    Change
    =IF(AND(A1="",...

    to

    =IF(AND(A1<>"",...

    Regards,
    Jay

  10. #10
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    On 2002-04-13 22:03, Cosmos75 wrote:
    Aladin,

    Why the COUNTIF('Master List'$A$5:$A$4004,A1)?
    It checks whether A1 itself exist in Master List before VLOOKUP attempts to fetch the value associated with it. Doing this check with COUNTIF is more efficient than doing it with VLOOKUP with ISNA (or ISERROR). BTW, as Jay noted, the AND part has a typo:

    A1="" should be A1<>""

    If the range $A$5:$A$4004 does not contain ant blanks, we can further shorten the formula to:

    =IF(COUNTIF('Master List'!$A$5:$A$4004,A1),VLOOKUP($A1,'Master List'!$A$5:$B$4004,2,0),"")

    Aladin


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com