[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Behavior of tables & data validation

Posted on 2014-01-27
1
Medium Priority
?
181 Views
Last Modified: 2014-01-27
Hello - I'm trying to automate a data maintenance worksheet that will feed pivot tables & charts on other tabs.

It already had ~100 rows, and I converted that range into a table

Then I added data validation lists as needed

But now when I go down to the first empty row to add new data, the table does not enlarge to include the new row, and the data validation lists are not on the new row.

Shouldn't the table expand to include a new row?

Is there a way to make the data validation lists effect the new row?

Thanks
0
Comment
Question by:mlagrange
1 Comment
 
LVL 19

Accepted Solution

by:
regmigrant earned 2000 total points
ID: 39812456
To copy validation to individual  cells  you can use 'copy, past special, validation'.

However you are correct that the table can expand and bring formula/format/validations with it to the new row - this is controlled by autoformat - which you can find under options, proofing, autocorrect options, "autoformat as you type"  make sure the 'extend table....' option is checked

Note that data validation will only copy if its applied to the whole table-column identically - you can't mix and match and have excel 'guess' which applies

Reg
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question