Solved

Formatting Data Entry Template

Posted on 2011-03-20
13
203 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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
 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

776 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