Inserting a row on a dynamic searchable dropdown list - rows moving up, top vanishing!

Hi All,

I've decided to start a new thread for this issue I am having rather than tag it on to the thread I had previously posted which was related to the same spreadsheet but not the same problem!!

I have a spreadsheet which uses a searchable drop-down list to find a supplier.

The structure of my spreadsheet is, I have 4 worksheets that contain supplier names, I then have a tab called LIST - (CG ONLY) this is where my dynamic name ranges are for the searchable drop down, the searchable drop down comes in to play on tab 2019_to2020_SupPerf, under supplier name you start to type and then click the drop-down and the suppliers appear which contain the typed characters, works spot on, really pleased with it.....

HOWEVER!!!

My problem comes when I add a new supplier to one of the 5 worksheets, all that works fine and filters which is what I want (Thanks Ali for your help with this one) but when the new supplier/company is added the LIST - (CG ONLY) automatically updates, which does, BUT what it appears to be doing is pushing the suppliers that are already on there up, for example my suppliers start in row B3 the formula is =STOCKISTS!$A$2, but when I add a new supplier on the STOCKISTS worksheet (it could be any of the supplier worksheets) the formula =STOCKISTS!$A$2 disappears and now row B3 is showing =STOCKISTS!$A$3, my new supplier has been added, but the top supplier has vanished off the list??

How can I add a new supplier and get the table to move down rather than up? it's doing some odd things!! I will admit I am a huge novice and what I have created has been from watching youtube and the help of Ali on this forum, I'm struggling to understand it all!

Current spreadsheet attached, any help would be great

To replicate my problem, go to the worksheet LIST - (CG ONLY) take not of the top supplier which is AALCO HULL LIMITED, now go to tab STOCKISTS, click the remote button 'Insert New Supplier', type bbbbbbbbb (or whatever you like) as the supplier name, then click the remote button 'Confirm New Supplier' this filters the names. Now if you go back to tab LIST - (CG ONLY) you will see AALCO HULL LIMITED is no longer at the top and has vanished off the list?

WHY!!!!! it's driving me bonkers

Any help would be extremely appreciated

Hi Ali,

I had marked it as SOLVED, but I'm guessing as soon as a reply is typed the SOLVED bit disappears? i will go and mark it as SOLVED again, sorry about that

Thanks

I have just seen you have done it for me yet again lol

Thanks

I am going to have to give up on this one and look at other options,

I decided to group all the suppliers into one table and everything worked spot on, there is an issue when you remove a supplier from this table but that will be a rarity and I can do that rather than a user.

HOWEVER

I now have another problem!! I didn't know you couldn't share bloody workbooks with tables in them so this is now rendered useless!! there is always going to be a minimum of 2 users at once using the spreadsheet! i think my only option is to create two identical spreadsheets and have one as a clone which copies data from the other and the two users can access their own spreadsheet and the data will replicate (the two users input different information, the problem comes when i have more than two users!! i really hoped Excel would be userfriendly but it really isnt for the way i am wanting to use it..

Gutted after spending days and days working and re-writing the flipping thing

Thank you for all the help, until the next instalment

So if I place the spreadsheet in sharepoint or onedrive can more than one person use the spreadsheet at once and it will/can be saved? I use one drive a lot for sharing large files but never for this sort of thing and I've never used sharepoint

Will take a look when I get in to work

Thanks

Unfortunately, Sharepoint and Onedrive will not work, I have tried both this morning and I keep getting the warning that someone has it open, etc.

If I share through one drive, it won't let two people in to edit at the same time.

Sharepoint, well online this looks to work, however I feel it only works with excel online, which my spreadsheet and formulas and remote buttons do not work, so yes it could do what i need it to do but not with a complicated spreadsheet, now i know you can open in proper excel and it will sync, BUT it will not allow co-editing if you choose this option.

Why is this so damn hard!!! I thought as things progressed in the world of technology things go easier not more bloody antiquated!!

So i am going to have to give up and live with one spreadsheet on our server and people are going to have to ask to use it if someone else is in it!! how office 2003 is that!!!!

Such a shame..

Hi Ali,

No not at all, you have helped me a hell of a lot, i am sorry i have made you feel that way, it certainly was not intended, not in the slightest, i appreciate everything you have helped me with, I just do not think i am bright enough to get these things to work!! we are a small old fashioned company, i am about as 'IT guy' as it gets here!!

you are correct, it looks as though it will work with tables but it will not work with the radio buttons I have set up, and it will not work with my dynamic searchable drop-down list i haev set up, all i can do is try stripping things down until it does work.

I get these errors

Attachment 620452

Attachment 620453

Attachment 620455

if I save a local copy and I try and open it at the same time as another user it tells me I can only open it in read-only or notify the other user who has it open.

Again I really do appreciate your help, I am frustrated with myself I do not like to give up on things but I feel the way things are set up I'm not going to have any choice.

Thank you so much

Having taken the radio buttons out it works better on SharePoint but the searchable dropdown doesn't work, which I need, to ensure sure all the names match so the formulas on the other worksheets are linked correctly (trying to make it idiot proof or I'll end up with one hell of a mess to sort out). I am not sure why it's referring to external links, I do not have any external links in the workbook, all the links are linking to other worksheets.

If I make the dropdown a standard data range dropdown, I think it will work but then the user will have to scroll through a lot of suppliers to find the supplier they want, there are still hundreds of suppliers to add to the list so it will be a pain to search through for a supplier.

AHH right I see, thank you for clarification

It's far from your fault, that's me in general, nothing is ever easy lol!!

Well I have now issued the spreadsheet to the business so we will see how it goes if I need to revisit the shared side of things i will (well knowing me I will look into it anyway, I don't like to be beaten!)