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
Gold	Doe	John
Gold	Smith	Sam
Gold	Bowers	Hedwig

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Lot[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]SPACE #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Sched[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Last Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]First Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]ID NUMBER[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]Grade[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V1 Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V1 Color[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V1 Make[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V1 Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V1 Lic. PLATE #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V2 Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V2 Color[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V2 Make[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V2 Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V2 Lic. PLATE #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V3 Year[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V3 Color[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V3 Make[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V3 Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: center"]V3 Lic. PLATE #[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]306[/TD]
[TD="align: center"]EVIT[/TD]

[TD="align: center"]111277[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2004[/TD]
[TD="align: center"]Gold[/TD]
[TD="align: center"]Toyota[/TD]
[TD="align: center"]Highlander[/TD]
[TD="align: center"]AAA1111[/TD]
[TD="align: center"]2004[/TD]
[TD="align: center"]Grey[/TD]
[TD="align: center"]Toyota[/TD]
[TD="align: center"]Camry[/TD]
[TD="align: center"]ABC3242[/TD]
[TD="align: center"]2008[/TD]
[TD="align: center"]Black[/TD]
[TD="align: center"]Honda[/TD]
[TD="align: center"]Accord[/TD]
[TD="align: center"]OUI2309[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]309[/TD]
[TD="align: center"]ER[/TD]

[TD="align: center"]122448[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Ford[/TD]
[TD="align: center"]Ranger[/TD]
[TD="align: center"]ASD7987[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]310[/TD]
[TD="align: center"]ER[/TD]

[TD="align: center"]364268[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2005[/TD]
[TD="align: center"]Gold[/TD]
[TD="align: center"]Acura[/TD]
[TD="align: center"]TL[/TD]
[TD="align: center"]GVS8937[/TD]
[TD="align: center"]2002[/TD]
[TD="align: center"]White[/TD]
[TD="align: center"]Ford[/TD]
[TD="align: center"]F150[/TD]
[TD="align: center"]WJH6432[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</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
Gold	Doe	John
Gold	Doe	John
Gold	Doe	John
Gold	Smith	Sam
Gold	Bowers	Hedwig
Gold	Bowers	Hedwig

<tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Lot[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]SPACE #[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Sched[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Last Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]First Name[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]ID NUMBER[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Grade[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Year[/TD]
[TD="align: center"]Color[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Make[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Model[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FFFF00]#FFFF00[/URL] , align: center"]Lic. PLATE #[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]306[/TD]
[TD="align: center"]EVIT[/TD]

[TD="align: center"]111277[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2004[/TD]
[TD="align: center"]Gold[/TD]
[TD="align: center"]Toyota[/TD]
[TD="align: center"]Highlander[/TD]
[TD="align: center"]AAA1111[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]306[/TD]
[TD="align: center"]EVIT[/TD]

[TD="align: center"]111277[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2004[/TD]
[TD="align: center"]Grey[/TD]
[TD="align: center"]Toyota[/TD]
[TD="align: center"]Camry[/TD]
[TD="align: center"]ABC3242[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]306[/TD]
[TD="align: center"]EVIT[/TD]

[TD="align: center"]111277[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2008[/TD]
[TD="align: center"]Black[/TD]
[TD="align: center"]Honda[/TD]
[TD="align: center"]Accord[/TD]
[TD="align: center"]OUI2309[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]309[/TD]
[TD="align: center"]ER[/TD]

[TD="align: center"]122448[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2000[/TD]
[TD="align: center"]Blue[/TD]
[TD="align: center"]Ford[/TD]
[TD="align: center"]Ranger[/TD]
[TD="align: center"]ASD7987[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]310[/TD]
[TD="align: center"]ER[/TD]

[TD="align: center"]364268[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2005[/TD]
[TD="align: center"]Gold[/TD]
[TD="align: center"]Acura[/TD]
[TD="align: center"]TL[/TD]
[TD="align: center"]GVS8937[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]310[/TD]
[TD="align: center"]ER[/TD]

[TD="align: center"]362574[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]2002[/TD]
[TD="align: center"]White[/TD]
[TD="align: center"]Ford[/TD]
[TD="align: center"]F150[/TD]
[TD="align: center"]WJH6432[/TD]

</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.
​