File Names
I have a rather large spreadsheet that calculates a lot of data for me that I
manually fill in the data fields. I want to create a template that users would fill in data, then they would save the file with a file name using our invoice number as it's file name. They would send me that spreadsheet. My master spreadsheet would take the data from that file and do the calculations. Problem is, I don't want to have to enter EVERY possible file name in my cell formulas on the master spreadsheet. These file names would simply be consecutive numbers (example 12733.xls 12734.xls. etc.). Is there a way to cause each line on my master spreadsheet to automatically advance the file name to the next spreadsheet file? |
File Names
There's 2 things that comes to mind that you should think about. While
having the invoice number as the file name is a way to make each file unique, you should think of having some sort of a naming scheme that you can use to be able to differentiate these workbooks from other workbooks via macros, such as: INV<Invoice Number.xls Example: INV12733.xls If you really don't want to change the naming scheme, an alternative to this would be to setup a folder to hold specifically these invoices for when they need to be populated into your master worksheet. It would be then for the macro to open these workbooks and then perform the tasks that needs to be perform to be populated into your master file. The reason why I said macros, which leads into the 2nd item to think about, this isn't really doable strictly from formula writing, given formula writing doesn't give you what workbooks are openned for that particular instance of Excel. For this reason, you will need to use macros to at the very minimal to at least put the file name within a particular cell of your master worksheet, which then you would use either the ADDRESS function and/or the INDIRECT function. Example: Cell 'B5' of the 'Process' worksheet is to hold the name of the workbook that is the current invoice workbook being worked on by the spreadsheet. Let's also say the name of the worksheet in the Invoice workbook is "Invoice", which is set as part of the template. Here's how the formulaes could look: =INDIRECT("[" & Process!B5 & "]Invoice!B6") =INDIRECT(ADDRESS(6,2,,,"[" & Process!B5 & "]Invoice")) In this example, it looks like INDIRECT by itself is more efficient, but there are times when I use the ADDRESS function, such as when I need the formulaes to be more dynamic. In those cases, I use the ROW() and COLUMN() functions most of the time within the ADDRESS function. I don't know how much of macro writing you know if any, and also not sure which way you are going to go, so that's why I didn't go into details on the macro side. -- Ronald R. Dodge, Jr. Production Statistician/Programmer Master MOUS 2000 "garyntn" wrote in message ... I have a rather large spreadsheet that calculates a lot of data for me that I manually fill in the data fields. I want to create a template that users would fill in data, then they would save the file with a file name using our invoice number as it's file name. They would send me that spreadsheet. My master spreadsheet would take the data from that file and do the calculations. Problem is, I don't want to have to enter EVERY possible file name in my cell formulas on the master spreadsheet. These file names would simply be consecutive numbers (example 12733.xls 12734.xls. etc.). Is there a way to cause each line on my master spreadsheet to automatically advance the file name to the next spreadsheet file? |
All times are GMT +1. The time now is 02:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com