Excel Update - Drop Down List - NEED HELP!!

[XL_Rookie]

Hello All,

I am using MS 365 for Business, there seems to be a recent update as I use to be able to select a cell with a drop in it, then select multiple sheets, and enter the data into the first selected cell which would then put the data into into all worksheets, but this doesnt seem to work anymore.

Looking for help to fix this as it is causing more work for me.

Thanks in advance.

[TMS]

Welcome to the forum.

I have to ask ... why would you upload a Word document with a picture of a broken Excel workbook? There is quite literally nothing we can do with that. No-one is going to create a test bed to check out the scenario you have described. What are the chances it would reflect your problem?


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

[XL_Rookie]

Hey TMS, thanks for the reply.
Sorry I am new to posting on here. The workbook that I am using has a significant amount of confidential information in it.

I have created a very simple workbook with multiple sheets and a drop down list in the same cell on each sheet. Weeks ago if I did this, I would be able to select the cell, then select all the sheets, then type "Red" into the cell and all sheets would say "Red"... but this doesnt work anymore.

[Croweater]

It's not 100% clear what you are after.

Do you ALWAYS want B3 to be the same for all sheets as you enter in the drop down in sheet test1
OR
Do you want it to be the default and still allow changes in all the other sheets?

If the former you don't need drop down lists in the sheets other than test1 at all, just a formula.
If the latter, then you'll need some vba as data vaildation doesn't really cater for 'default' values.

In fact what I think would have happened is someone saved it as a .xslx file and deleted the code somehow?

Please Login or Register  to view this content.

[XL_Rookie]

I can confirm that file is an .xlsm, there are macros to protect and unprotect all sheets

Please Login or Register  to view this content.

This has worked for a very long time.


I have modified (sanitized) the original workbook, removed a ton of confidential information.
This is a portion of a workbook that we use to quote, please note only the dropdown lists in column A will work, the rest will have errors as I removed associated data.
There are no defaults for the cells, between rows 3 and 12 they remain blank until we input the work centre, but we used to be able to select multiple tabs, then select a cell in the first tab you selected, and type in the work centre into the cell and it would appear in that same cell on all selected tabs.
However it doesnt work anymore, I have not saved it in any new manner, there has been no macro changes, not sure what else to look for.

[Croweater]

So, what you are saying is that the 'representative' workbook you provided is absolutely nothing like the real one and therefore the code provided is of no help whatsoever....right?

Also that you are asking for us to debug your code without actually providing it (You have said it is a .xlsm file but have attached a .xlsx and the code you have provided, is not the ONLY code in the workbook is it?) Is that right?

Oh....and you completely ignored the questions I asked. I'm out.

[XL_Rookie]

Hey Croweater,

I don't need anyone to debug the code I posted, there is nothing wrong with it, I only posted it to show that it was an .xlsm file and posted the code... my bad, I didn't think conveying what I needed assistance with would be so hard.
I can not upload the actual workbook as it has too much confidential information on it so I sanitized it by removing ALL codes, but the code has nothing to do with the problem, the only code in the workbook is what I posted above.

To Answer your questions:
Do you ALWAYS want B3 to be the same for all sheets as you enter in the drop down in sheet test1 - No
OR
Do you want it to be the default and still allow changes in all the other sheets? No

If the former you don't need drop down lists in the sheets other than test1 at all, just a formula.
If the latter, then you'll need some vba as data vaildation doesn't really cater for 'default' values.

Each sheet is used for a different part, however some of the cells may be same but not all the time so there is no default, the default is blank, and there is potential that every time we use it the cells will all be different or some may be the same , so we type the work centre into the cell instead of taking to the time to find it in the drop down menu. But previously I was able to select multiple sheets and type into a cell and it would appear on all selected sheets (in that same cell).

If you select tabs "Quote Part 1" through "Quote Part 5", then select "A8" and type in "MM5" into cell "A8" , it will show up in tab "Quote Part 1" only... a few weeks ago it would show up in all tabs "Quote Part 1" through "Quote Part 5"

Thanks for taking time to help though.

[JeteMc]

I selected cell A6 on the Quote Part 1 sheet and then select all the sheets by pressing and holding the Shift key and selecting the Quote Part 5 tab.
If I type STK28 into cell A6 then STK28 will appear in cell A6 on the other sheets as well.
However, if I then select STK32 from the drop down then it only appears in cell A6 on the Quote Part 1 sheet.
Granted that I am using an older version of Excel so the functionality may be different.