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)

Fre, I'm probably misunderstanding your need. You posted this in the programming forum, but it isn't obvious to me that you need a VBA program for it. Let me rephrase this and see what I'm getting wrong. If you were doing this manually, rather than in VBA, your steps would go like this:

0) There exists a file Input.xlsx.

1) You open Input.xlsx (I know, it's supposed to be a copy; bear with me a minute.)

2) You do whatever you want to Input.xlsx—filter, change the value in the date_of_start column, whatever you like.

3) You save the results to Lookup.xlsx, and also save it to Input.xlsx.

Now the changes you made are both in Lookup and in Input. Isn't that what you wanted? I figure it isn't...but what am I missing?

Hi Bob,

Thank you for your response.

To illustrate what I want, I've made two files:
https://www.dropbox.com/sh/3klscgv1w...2RqqLt1Ga?dl=0

The "backend" is the input excel-file. (which I called input.xlsx in my first post)
The "frontend" is the interface for the end-user. (which I called lookup.xlsm in my first post)

I would like to update the data (in the backend file, table starts at cell A1) via the frontend file (where you can filter the data for looking up the right record).
I've added some explanations in the files themselves.

Hopefully, my explanation is clear enough.

Ok, I think I get it. If we call these BackEnd.xlsx and FrontEnd.xlsm, I take it you want the user to be able to do the following:

1) Open FrontEnd.xlsm and enable macros.
2) Type in fragment of a name and/or street address, and...
3) Hit <Enter> to see listed below the rows from BackEnd.xlsx that match the criteria entered.
4) Type a value into FrontEnd.xlsm's date_of_start column, and...
5) Hit <Enter> to have that value written over whatever's in the corresponding cell in BackEnd.xlsx.

That doesn't sound very complicated. What have you already done in VBA? It sounds like you may know how to do some VBA in Access and you need a start in doing the same sort of thing in Excel, maybe?