Create form to update worksheet

Hi,

I was hoping someone could help.

Attached is a basic form created so that when you enter specific information in the search box and press search it locates the cell on the next worksheet and takes you to it. I need an amendment to this if it's possible.
What I require is when you enter a number in the search box and press search it doesn't visibly take you to that cell on the spread sheet but it finds the row that that number exists in and creates a message to say number located or if not, no number found.
Once the number has been located I need 2 more boxes that you can enter more information in that automatically populates cells on the same row that number exists in.
For example if I enter k0225 it should find that on the SIMS worksheet and say number located. I want to then enter a name in the 1st new box "Peter Parker" which will enter this into column D on the same row. Then enter 07895412365 into the 2nd box which will enter this into cell C on the same row.
This gives the ability to update cells quickly without having to navigate through thousands of rows of data.

Please see the attached to work with.

Let me know if this is possible and if so let me know how or by all means show me.

Thanks in advance

Maybe something like this ?

Please Login or Register  to view this content.

Thank you so much Karmapal and Mehmetcik I'm sure these will work but would you be able to add them to the workbook I attached as I'm struggling to get them to work for me and I'm sure it's about how I'm adding the text boxes and the macro to validate against the SIM tab?

Apologies as you've already done so much.

Using code above from karmpala - this will get you started

Hi Nigelog,

This works really well thank you.

It has however given me more ideas of what I would like to happen which is beyond me so I am looking for some kind assistance.

If you see the attached which now includes Nigelog's solution, I would like the below to happen. Hope this make sense.

On the SIM number side I need 1 search box rather than 5 and when you enter the SIM number it searches only the column that contains SIM numbers and verifies if it exists. If it exists then the next boxes appear as they do now asking for the additional information which will update the corresponding column cells.
What I also need is the option if the number doesn't exist then it will enter a new line in the SIM column and then continue with the additional information options filling in the corresponding column cells.

Then

On the Phone number side do the same thing but lookup from a column that contains the Phone numbers. The additional info boxes that then appear need to match those from the SIM side.

What I would also like is the main sheet to be locked down with a password so it can't be manually amended meaning the only way to amend the sheet is by using the form we're creating.

In addition to this, can there be logic created that does the below.
If the SIM or Phone number exists - update the corresponding additional columns cells
If the SIM or Phone number doesn't exist - add new line to the bottom and update the corresponding additional columns cells
If there are more than one entry of the same SIM or Phone number but all apart from 1 has been struck through then only update the cells of the line that hasn't been struck through. Striking through doesn't have to be the only option, if this doesn't work with that but would work with colouring the line then we can use that option instead as we will be using both striking out and conditional formatting colours.

I know this is a lot to ask but it would be so valuable to me and a project I am working on I will be eternally grateful. Any further questions then please do ask.

I forgot that I had done this
This was a similar project for checking if mobile numbers existed in a database, this will point you in the right direction.

Hi Nigelog,

Thanks for this, I see what you're intending to do and it makes sense. I'm not brilliant at this sort of thing so I'm struggling to get anything working the way I would like. My ideal would be your first suggestion but tweaked to include the additional options I listed before.
Is this possible do you think? Apologies for asking as this probably results in you doing most of the work but you have no idea how appreciative I'll be should we get this working as desired. The impact this will have on our business will be huge.

Hi
refresh my memory as to what exactly you require preferably by mocking up what you require in a workbook adding notes if required. Please then attach to a post here.

The second version I forwarded is far easier to amend and utilize. Can you mock up your requirements in this??
Then there would only be one search box required as it seems all sims have a letter prefix to the number??

See if this is nearer

@ dotchiejack - very tidy option

@ dotchiejack - This is almost perfect but I think I might be able to tweak it to take it to the level I need it to be at with the data I have. Really clever piece of work here.

@nigelog - I liked your effort too but Jacks took my idea to a level I hadn't even though about.

I really appreciate both of your efforts with this, you're saved me weeks of work and I will now be able to see huge cost savings for my company.

Thanks again

May nigelog an me have a %?

HAHA I would love to but I'm not even going to see any of those savings

You're right though this does work faster, good work. Just a quick request, and I know you've done so much already, but would there be a way to restrict it adding duplicate entries? For example right now I could enter K0206 and press New and it would add a new line but I only want unique entries so could this be restricted so it said "this number already exists" perhaps?

Also, I've taken a look at the code and I thought I had some clue on how these were put together but I'm struggling to see where to enter code to lock down the SIM sheet and where do I change the code to point to different columns as this is just an example and not using the correct columns and titles?

You're too good, I really appreciate it.

The example is attached.

I would like to see all the columns using the form but only update the ones listed below.

A, B, C, E, L, P

@dotchiejack - Have you had any success with this yet?

Hi Guys,

Is there anyone who can continue the great work dotchiejack started and include the additional requirements I listed in my previous post please? I'm getting close to my deadline and would really like to have this working prior to starting the work this is intended for.

I really appreciate any assistance in advance.