How do I add existing Drop Down Lists with Data Validation to more rows in spreadsheet.

Question

ISSUE NOW RESOLVED - syntax error in formulae

First row of my spreadsheet is the column titles.

In row 2 I have four Data Validation drop down lists and each column drop down list is dependent upon whichever item is picked in a drop down list in a previous cell in the same row, but not necessarily adjacent columns. All is working correctly in row 2. The multiple lists are set up in a separate tab.

The spreadsheet has 500 data entry rows to allow recording of approx. 6 to 10 jobs per day. How do I extend the Data Validation List rules to all 500 rows?

I have used Copy/Paste Special/Validation on each column without success. I have also tried highlighting all 500 cells in the column then going to Data Validation which identifies that "the selection contains some cells without Data Validation settings" but again it is not working correctly, by which I mean that the drop down lists are incorrect for the data selected in a previous drop down in the same row.

Does anyone know what I am doing wrong?

Many thanks for any assistance as this issue is driving me up the wall.

Regards,

Dee

Answer 1

Hello! I'm Jen, an independent advisor and a Microsoft user like you. I'd be happy to help you out with this issue.

If you want to extend your data validation, follow these steps.

1. Highlight where you want to extend it to.
2. Make sure you have included cells with the data validation.
3. Click on Data – Data Validation.
4. You will receive a message that says “ The selection contains some cells without Data Validation settings. Do you want to extend Data Validation to these cells?”
5. Click on Yes.
6. Your data validation dialog box will open up.
7. Click OK.

Kindly check the following links for more information.

https://support.microsoft.com/en-us/office/add-...

http://excelrocker.blogspot.com/2014/08/data-va...

Note: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

I hope this information helps.

If you have any questions, please let me know and I'd be glad to assist you further.

Thank you and Best Regards!

Jen :)

Answer 2

Hi Jen,

New information....PROBLEM SOLVED!

When I execute your instructions 1 to 7 in Column D, all the drop-downs are being controlled by Cell C4 (first line of the document). If I look at drop-down for Cell D5, the formula from Data Validation shows =INDIRECT($C$4) instead of =INDIRECT(C5).

I accidentally locked the formula to Cell C4. $ symbols now removed, Data Validation actions 1-7 repeated and now all working as required.

Thank you for your time and effort as it was looking at the videos you linked that gave me the idea to check the formulae.

Regards,

Dee

Answer 3

Hi Jen,

Thank you for your reply, however this does not solve my issue, possibly because I have not furnished you with enough information.

Column A is just a sequential job number.

Column B has a short drop-down of vehicle models(=Lists!$A$2:$A$7). Lists is the name of the tab containing the data for the drop-downs.

Column C has a drop-down for Issue Location on the vehicle and has 8 options(=Lists!$B$2:$B$9).

In Column D, for each 'Issue Location' selected in Column C, there are between 2 and 11 'Component' options to chose from dependent upon what was selected in Column C (=INDIRECT($C$4)). First row in document is Row 4.

Column E is a description of the 'Issue' for which there are two different drop-down lists depending upon what area of the vehicle ('Issue Location') was selected in Column C (=IF(C4="Paint",Paint_Issues,Issues)). (This is saying that if 'Paint' is selected in Column C then you are offered the Paint_Issues list, otherwise you see the general 'Issues' list).

The first row is working perfectly and also Columns B & C. I now need to know how to copy/drag Columns D & E down from the first row to make the correct drop-down lists appear in the the rest of the document.

The method you posted is what I outlined in my original post and is what I did in Columns B & C, but it does not work for Columns D & E.

Can you see anything in my formulae that I have done wrong that is causing a conflict?

Many thanks and best regards,

Dee

Answer 4

That's great to hear, Dee! I'm glad your problem now has been resolved.

Thanks for getting back to us and letting us know.

Stay safe and healthy!

Best Regards,

Jen :)

Answer 5

One way is to define your data as an Excel table. When you define validation on a column, as you add new columns, the validation is automagically extended.

!0 Excel Tables: The Excel Feature Every Assistant (And Your Team) Should Master – Free Online Seminarhttps://register.gotowebinar.com/register/3894208898484145677****Frustrated with the amount of time you spend fixing and formatting data in Excel?  Ashamed of how your spreadsheet looks and behaves? Discover the true power of Excel to make your life incredibly easier when managing and tracking data. Whether you are working with event lists, expenses, sales transactions, or a list of employees and customers, you want to ensure that the information is accurate and easy to consume. Learn how to effectively manage information to get the best results in less time. You’ll work smarter and faster than ever before!  In this session, you will learn how to:

.   •  Use Excel tables to manage any list

.   •  Easily find and remove duplicates

.   •  Find the right information using sorting, filtering and slicers

.   •  And much more!

.

!02 Overview of Excel tables****https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c

To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table (previously known as an Excel list).

.

!03 EZ guide to Excel Tables                     2016 09 09

https://www.accountingweb.co.uk/tech/excel/ez-guide-to-excel-tables

Although at first glance Excel Tables may look to be just concerned with formatting, they do a great deal more than just apply different colours and borders to a block of cells. Perhaps the most important attribute of an Excel Table is its ability to automatically expand to include any adjacent rows and columns into which data is entered. This means that, unlike a 'normal' Excel range, a reference to a Table column can adjust automatically to include new rows.

Tables have lots of other useful features. For example:

.  *  Formulae and formats can be copied automatically to the other rows in the same column and will extend to new rows added to the Table

.  *  Filter and sort dropdowns are automatically added to the Table headings row

.  *  Tables can be given meaningful names which are used as part of the structured formula language available to create references to Table contents

.  *  Slicers can be attached to Tables (from Excel 2013 onwards) to allow Tables to be filtered in the same way as PivotTables

.  *  Tables can be used to quickly remove duplicates from a list

.

!04 Excel Tables at excelcampus-Tutorial Beginners Guide for Windows 2007-2013& Mac 2011.mp4  16min (downloaded) (and example files)

https://www.excelcampus.com/tables/excel-tables-tutorial-video/ -------------------------------------------- 2013 10 01        Jon Acampora

10 Awesome Reasons to Use Excel Tables

  1. Automatic Formatting ------------------------           2. Organizing & Naming Data

  3. Sorting & Filtering -----------------------------           4. Auto Expansion & Navigating

  5 Total Row ----------------------------------------           6. Remove Duplicates

  7. Create Unique List ----------------------------           8. Pivot Table Integration

..9. Chart Integration -------------------------------         10. Table Formulas (Structured References)

.

!05a Shortcuts for Excel Tableshttps://exceljet.net/tips/shortcuts-for-excel-tables

https://www.youtube.com/watch?time_continue=197&v=-K_Z3OACOIA

Excel has great support for shortcuts in tables. Shortcuts for inserting, selecting, and deleting rows and columns all work much better! Watch the video to see how.

Ctrl Shift L  Toggle Autofilter -------------------         Alt <↓>  Activate filter

Ctrl T Insert table ----------------------------------         Shift Space Select table row

Ctrl Space Select table column ----------------         Ctrl <A> Select table

Ctrl Shift <+> Display Insert Cells dialog when no row or column selected

**Ctrl Shift <+>**Insert rows when a row is selected  Ctrl Shift <+> Insert columnswhen a column is selected

Ctrl <-> Delete selected rows -------------------         Ctrl <->  Delete selected columns

.

Another Way

is to insert a blank row in the middle of your list, make your entry, then select all of the entries and sort them

By using Excel to "expand" the list, Excel will adjust references to the list where ever they are.