File Names

[Guest]

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?

 

[Ronald Dodge]

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.