Lookup UDF

[puzzlelover22]

Hi All,

Working on my first UDF and I'm getting a #VALUE! error.

Attached is a sample file. Basically I'm trying to write a function to replace the INDEX/MATCH I have to write constantly. In the sample file, the lookup values are in a separate tab. For my purposes, the lookup values are in a separate workbook.

Any help would be very much appreciated!

Best,
Alex

[davesexcel]

Does this work for you?

Please Login or Register  to view this content.

[jason.b75]

The function will not recalculate if any changes are made on the Data sheet.

You need to make it volatile, or pass the data sheet ranges as function arguments so that they are recognised as precedents to the function in order for it to calculate properly.

[puzzlelover22]

Thanks for your replies. Davesexcel, that works in the sample file, but still returns an error when I try to add path and workbook names. Any thoughts on how I should fix that?

Good call jason.b75

[puzzlelover22]

Thanks for your replies. Davesexcel, that works in the sample file, but still returns an error when I try to add path and workbook names. Any thoughts on how I should fix that?

Good call jason.b75

[MrShorty]

In the file in post #1, I see mostly typo errors:

The file name is udflookuptext.xlsm, but your code is trying to search in a file named "udfloopuptest" (no extension).
I notice that you are erroneously trying to access a worksheet object instead of the worksheets collection.

When I correct those two errors (spell the file name correctly and include extension, access worksheets("data")), the code seems to execute correctly.

You mention giving the function a path. I don't know that path has any meaning for an opened workbook, so I wonder if you are trying to access closed workbooks with this udf. I don't know that VBA can do that (though if others know better, feel free to correct me). If you are needing to access data in closed workbooks, I would make that point clear here in the thread, so that that aspect of the task can be properly accounted for in any proposed solutions.

[jason.b75]

Why not skip the UDF idea, if you want to reduce typing, use some named range trickery instead.

* Note that I haven't tried this with the Data sheet in a separate, closed workbook, hopefully it will work *

Create a named range called GetMSA_County that refers to

=INDEX(Data!$D$2:$D$12,MATCH('Front End'!$A:$A,Data!$B$2:$B$12,0))

Remember to include the full path for the other workbook that holds the Data sheet.

Now just enter =GetMSA_County into B2 of the front end sheet and the background named range formula will (hopefully) do the rest for you.

[davesexcel]

The function appears to work like this

Please Login or Register  to view this content.

The function probably will only work if the other workbook is open.
Formulas will work if the other workbook is closed.

Please Login or Register  to view this content.