Reference constant cells in different files from a master workbook

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.


0
Utf
11/14/2009 2:54:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

1 Replies
703 Views

Similar Articles

[PageSpeed] 27

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

"Byron" wrote:

> 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.
> 
> 
0
Utf
11/18/2009 5:58:11 PM
Reply:

Similar Artilces:

Removing characters from cell
Hope someone can help me with this one.... I have text in column "A" a list of parts like the following..... ...nhg1234 ..nhg1235.54 nhg3456 ....nhg1253.7 Is thier anyway of removing the dots in front the part number? I dont want the points moving after the the main part of the number. Please help TiA mag()() Do you part numbers all start with nhg? If yes, you could select column A edit|Replace what: .n with: n replace all And keep hitting the replace all button until all are fixed. "Mag()()" wrote: > > Hope someone can help me with this one.... >...

Can I delete a file that is in use
I have a malocious file called sdra64.exe it's in my appdata\roaming folder I can't delete it because it isays it s in use by another program How can I set it up so that it is deleted somehow? Thanks I have a malocious file called sdra64.exe it's in my appdata\roaming folder I can't delete it because it isays it s in use by another program How can I set it up so that it is deleted somehow? Thanks You can try using the task manager. Bring up the manager and go to processes and stop it from running. You can try to delete from Safe Mo...

How to count cells
Hi, I want to put a summary of Job orders in a table. (Excel (Zip) file attached) I am having a problem of total. I want to make a total of only approved job orders not the all job orders. Also if the column contains hot / cold type, I need red and bold for hot job order of complete row. Also I want to be counted by job type and entity. For example, in the total approved 7 permits, CONST entity 3 cold jobs and 1 hot job. how can i display in the abstract table. Can anyone help me please. +-------------------------------------------------------------------+ |Filename: count.zip ...

Function to encapsulate code block into a constant?
I writing more and more apps where the application generates an Excel spreadsheet and pushes various VBA routines into it. I seem to be spending too many man hours on the details (placement of vblf's, "_", quotes, and such when creating constants. I'm thinking I should be able to develop the routine in an Excel spreadsheet, test it... and then feed the routine to a function that returns a monster constant like the one below - even checking for excessive continuations and breaking it up into multiple constants as needed. For example, this: --------------------------------...

hyperlink to existing excel file
When I create a hyperlink to an existing excel file and then click on it, explorer opens to the folder that the file is in instead of the excel file opening. Just to test I pointed it to a word document and the doc file opened fine. How do I get a hyperlink to open the excel file? Thanks. Are you sure it isn't to the folder instead. Only way I can reproduce that is if I select the folder instead of the file when I create the hyperlink. As expected if I use a hyperlink to a particular file it will open.. -- Regards, Peo Sjoblom "purplehaz" <software@for.me> wrote i...

Save for Web (.htm) file
I have two similarly structured XLS file. One file "A" is much longer...perhaps 2000 rows long...when saved as a htm file it is around 500KB. I take another XLS spreadsheet that looks like the same structure...rows and colums...only about 560 rows...when I save this as webpage .htm it ends up being 11.2MB. What the heck happened? How do I get it down in size. There is no apparent reason why a smaller file ends up being 11 time larger that the smaller file when saved! Thanks, --confused ...

Importing into Excel from a Text file
We have a text database of about 7,000 rings. It is in a fixed format. Unfortunately some where we have about 4 or five rings whose format is screwed up. We figured by importing to Excel, we should be able to find where the records are to fix them. But, we just can't get the following format to import into excel. If anyone can help us, we sure would appreciate it! Thank you Greg Gates www.ringdesigner.com Each record begins with Begin Product Each record endswith EndProduct a semicolin seperates each record Also there are para symbols at the end of every line Herer are a...

Unpopulated Cell Address
Hi I'm a newbie and was wondering can anyone help with my query. I am trying to find out how to print a spreadsheet with just the data that is contained in certain cell address's. Where a cell address is not populated I want the whole row to not appear in a printed copy. Any ideas? Try this idea Sub hiderowsandprint() With Range("a1:a21") .SpecialCells(xlCellTypeBlanks).Rows.Hidden = True .Resize(21, 8).PrintPreview .Rows.Hidden = False End With End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Mick Smith" <notsa...

change the formula by changing contents of cell
I have a table that ranks a list of players by their statistics, for example, when I change cell B2 to "walks", my formula (large) lists the player with the most walks in cell B4, 2nd most in B5, 3rd in B6... that works well because more walks are good. But, when I change cell B2 to "strikeouts" I want it to list the player with the fewest number of strikeouts in B4, next fewest in B5 and so on. Is there a way to change the "large" function to the "small" function within the formula? I have set up a helper cell (C2) that changes fro...

point exchange to a different priv1.edb file
I have a snapshot of my priv1.edb file on a different drive. I know that exchange 2003 lets us me the priv1.edb file to a different location if the file isn't already there. Is there a way to have exchange point to a different priv1.edb and use it? On Thu, 5 Oct 2006 21:31:01 -0700, Han <Han@discussions.microsoft.com> wrote: > >I have a snapshot of my priv1.edb file on a different drive. I know that >exchange 2003 lets us me the priv1.edb file to a different location if the >file isn't already there. > >Is there a way to have exchange point to a differ...

Preventing Column Cell Values from Printing
I have a summary worksheet with several columns of information. There is one particular column (E25:F39) where I don't always want to print the values. Is there some simple way to toggle this column's values on and off just before hitting the print command? I was thinking of using an adjacent tick box outside the print area, which when ticked would print the column values, and vice-versa. I'm looking for a formula that would examine the tick box, and if the tick (letter 'a' in Marlett font) is not present (i.e. cell value = ""), then the values in column (E25:...

How do I change the value in cell based on a future date
I would like certain cells to be cleared after a certain date one way: Put this in the ThisWorkbook code module (Right-click the workbook title bar and choose View Code): Public Sub Workbook_Open() If Date > #12/15/2004# Then _ Sheets("Sheet1").Range("A1,B2,J10,K39").ClearContents End Sub Note that this won't work if the user opens your workbook with macros disabled. Note also that there's no way to prevent a user from accessing your data - i.e., they can set their clock back and open the file. You can make things more difficu...

IF COUNTIF & COUNTA on Filtered Visible Cells #7
Hi Frank, Hope this will help. Correction to Last Posting: My Helper Column "U" increments one Row at a time and says: Check from Row above Current Row back to beginning of my "V" Range: i the Room was used previously give me the Last (MAX) time it was used b returning the relevant Row Number of the (text based) Group Nam (Executive, Trainees, Manager, Graphics, etc.) that used the Room Last The Helper Column "U" then passes this data to the Formula in Colum "T" - it checks for the criteria within the specified Range and doe the COUNT calculation us...

Hyperlink Error
I have a hyperlink set-up for a netwrok file. It works fine, but when the file is copied the hyperlin chages to the folder that the file has been copied to. original link - L:\folder\folder\file Changes to - C:\file How can I get it to keep the original link? Justin ...

getting a single line from a file
I was wondering if someone could tell me how to read a single line from a text file. I tried using getline, but my MFC program does not recognize the getline function. Nor does it recognize <string> or <string.h> or using namespace std. So is there another function that I can use in MFC? Roger Take a look at the CStdioFile MFC class. -- Cheers Check Abdoul [VC++ MVP] ----------------------------------- "R.H." <roger.huggins@intermec.com> wrote in message news:e98o6IMmDHA.1764@tk2msftngp13.phx.gbl... > I was wondering if someone could tell me ho...

Data within a cell
If names are in cells as "last name, first name, middle initial" can a macro be run to change the order to "first name, middle initial, last name"? A formula will do Assuming you have spaces between commas and names, =MID(A2,FIND(",",A2)+2,FIND(",",A2,FIND(",",A2)))&RIGHT(A2,FIND(",",A2,FIND(",",A2)))&", "&LEFT(A2,FIND(",",A2)-1) Then just copy down. If you want this more permanent, copy the cells with formulas, right click, paste special. -- Best Regards, Luke M *Remember to click &q...

Data entered from list automatically enters number in another cell
I am using Excel 2007 and here is an simplified example of what I need. Items Price Potatoes 4.35 Apples 5.55 Oranges 7.95 Onions 4.55 Carrots 3.75 Items Column is List for Valid entries in table below Prices are the numbers I want entered when I enter the Item If I enter Oranges from Dropdown List in A16 , I want Excel to automatically enter the number 7.95 3 columns over in D16,or if it is quite a bit easier, just 1 column over in cell B16 Example of table I want: My Entries: Excel Enters ...

Transfering a changing cell value progressively through a workbook
I am creating a payment application form in excel 2007 using windows 7. Each worksheet represents one months invoice. Say I have a formula in "sheet 1/ cell Q7" that sums the total billed to date for a particular budget line item. This value will be transfered to a the next months payment application "sheet 2/ cell K7" this becomes the total amount of previous applications, then "sheet 2/ Q7 is =sum(K7,M7,O7) this value is transfered to "sheet 3/ K7, and so on and so on, until the completion of the job. How do I acomplish this. -- Thanks Mike ...

Summing of Different sites within a day
Hello! I have an employee attendance database for 2 sites, FL and AZ. My report is sorted first by date, then by site then by reason (there are 8, e.g. "Vacation") I have a sum in each Reason footer that gives me the total number of hours everyone at that site was out for a particular reason. I also have a sum in each date footer that gives me the total number of hours everyone at both sites was out for all reasons. What I need is this sum in the date footer to be broken up by Reason, regardless of site. Thank you for your help! I don't get how a "...

overwritten file
I have a client that overwrote an excel sheet. He did not have autorecovery on. Is there any way to recover this document? Sorry, no. -- John johnf202 at hotmail dot com "mark" <mark@mwts.cc> wrote in message news:4c7501c356ae$89ae8500$a001280a@phx.gbl... > I have a client that overwrote an excel sheet. He did not > have autorecovery on. Is there any way to recover this > document? Backup file is pretty much his only option if one exists. Has he perhaps sent the file anywhere else that he can get a copy back from, or if so he could possibly still have a ...

File Size #4
Using Windows XP and Office 2007 Is there a reason that Publisher files keep growing in size? We have a weekly bulletin with some static information and some information that changes from week to week. On Monday, we open last week's publication, delete the no-longer-relavent text boxes and pictures, and add new text boxes and pictures for the current week. That has been our standard practice. I just noticed that the file size is increasing exponentially which leads me to believe that even though I delete things, there is still some sort of "ghost" material left in the...

Preventing Excel files from being moved or deleted
What permissions setting is used to prevent users from moving or deleting an Excel file on a network? This is more of an network operating system setting question. I would think putting the file on a share that the users only had readonly access would be enough. But this would mean that the users could still copy (not move) the file. And they wouldn't be able to update the workbook. bg500 wrote: > > What permissions setting is used to prevent users from moving or deleting an > Excel file on a network? -- Dave Peterson All explained in Start>Help and Support "p...

Windows Easy Transfer won't load files?
I ran Windows Easy Transfer on my laptop with Windows 7 RC1 to save and move my profile data because Win 7 RC is shutting down. I am trying to install the Windows Easy Tranfer data to Windows Vista Business. Each time I try it won't read the file but keep asking for the drive with the data to be loaded. I tried a test by moving the the data (.MIG) files out of the folder I saved them to and put them in the root of the drive but that didn't help. I tried a test of copying the files to the root of C: and now it actually sees the file for the short test at home I trying in t...

publisher 97 won't import a file graph of acceptable quality.
To MSFT: Let me try this again. I have Pub 97 that I must use for writing a manual. I try to import a black and white graphic-a chart, in jpeg or gif or whatever,but the graphic is of such poor quality that it can not be used. This only happens in my Publisher program. I have tried to change the adapter accerleration reading, but it does not help. Does anyone else have this problem with the older version of Pub 97? Do you have this problem with later versions of Publisher? Any ideas are appeciated. Check your graphics filters. Might have some corruption going on. They are in a folder sim...

List of File Extensions
Is there a list somewhere that contains the file extensions used in Office 2003 applications? I need to backup files before re-installing my OS. These include files that store settings, customizations, macros, Outlook address books, etc. ...