Solved

Formatting Data Entry Template

Posted on 2011-03-20
13
208 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
13 Comments
 
LVL 50
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
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
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 

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 (Microsoft MVP / EE MVE) 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
 
LVL 50
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
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
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

623 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