Advice on keeping data current across 2 sheets

[Dungadin]

Looking for advice and help.

I have a user form that can enter, search, edit and delete information that I need to expand on.

Currently all the information is stored in 1 sheet, "Data". Which is the source and copy location for a filter for the "master data" (if you will).

For example, if I wanted to search for "Doe" in my user form, I would select the column header from a combobox in the userform, which would populate cell AA1, and from a textbox in the userform, "doe" would be populated in cell AA2, which would then search, filter and copy the data from Row 2, A:V to Row 2 AC:AX, and then display that information in listbox in the userform. I can then double click the data in the listbox which populates associated textbox's, which I can edit and save the new information.

This is all well and good for the first 7 columns A:G. The problem is, there are times I need to search by either, year, color, make, model or plate#. Since this information is in multiple columns, and I need it to be displayed in the listbox as single line items, I need to copy and keep up to date, the same information in a 2nd sheet.

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	Lot	SPACE #	Sched	Last Name	First Name	ID NUMBER	Grade	V1 Year	V1 Color	V1 Make	V1 Model	V1 Lic. PLATE #	V2 Year	V2 Color	V2 Make	V2 Model	V2 Lic. PLATE #	V3 Year	V3 Color	V3 Make	V3 Model	V3 Lic. PLATE #
2	Gold	306	EVIT	Doe	John	111277	12	2004	Gold	Toyota	Highlander	AAA1111	2004	Grey	Toyota	Camry	ABC3242	2008	Black	Honda	Accord	OUI2309
3	Gold	309	ER	Smith	Sam	122448	12	2000	Blue	Ford	Ranger	ASD7987
4	Gold	310	ER	Bowers	Hedwig	364268	12	2005	Gold	Acura	TL	GVS8937	2002	White	Ford	F150	WJH6432

<tbody>
</tbody>

Data​

Because I'm having problems being able to search multiple columns and display this information in the UserForm, I need to basically copy this information and condense it so it will display properly in the userform.

	A	B	C	D	E	F	G	H	I	J	K	L
1	Lot	SPACE #	Sched	Last Name	First Name	ID NUMBER	Grade	Year	Color	Make	Model	Lic. PLATE #
2	Gold	306	EVIT	Doe	John	111277	12	2004	Gold	Toyota	Highlander	AAA1111
3	Gold	306	EVIT	Doe	John	111277	12	2004	Grey	Toyota	Camry	ABC3242
4	Gold	306	EVIT	Doe	John	111277	12	2008	Black	Honda	Accord	OUI2309
5	Gold	309	ER	Smith	Sam	122448	12	2000	Blue	Ford	Ranger	ASD7987
6	Gold	310	ER	Bowers	Hedwig	364268	12	2005	Gold	Acura	TL	GVS8937
7	Gold	310	ER	Bowers	Hedwig	362574	12	2002	White	Ford	F150	WJH6432

<tbody>
</tbody>

Data2​

I see there being 2 options for this.
1. When the data is entered, I could write into the VBA to enter it into the 2 different locations. If I do that, then I would also need to write into the sub's to also edit or delete in the 2 locations.

2. Write a single sub, that copies the current data from sheet 1 (data) to the 2nd location sheet 2 (data2). But if I do a copy and paste code, won't it just keep adding the data to it? Or do I need to do some sort of "clear" to remove the data in the 2nd location? Or a filter?

With option 1, I think I can figure out how to write all of that, just going to be a bit time consuming and I think susceptible to errors. Because even though it's supposed to be edited through the userform, I can see someone going directly to the worksheet and editing the data there.

With option 2, I have no idea how that code would even look.

Please advise....and thank you for your help.

 

[My Aswer Is This]

I saw a similar posting from you earlier this month and you did not receive any help.

I believe it is always best to explain what your attempting to do. You seem to be telling us how you want to do this instead of letting us figure out the best way to accomplish the task.

Like this:

I would select the column header from a combobox in the userform, which would populate cell AA1 and even more.

I think your goal is to search for a parking location.

But you want the option to search by several different values like Color Make License number and more I believe I saw in your previous post.

And I never understood where to end result would be entered.

If you were to search by license number and that number was found what would you want the result to be.

Like if you entered 789456 into Userform Textbox1 we could have a script search the entire sheet for that number and a pop up message box that would tell you what column and what row we found that license plate number in.

In this case there would never be more then one
Now if we searched for parking location by Car color there could be more then one result.
There may be several cars in the parking lot which is Red

We would not need to specify the exact column to search

We can search a range like Range("A1:L400")


Please post back why something like this would not work.

And provide what you want the final result to be and let me figure out how to do it.
I like helping so I hope I will be able to help you.
​