Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge Data with Query Option
I have mutiple workbooks in a folder. All workbook data is on 'Sheet1' and
have the same format. Row 1 contains headers. Column A2:A20 contains dates Column B2:B20 contains reference #'s such as 1356, 1378, 1478 etc Column C2:C20 contains data pertaining to the Reference #. How can I set up a macro on a master workbook that will pull the data from all workbooks in the folder based on the information entered on the Master Workbook. As an example, on Master Workbook, Sheet1, A:1 = 1356, then all instances of 1356 and related data from all workbooks in the folder would be displayed on the Master Workbook? Your expertise, as always is appreciated |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Merge Data with Query Option
Hi,
If all the workbooks are open you can use the INDIRECT function, but this function does not work against closed files. I suspect you are going to need to create a macro. The macro could build a formula that references the cells or it could bring in the value without links. It all depends on what you want. If you want to create formulas in cell, then open one of the workbooks and create the formula by pointing and clicking. Then close the file to which the formula is linked. Select the cell with the formula and turn on the macro recorder. Press F2, Enter and turn the recorder off. Go and look at the vba code that was created. See if you can generalize it to your overall needs. If not post the code to here. -- Thanks, Shane Devenshire "Trying to Excel" wrote: I have mutiple workbooks in a folder. All workbook data is on 'Sheet1' and have the same format. Row 1 contains headers. Column A2:A20 contains dates Column B2:B20 contains reference #'s such as 1356, 1378, 1478 etc Column C2:C20 contains data pertaining to the Reference #. How can I set up a macro on a master workbook that will pull the data from all workbooks in the folder based on the information entered on the Master Workbook. As an example, on Master Workbook, Sheet1, A:1 = 1356, then all instances of 1356 and related data from all workbooks in the folder would be displayed on the Master Workbook? Your expertise, as always is appreciated |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merge option is not available. How to undo merge in this case? | Excel Discussion (Misc queries) | |||
Parameter/Criteria option not available for External Data Query | Excel Discussion (Misc queries) | |||
VLookup to merge Access Query data into Excel Report | Excel Discussion (Misc queries) | |||
I want to merge two workbooks but the option is greyed out. Why? | Excel Discussion (Misc queries) | |||
Problem with .Background Query option of ODBC Query | Excel Programming |