Dynamic Creation of Invoice number

[Guest]

LOL
Please don't use the forums at DSLreports.com as a way to exchange files !
If you want help from members there, fine, but don't just post a file like
that, explain the problem...

Please go back there and ask the moderators to move it to the "/dev/nul" or
to delete it, if you don't intend to ask help from members there about this
subject.

The forum I used to pass you my file is self deleting daily, but I probably
shouldn't have use it for that anyway...

I will take a look at your file tonight and post back.

Thanks,
Luc.

 

[Guest]

Are paid invoices ever removed from the sheet ?

You have formulas in your table, you should avoid that for
"accounting/history" data.
Normally, that kind of data should not be tempered with manually (editing,
sorting, etc.).

I am trying to understand how that sheet will be use to give you better
advices...

 

[Guest]

Hello again,

Hmmm...good question, I assume that I would have one sheet per year. All I
really need from this sheet is that it generates a new proposal number since
some aren't accepted and so I can track the invoice numbers vis a vis
proposals.

My real accounting data is in my accounting program, this is just a quick
reference for fun, redundant really but since it's portable and I don't have
to produce reports it's easy. In the final version I'm going to lock all the
cells that are formulas...the spreadsheet isn't finalized I haven't done it
yet.

My main focus is to learn how to create the input tool and append the final
row of the main invoice table with a new ID and a generated invoice number
based by client. I know there's a lot of redundant information here but it's
by design.

Thank you again.

 

[Guest]

Still, your table is useless if you can't trust the information in there.
Locking cells with formulas is not making those cells safer.

The problem is that you want to play with the table, sorting, direct
editing, etc.

For example, with the table you posted at DSLR, if you sort the table on the
"Cliente" column, your ID column is screwing up. The rest of your current
formulas are ok as they are now when you sort, but if you ever change them or
make a mistake in your manipulations, they can become unreliable.

What I would do is create an "editing" area (1 row) where you build new rows
and edit existing ones. The table would contain only values (no formulas) and
the insertion/replacement in the table would be controled by macros. My
sample file was doing that for new rows. It would be relatively simple to
modify to add the "extracting/editing" capability.

If you prefer to work directly in the table, the formula to build the
proposal number has to be put in a cell out of your table and its value
copied (by maccro or manually) in the new row. Putting that formula directly
into the table is unreliable, if at all possible.

In your sample file, C9 is looking for a named reference "client_current"
that you haven't define. It shoul be pointing to $B$9. The formula also fails
because you have entries in that column not respecting the format you
specified (blanks and "-------").

You need to document yourself about buttons and macros. Look for "ActiveX
control" in the Excel Help system...

Let me know how it evolves and feel free to ask questions...

Regards,
Luc.