Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 215
  • Last Modified:

Formatting Data Entry Template

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
Carlynne
Asked:
Carlynne
  • 6
  • 5
1 Solution
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CarlynneAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
CarlynneAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CarlynneAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CarlynneAuthor Commented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
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
 
CarlynneAuthor Commented:
Good to know that one as well.

Thanks so much!

carlynne
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now