Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 dont 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 didnt know better started us down this path and we cant change now. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reference constant cells in different files from a master workbook
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 copypaste 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 copypaste values. Remember to copypaste 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 copypaste 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 copypaste 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 dont 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 didnt know better started us down this path and we cant change now. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reference array constant values in a formula | Excel Discussion (Misc queries) | |||
Array Constant: How do I reference each value in a formula | New Users to Excel | |||
How do I set up a constant reference to a cell that moves? | Excel Worksheet Functions | |||
excel uses what technique to keep a cell reference constant when | Excel Discussion (Misc queries) | |||
Multiple workbook user's with Master workbook - all password protected | Excel Discussion (Misc queries) |