Worsheet change event help

[USCguy09]

I have a spreadsheet which I'm using it like a querying tool.
In one tab named, "properties", contains a large inventory of properties each with a unique property code identifier. I am pulling all this from an external database source.

In another tab named, "query", is where I type in whatever property codes in column B (starting with B6) and the rest of the fields will be auto-generated. I'm currently using the index & match function to accomplish this. Problem is, I need this for more rows and and to copy the equations to thousands of rows will be inefficient and slow.
The equation being used in cell C6 for example is below:

[B][FONT=Arial][SIZE=2][COLOR=#000000]
=IF(INDEX(Table_db.accdb[street],MATCH(B6,Table_db.accdb[property_code],0))="","",INDEX(Table_vBase_Connect.accdb[street],MATCH(B6,Table_vBase_Connect.accdb[property_code],0)))</pre>[/COLOR][/SIZE][/FONT][/B]

Its like this for the rest of the cells but with the appropriate references.

Someone recommended me to use the worksheet change event macro.
Problem is, I don't know how to convert my equation syntactically to VBA.

Can anyone help me out with this?

 

[ChrisM]

I don't think you'd want 1000s of formulas running off of a worksheet change event macro. That means any time any value in your worksheet changed (ie you type some text somewhere) all those formulas would recalculate.

You really can't beat the built in formulas for speed, I would stick with those first. Have you considered putting your data in a pivot table, then using GETPIVOTData formulas to do the lookups? That would eliminate all the Index/Match.

Or how about the Database functions (dlookup, dsum, etc)?

 

[USCguy09]

I don't think you'd want 1000s of formulas running off of a worksheet change event macro. That means any time any value in your worksheet changed (ie you type some text somewhere) all those formulas would recalculate.

You really can't beat the built in formulas for speed, I would stick with those first. Have you considered putting your data in a pivot table, then using GETPIVOTData formulas to do the lookups? That would eliminate all the Index/Match.

Or how about the Database functions (dlookup, dsum, etc)?

hmm i see. i'm really not all that familiar with the worksheet change event but i def would not want the formulas to recalculate upon every time text is entered anywhere.

Right now, my sheet is set to 1000 rows and has 10 fields so thats 10,000 equations running. I saved the sheet as a binary and the file size is 365 kb.
so i guess its not that bad. Its just that, there could be a time where more rows are needed and I just want to make it easy to edit the sheet appropriately quickly.

As for the pivot table, I haven't really considered it cause I don't see a use for it right now. Wouldn't replacing all the index/match equations with the getpivotdata equation be essentially the same thing? performance wise, not sure if it will make a difference.

I want to stay away from database functions b/c this spreadsheet is oriented toward other colleagues of mine whom are unfamiliar with database functions, SQL or any programming languages for that matter.

I guess I could just add more rows then. I'm just OCD about finding faster, more efficient ways of getting things done.

 

[ChrisM]

Yes, the pivot table or database formulas are just variants on what you're doing now. Can't say for sure which would be faster, was just throwing alternatives out there.