Auto Change Formulas - Please Help.

  • Thread starter Thread starter bartendersunny1
  • Start date Start date
B

bartendersunny1

Ok, I'm making an invoice tracker, I use a template to create the invoices and I fill in and name each sheet the invoice # ex. IN12345 (and I place the name of the invoice into the sheet in cell A1). I keep all of theseinvoices in a folder labeled invoices. I need to get certain info off of them and placed on a common sheet. I need the spreadsheet I'm making to auto populate the info each time I drop a new invoice in the invoice folder. I can't get it to change the invoice # automatically. I can paste the formulas all the way down.

Also I have a range of cells that I need it to sum in addition to change the reference of the name of the sheet.

I also need a column in this tracker to update the date of which the line row was added to the sheet.

Any help is highly appreciated!
 
I have several invoicingpoint-of-sale custom apps that stores the next
invoice number in the workbook for the period being invoiced (ie:
month, qtr, or fiscal year). This is stored in a CustomProperty named
"NextInvNum" as a number that gets appended to the invoice numbering
scheme. (In my app invoices are, by default, prefaced with the calendar
year followed by a hyphen and the invoice number) All invoices are
stored in a fiscal period file which, in most cases, is an entire
calendar year. Here's my file naming format...

CompanyName_YYYY_Invoices.xls

...where invoices are added whenever new ones are created for the
current year.


How it works:
An invoice template is stored as a XLT and used for adding new invoices
to the fiscal file via Sheets.Add Template:=gsDefaultInvTemplate.

The app reads the value stored in the CustomProperty named "NextInvNum"
and inserts the new invoice number into the range named "InvoiceNum",
then sets the sheetname to the same value, and finally sets the
CustomProperty value to the next invoice number. The invoice number
scheme is simply...

YYYY-###

...which can be modified to fit the user's scheme however desired. (The
default concept precludes that the year is the current calendar year,
and the delimiter is a hyphen) The actual format of the number digits
used is determined by the next invoice number. For example, #1000 will
display as 2013-1000, where the previous invoice number was 2013-999.
The first invoice number was 2013-001.

Each January the app will create a new fiscal file for the new
calendar year. The template.xlt does not have a CustomProperty; this is
added when a new calendar year file is created.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
(e-mail address removed) has brought this to us :
Ok, I'm making an invoice tracker, I use a template to create the
invoices and I fill in and name each sheet the invoice # ex. IN12345 (and I
place the name of the invoice into the sheet in cell A1). I keep all of
these invoices in a folder labeled invoices. I need to get certain info off
of them and placed on a common sheet. I need the spreadsheet I'm making to
auto populate the info each time I drop a new invoice in the invoice folder.
I can't get it to change the invoice # automatically. I can paste the
formulas all the way down.

Also I have a range of cells that I need it to sum in addition to change the
reference of the name of the sheet.

I also need a column in this tracker to update the date of which the line row
was added to the sheet.

Any help is highly appreciated!
Click to expand...

If you were to adopt the approach I described in my other post you
could store the info you need from each invoice on a "Summary" sheet in
the same file as the invoices. (I suspect, though, that you have gobbs
of disk space and don't care that each invoice is stored in its own
xls)<g>

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Back
Top