Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Change Formulas - Please Help.
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Auto Change Formulas - Please Help.
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[s] 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto-adjust formulas | Excel Worksheet Functions | |||
auto extend formulas | Excel Worksheet Functions | |||
Using formulas along with Auto Filters | Excel Programming | |||
Auto add of rows and formulas | Excel Worksheet Functions | |||
auto fill of formulas | Excel Discussion (Misc queries) |