VLOOKUP formula inserted ONLY (dynamically) when I insert a

[Cosmos75]

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 ]

[croweld89]

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

[Cosmos75]

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?

[Big Blue]

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.

[Cosmos75]

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

[daleyman]

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)

[Aladin Akyurek]

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

[Cosmos75]

Aladin,

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

[Jay Petrulis]

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

[Aladin Akyurek]

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