Help with making formula that allows me to use data from an unopened worksheet

**BRDavie** · 11-06-2013, 07:55 PM

Hello!

I am fairly new at this, so please forgive me.

I am trying to make a worksheet that will insert data from other worksheet that are saved on my computer. These files are all saved as "Ticket #3405", where the number changes with each ticket. The next file is saved as "Ticket #3405" and so on. I want to create a main worksheet that takes all the pertinent info from these worksheets and automatically inserts them. Essentially, I need to write the ticket# in a column on this main worksheet and have all the info populate other fields in this worksheet. How do I make the worksheet access info from saved spreadsheets on my computer? And how do I make the formula change to access the correct spreadsheet when I type in the ticket #?

Thank you,

Brandon

**bmxfreedom** · 11-06-2013, 09:14 PM

Unfortunately, there is no way to indirectly reference cells in a closed workbook, but with the consistency in file names and locations, it is possible to accomplish this through the use of a macro in the worksheet change event. Try this:

Please Login or Register  to view this content.

This code assumes that the main worksheet that you want the formulas in is saved in the same location as the ticket files themselves. If that is not the case, you will need to change the path as appropriate. It also assumes that the ticket numbers you enter are in column A. Insert this code into the sheet where you enter the ticket numbers, and it will create a formula to the right of the ticket number as you enter it that references the value in cell A1 of that ticket number. All of these parameters can be changed by changing the parts of the code that are : FrmlaCl = Cells(Target.Row, Target.Column + 1).Address to put the formula in a different cell, or Sheet1'!$A$1" to reference a different location in the ticket spreadsheet. It also assumes that the ticket spreadsheets are saved in the .xlsx format. If not, you would need to change "".xlsx]" to whatever format it is, whether ".xls" or ".xlsm" or any other excel format...

I hope this helps.

**BRDavie** · 11-07-2013, 06:45 PM

Thanks! This will be very helpful once I fully understand it. A few questions,

1)If the main worksheet is not saved in the same location as the ticket files, how can I change the path as appropriate?
2)How can I change the FrmlaCl = Cells(Target.Row, Target.Column + 1).Address part of the formula to move it away from column A

Thank you!

**bmxfreedom** · 11-07-2013, 08:35 PM

All of the code in this workbook relies on the files being in the same location, though you could just replace ThisWorkbook.Path with the path of the ticket files.
The frmlaCl can be distanced from column A by changing Target.Column + 1 to Target.column + 2, for 2 columns away (C), or 3 for 3 columns away (D), or you can get rid of Target.Column completely, and just specify whatever column number you want; i.e., if you want the formula in column F, replace "Target.Column + 1" with the number 6.