Solved

Formatting Data Entry Template

Posted on 2011-03-20
13
202 Views
Last Modified: 2012-05-11
Hi,

If I am making a blank data entry template, and I do not know in advance how many records will be entered, how can tell Excel 2007 to apply my data validation rules and drop down lists to any new record added?

There must be a way to do this without copying all the data validation rules into hundreds of blank cells in advance.

many thanks!

carlynne
0
Comment
Question by:Carlynne
  • 6
  • 5
13 Comments
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35177971
Hello,

Use an Excel Table. Start with a table consisting of the header row and at least one row of data. Apply your formatting and formulas to the data row(s), then click any cell and click Insert > Table.

If new data is entered in the next empty row beneath the table, formatting and formulas will be applied to the new row.

cheers, teylyn
0
 

Author Comment

by:Carlynne
ID: 35178025
Hi Teylyn,

I was hoping you'd respond:) Actually, I got this idea from you originally, but when I tried to follow your instructions, I can't get it to work. So, I think I must be doing something wrong.

When I set the data validation rules, I have to select how many rows to allow the data validation, right? So, I'm back at square 1, where I am predetermining how many rows will be entered.

If I understand correctly, the insert table function just selects how ever many rows I predetermine for the data validation rules so it's not really solving the original problem, unless I am misunderstanding something.

I hope this makes sense. Thanks so much for your help.

carlynne
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35178150
Hello Carlynne,

it seems that you want to set up a data validation list for a field in the current table, where the source for the data validation is also in the current table. Is that right?

Or are you concerned that the data validation will only apply to the existing cells? If data validation is set up correctly, it will apply to new rows in a Table.

Can you throw together a sample table that illustrates what you want to achieve, upload the workbook and explain in context? Then I (or someone else) will be able to guide you through the process.

cheers, teylyn
0
 

Author Comment

by:Carlynne
ID: 35178212
Hi Teylyn,

Yes, when I try to insert table, the data validation rule only applies to the cells that I selected for the data validation rule.  I will give people an empty template, and I won't know in advance exactly how many records they will enter.  I will usually just estimate how many records and then just apply the data validation rule to say 10,000 cells to be sure to cover the maximum possible records. However, you said before that it's not good practice to do this., so I'd like to learn the better way.  

Thus, I would like to set up the data validation rules so that they are applied whenever a new record is entered.  I have created a sample practice template of the sort of thing I want to do. I set data validation rules for the first 5 cells for each column. I don't know how to set up data validation rules without first telling Excel what cells to apply the data validation rule so if there's a way to do, I'd like to know how to do this.  

See attached.

carlynne
Practice.xlsx
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst earned 250 total points
ID: 35178226
Hello,

steps I took in the attached file:

- I entered data in row 2, using the data validation lists where applicable
- clicked in A2
- clicked Insert Ribbon > Table and accepted the defaults with "My table has headers"
- clicked A3 and the data validation list is there.

Please let me know if that does not work for you.

cheers, teylyn
Copy-of-Practice.xlsx
0
 

Author Comment

by:Carlynne
ID: 35178481
Hi Teylyn,

Yes, validation rule is there for the first 5 records, but after that it's not there. So we are still back in square 1 or am I  missing something here?

thanks for your patience.

carlynne

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35178492
I don't understand.

As long as you enter new data in the next empty row immediately below the table, the formulas and data validation and formatting will be applied to the new row.

If you leave a blank row, and enter data in a row not directly beneath the data table, then you will not see these settings applied.

Can you post a workbook that shows that the data validation does not work after 5 rows?

cheers, teylyn
0
 

Author Comment

by:Carlynne
ID: 35178508
Ah, okay. I see. I have to enter the data first and then the drop down arrow will come. I thought if I just clicked in the cell the drop down arrow would appear as it does with the first five records.

Thanks so much. Sorry for confusion.

carlynne
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35178524
Yes, when you have a data validation drop-down in column A, it can be tricky to enter a new row starting with column A. But as soon as you enter any value into one of the other cells of a new row, the data validation cell will be showing the drop-down.

Whenever I build a table like this, I try to make column A a type-in value rather than a drop-down value to avoid this issue.

cheers, teylyn
0
 
LVL 50

Expert Comment

by:Ingeborg Hawighorst
ID: 35178528
You can also just click the next row in column A, hit Ctrl-D to copy the value from the row above, and then use the drop-down in the copied cell to start your data entry.

It's a bit clunky and I'm sure it's a bug. I've tried to find other solutions, but so far have not been successful.

cheers, teylyn
0
 

Author Comment

by:Carlynne
ID: 35178536
Good to know that one as well.

Thanks so much!

carlynne
0
 
LVL 24

Expert Comment

by:broomee9
ID: 35821679
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
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…

932 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now