"Auto" filling a cell...how?

[playz123]

I am new to Excel, but slowly learning how it works. One of my first projects is to try to create/recreate a Checkbook spread sheet. I did find one on line to use as an example, and have figured out most of it, but one thing I don't know how to do is as follows:
If I click on any cell, for example in the "Category" column, a little arrow pops up to the right of the cell and when I click on it, a box opens showing a list of entries from which I can choose. (See attached image). That saves typing a new entry every time in the cell. My question is: how does one create that feature? And can I then add new entries to the box as I progress? Any advice would be greatly appreciated. Please remember I am not as knowledgeable about the program as you are, so details are important. Many thanks.......frank

[Tony Valko]

See this...

http://www.contextures.com/xlDataVal01.html#list2010

[playz123]

Thank you very much, Tony. That link was most helpful and I've now been able to create my first 'linked data table'. The next step is to figure out how to do something similar for the check number column where I wish to enter a check number and then have it increase sequentially each time, and also one for the date where I can click on the arrow and have today's date inserted. So am 'getting there, but still have a few things to learn yet. Cheers!

[TheN]

Today() returns today's date.

Attach a sample workbook and someone will probably be able to figure out what you want.

[Tony Valko]

I use drop down lists in my check register for just about everything!

Let's assume column B is for the check number.

In some out of the way cell, say cell X1, enter this formula:

=LOOKUP(1E100,B:B)+1

Then, setup drop down lists in column B and use cell X$1 as the source.

[playz123]

Thanks again, Tony. Will definitely try that at my earliest convenience. Must admit I don't understand even one bit of that formula, but if it works, it won't matter. Cheers.

But before I proceed, it seems Excel is changing your formula by adding a Plus sign between the E and 100, so it reads =LOOKUP(1E+100,B:B)+1 Is that correct??

ALSO, thank you TheN; that should help me with the Date setup!

[playz123]

Haven't even reached the stage of making this column a table yet, and already have run into problems. I get an error message entering the formula and it says it's creating a circular reference or some such thing. What am I doing wrong?

[Tony Valko]

Try this...

If column A is your check number...

You'll have to manually enter the first check number so assume you enter 1 in cell A2.

Enter this formula in cell X1...

=LOOKUP(1E100,A:A)+1

Now setup the drop down lists in column A.

Assume you want to do this for the range A3:A100.
Select the range A3:A100
Goto the Data tab>Data Validation
Allow: List
Source: =X$1
OK out

Try it out...
Select cell A3 and click the arrow. The only selection will be check number 2.
Select cell A4 and click the arrow. The only selection will be check number 3.
Select cell A5 and click the arrow. The only selection will be check number 4.
etc
etc

Now that you see that it works go ahead and clear cells A3:A5.

When you enter the formula in cell X1 Excel will add the + sign.

=LOOKUP(1E+100,A:A)+1

1E100 (1E+100) is scientific notation which is a "shorthand" method for entering very long numbers.

1E100 is scientific notation for the very long number 1 followed by 100 zeros. That is a ridiculously large number!

The way that LOOKUP works is if the lookup_value 1E100 is greater than all the
values in the lookup_vector A:A it will match the *last* value in the
lookup_vector A:A that is less than the lookup_value 1E100.

There is a 100% chance that the lookup_value 1E100 is greater than any number in column A (check number) so the formula will always return the last (bottom-most) number from the range A:A and add 1. Thus, the drop down list will always have 1 selection and that selection will be the next check number.

[playz123]

Success! Thank you Tony. That was of enormous help. I don't quite understand some of it, like why a $ sign is used when sourcing the 'List' in X1, but that doesn't matter right now. Have also discovered that if I want checks listed out of sequence, I can't enter them that way. Since I write so few checks these days, perhaps it would be simpler just to enter the numbers manually each time. Nevertheless I now have options. Next step is to create the Date entries, but I think I can figure that out...at least I hope I can. And I've learned a lot today thanks to your tutorial and the link you provided...all safely stored for future reference. Cheers............frank

[playz123]

Will mark this as Solved and start a new thread if I run into more problems.

[Tony Valko]

Good deal. Thanks for the feedback!