Disable lookup autofill

[ajaykgarg]

Hi

This may sound weird.

I have a lookup table, using which I lookup user name and populate their location on the main sheet (using VBA code).

For names missing in the lookup table, I get #N/A, which is expected.

Next, I loop through all #N/A's on the main sheet, and add their corresponding user names in the lookup table.

Here, I want to stop my program, and pop up a msgbox to add locations for user names newly added in the lookup table.

But, as soon as the names are added to lookup table, their locations are updated as 0 on the main sheet.
I think this happens due to lookups refreshing automatically, and substituting missing locations with zero's. (Remember, user has not yet got a chance to update these lissing locations in lookup)

Is there a way to stop this lookups refresh until user manually updates loctions and clicks a button?

I will try to create a sample sheet with this scenario, and upload it later today, but meanwhile, if anyone has any solution, I will appreciate it very much.

tia
Ajay

[ajaykgarg]

Added sample sheet

[foxguy]

Hi Ajay;
I think that the only way that you can prevent the refresh is to set calculation to manual.
I don't think that is a good idea. If you turn automatic calculation off and the user doesn't put something into N8 (which presumably would set calculation back to automatic), then calculation is still set to manual.
I think this is a better solution.

Please Login or Register  to view this content.

Or you could change the formula in B5 to

Please Login or Register  to view this content.

If you go this route and have a Huge list, you might do this

Please Login or Register  to view this content.

that way vlookup only executes once so the calculation is faster

[ajaykgarg]

Thanks, Foxguy.
I used a work around wherein I programatically pre-poulate the cell B5 with text "#N/A" so that it doesn't default to zero after I add "D" to lookup table.

I know the solution is as weird as the problem, but It works for me.

Thanks very much
Ajay

[snb]

It's only a matter of:

Please Login or Register  to view this content.

[foxguy]

Hi snb;

It's only a matter of:

Please Login or Register  to view this content.

You're right. I completely forgot about this, and it could easily be a better solution than mine.