lookup/filter data, update it and save it

[fre_]

Hi Community,

I'm busy with the following little project:

Given is an input table in Excel. (input.xlsx)
It contains following fields:
ID, name, address, postal code, date_of_start (headings are in first row A1, A2, A3, A4, A5)
This table has 300.000 records. (data start at row 2)

I would like to create a new Excel File (lookup.xlsm) where I can filter the data in the original Excel-file. (filter based on name and address)
In the filtered results, it has to be possible to change/update the value of "date_of_start" and that should be saved in the original table. (input.xlsx)

=> is such thing possible with Excel/VBA?

I discovered the Excel Advanced Filter function, but my need is a little bit more complex than that I guess:
- I need to have 2 files: one with the data (backend) and one with the search & update function (frontend).
- the changes made in the frontend has to be saved in the backend.
- The use of MS Access is not possible.

Any advice how to manage this would be appreciated.

(with Excel Advanced Filter, I nearly had what I wanted, but the problem is that when you use "copy to another location", you can't make changes in the filtered results, and that's what I need)

[dflak]

Please attach the sample workbook - the backend especially. Attach the front end so we know what you want it to look like.

There is a function in Excel called MS-Query. It can read from almost any data source, including another Excel workbook. So you can use the "back end" as if it were a database file. I can help you with the steps for setting it up but it will make more sense if we both working with the same set of files.

[fre_]

Hi,
thank you for your reaction.
I will look into MS Query.

Meanwhile, I've made 2 test-files to work with in my dropbox (backend.xls and frontend.xls):
https://www.dropbox.com/sh/3klscgv1w...2RqqLt1Ga?dl=0

(I can't upload the files to this thread for some reason)

[dflak]

I notice that you now have a requirement to write data back to the source file. I'll work on that piece later. I have to open the source file, find the records and update only those lines. Too bad Access is not an option. This is the kind of things databases do automatically without coding. There is a concept in quality management called "fitness for use:" the more you have to modify a product before you can use it, the lower quality it is. Access = no modifications, Excel = some coding required.

Attached is a document that explains how you can connect to the back end using MS-Query. About the only thing I forgot to record was that somewhere in the process, it asks you where you want to return the data. It looks like you want it to start in Cell C8.

[fre_]

Thank you very much for this detailed walkthrough.

The reason why I said that MS Access is not an option is because MS Access is not installed on the PC of the person that has to use the excel Frontend file.
Or can Excel save to a mdb-file without MS Access has to be installed on the same PC?
In that case, I could consider using MS Access as backend if that would be easier to save the updated data.
(I do have a license for my own PC).

[dflak]

Microsoft provides free, runtime only licenses for Access. They allow you to run already-developed queries, forms and reports. They won't let you modify or add tables, forms or reports, but you probably don't want the end user doing that anyway.

The end user would have no problem doing data entry or running reports.

Also, MS-Query works well with an Access back end without any kind of Access license installed.

In answer to your question, it is possible to have Excel update MS-Access. I've done it, but I'm rusty; it's been about a decade for me. It will take some coding to develop the query. If you don't give the user a runtime Access front end, this would be the way to go; the Access part will take care of finding the correct records and updating them.

You have a relatively good application for this. The ID is unique, and you know exactly what field you will be updating. This makes formulating and building a query string easier.

What version of Access do you have?

[fre_]

Hi, thank you for this info.

I'm using the 2010 version of Ms-Office.

[fre_]

Been busy a whole day looking for a solution but I came to the conclusion that I will have to use something like Access runtime.
The filtering part is not that difficult using Excel only, but the part where the data has to be updated and saved is more difficult to handle without other tools.

Thank you again for all the advice.

[dflak]

Well, the MS-Query part isn't wasted. It works very well with MS-Access and no additional licenses are required. If you decide to use it with Access, I advise writing the query part in MS-Access with any "fixed" parameters in place. Anything you need to pass at runtime, set up on the MS-Query side.

Also MS-Query won't work with Access queries that use UDF and a couple of Access-specific functions like NZ(). In the case of NZ() you can replace it with an iif statement.

Databases are good for managing the data. Excel I good for reporting it.

[fre_]

Thank you for that advice. I will give it another try using that combination.
If I could get it work without other tools, it would be great.