Reference constant cells in different files from a master workbook

[Byron]

We need to have master spreadsheets that summarize value from different sets
of identical excel spreadsheets. We have a multi-tab template spreadsheet
that gets filled in with the data for each shipment and saved. The layout is
exactly the same for each shipment and only item serial numbers change. We
then generate another summary spreadsheet report periodically that
consolidates the serial items and numbers from the individual shipments.

Right now we are just pasting the serial numbers from the individual
shipment spreadsheets into the summary spreadsheet. I know I can reference
the cells in the other files, but I don’t want to have to edit the file names
in every referencing cell for each new summary report. What I would like to
be able to do is have an area of the spreadsheet where I can enter the
variable spreadsheet file names once and have that name change throughout the
summary spreadsheet.

So, this week we have 2 shipments stored in excel spreadsheets detail1 and
detail2. We change some cells in excel spreadsheet summary1 and the data is
pulled from detail1 and detail2. We save summary1 and report on it.

Next week we have 3 shipments stored in excel spreadsheets detail3, detail4,
and detail5. We change some cells in excel spreadsheet summary2 and the data
is pulled from detail3, detail4, and detail5. We save summary2 and report on
it.

I understand the variable number of shipments report on will cause issue as
well, but right now I just want to get the dynamic file reference addressed.

And, yes, I know we would be better served using a database, but as usual
someone that didn’t know better started us down this path and we can’t change
now.

 

[Squeaky]

Hi Byron,

I have a 2-fold solution. It sounds complicated but once set up it is not.
Experiment on a copy.

If you use a regular formula to pull the information from the other
spreadsheets, like =sheet2!a1 you can use the indirect method instead. I am
using columns J and K to put in formulas. Please adjust accordingly.

The setup:
In cell J2 I put the name of the sheet as it is on the tab that you want to
get info from. I will use Sheet2.
In cell k1 I put the number 1 to start. In k2 I put =k1+1, then dragged it
down the list, so the numbers in K increment by 1. If you want to hide column
K, put =J2 in K1, then you can change the number in J2 instead.

On sheet1 in the first cell you want to pull data into put: (I am assuming
the info in sheet2 is starting at the top of the sheet in column A)

=INDIRECT($J$2&"!"&"A"&K1)

In the formula change the "A" to be your column on sheet2.

Cell J2 on sheet1 will contain the name of the sheet, which you can modify.
K1 will pull the number in cell K1, and that stands for the ROW the first set
of information is in on Sheet 2. So if your data starts at Sheet2 D100, you
will want to change the "A" to a "D" in the formula, and put 100 in cell K1
on sheet 1.

Dragging down the formula will pull the info from subsequent cells. The K1
will change to K2, K3, etc, so the indirect formula will increment.

Once you have your data, highlight the cells you got data into and then
copy>paste values to clear the formulas in only the cells you got data from
on sheet2. Remember to keep the formulas below that point. So when you change
J2 to Sheet3 and K1 (or J2 if you set it up that way) to the row the first
number is in on sheet 3, it will pull the info below where you did the
copy>paste values. Remember to copy>paste values when you are finished with
each sheet or the data will change when you change the sheet name. (You can
set the shortcut icons on your toolbar for quick usage.)

So when this is done, you will only need to put in the sheet name in J1, the
beginning row in K1 (or J2), and copy>paste values when you are done with
that sheet. Drag the formulas there and in the K column down far enough.

There are other ways to handle it so you won't have to copy>paste values,
but that is the easiest way, and it prevents your spreadsheet from getting
bogged down with too many formulas.

I hope this is not too confusing. Let me know if you have questions.

Squeaky