Hi, is there a way to add a drop down menu to an existing large spreadsheet that will retain the existing data in the spreadsheet. When I create the drop down it appears that any existing data in the column disappears or is deleted. Thanks much
Hi, is there a way to add a drop down menu to an existing large spreadsheet that will retain the existing data in the spreadsheet. When I create the drop down it appears that any existing data in the column disappears or is deleted. Thanks much
Welcome to the Forum John Dockall !
Creating a dropdown does not delete any data. Can you explain in more detail what you do when you create your dropdown that causes this problem? Are you using macros?
Im using Excel in Office 365. Im not using macros to create the drop down and there are no macros in the spreadsheet. Im following the typical procedure to create the drop down vi data validation, choose list, etc.
But the drop down list choice tab only appears in the first row of the column for which Im trying to create the drop down. Im presuming I have to "drag down" the tab down the column to get
it to be present in cells further down the column. When I drag the tab down the spreadsheet cell values already entered previously disappear. Obviously I am doing something wrong
following the data validation procedure but dang if I know what it is. Ill give it another go and see how it goes...thanks for the assistance. Part of the problem might be Im in compatibility mode with a spreadsheet created with an older version of Excel. Ill check that and convert the file to the present version of Excel and see if that fixes things.
JD
Last edited by John Dockall; 02-27-2020 at 10:04 AM.
This is quite different than what you described in your first post. That's not the right way to do it. When you drag down, you are dragging down the values too, so naturally they will replace any values that are there to start with.
- Select the cell with the dropdown
- Copy
- Select the cells where you want this same dropdown to apply
- Click Paste Special, and select Validation, click OK
The data validation rule along with the dropdown will be applied to the target cells without changing their values.
Let me take an example:
Assume that you data validation only in Cell A2 and you need to copy only data validation to Cell A3, A4 and so on. But problem is that A3, A4 and so on cells have data in it. If we drag A2 Validation to below cells A2 data will be copied in below cells with validation.
To over come this problem select all the cells which you require Data validation (Make sure that first cell in the selection has data validation which you want to copy) and press ALT D+L and select Yes and click Ok.
Hope it solve your problem!!
A major thank you for this....this was my problem...apologies I was not more clear in my first description of my tribulation. Your suggestion solved my problem post haste.
That was it. thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks