List to auto add new numbers as needed

[Bond00]

So there is a list of invoices with some data to the side for each, as a invoice # is used and data is added for it, i'd like a new one to be added to the list in col A.
Is there a way to do this without vba or no? Would rather not make this doc a .xlsm if i could help it.
Also keep in mind other xlsm docs will reference this list for automation in other workbooks. It will check what the next available # is and input data next to the one with empty lines in the data fields.
I cant reference D1 in the A col or it errors with a circular reference.

Book1
	A	B	C	D	E	F	G
1	Last value:	INV000009	Next value:	INV000010
2
3	INV000001	data	data	data	data	data
4	INV000002	data	data	data	data	data
5	INV000003	data	data	data	data	data
6	INV000004	data	data	data	data	data
7	INV000005	data	data	data	data	data
8	INV000006	data	data	data	data	data
9	INV000007	data	data	data	data	data
10	INV000008	data	data	data	data	data
11	INV000009	data	data	data	data	data
12
13
Sheet1

Cell Formulas
Range	Formula
B1	B1	=LOOKUP("ZZZZZ",A3:A17)
D1	D1	=LEFT(B1,3)&TEXT(RIGHT(B1,LEN(B1)-3)+1,"000000")

 

[jkpieterse]

You could try this:

1. Add headings to your table in row 2
2. Format your range as a table (Home tab, Format as Table gallery), check the box that your table has headers
3. Empty column A of the table and type this formula into cell A3:

   ="INV"&TEXT(ROW()-ROW($A$2),"0000000")

4. your table should look similar to this:
   
   
5. To add a new invoice, simply click on the bottom-right cell of the table and hit the TAB key.

 

[Bond00]

Neat that it does that with that formula but it seems to always keep the order so if i decide to use the table to change a different column with A-Z it doesn't work and messes up the data because col A is always in order with A-Z with that formula.

 

[jkpieterse]

You could make it a habit to copy, paste special values the newly added invoice number. The formula will automatically reappear on subsequently added new rows. If you need more control, use of VBA is probably necessary

 

[Bond00]

Ok what i think i could do for this is just have a really long list already made on col A and just search the last col B without data in it and then just work from that. No need to auto add new invoice #s as it goes i guess, it wont hurt if they are already preexisting already.