Link to home
Start Free TrialLog in
Avatar of Carlynne
Carlynne

asked on

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
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

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
Avatar of Carlynne
Carlynne

ASKER

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
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
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
ASKER CERTIFIED SOLUTION
Avatar of Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

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
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
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
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
Good to know that one as well.

Thanks so much!

carlynne
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.