Forum Discussion
Help creating database index formula
Hi there,
I'm trying to wrap my head around how to go about this one, but I'm stumped. Maybe it isn't possible.
I've created an individual file template for each piece of equipment we own. I want an index with information pulled from the same cells in each document. Each file is saved under the item's asset tag number (ie. CRLP1001.xls, CRLP1002.xls..etc)
What I'm trying to do is this: I want to be able to enter the asset number into column A and have the subsequent 8 columns insert that number into the existing formula. maybe something like this:
='T:\Equipment files\[(=A31).xlsx]Sheet1'!$D$4
the hope being that from there for every new piece of equipment added it's simply a matter of entering the asset number into the first column and the rest will self populate.
thanks for you thoughts
Terri
The INDIRECT function allows you to use a string which matches a direct reference and subsequently pulls the information from that reference. However, in order for this to return the value it requires the file in question to be open in Excel.
That being said, if your equipments files are identically laid out you can use PowerQuery to pull in their information. For this the equipment files must be in the same folder too.- Click the Data tab, New Query drop-down, From File, From Folder.
- Navigate to your folder and click OK
- A screen opens with (a sample of) the files in your folder. CLick "Transform data"
- In the next screen a small table shows which has "Content" as a first column and values listed as "Binary". Click on the small Icon to the right of "Content" which says "Combine files" if you hover your mouse over it.
- Excel shows another screen which lists the worksheets in the files. Click the one you need to pull data from and click OK.
- From here it is up to you to figure out what next steps to take.
Not exactly what i'm trying to do - i only want about 6 cells worth of info in the index, where as the individual file holds many records, pictures etc. I want the index to be updated automatically whenever one of the individual files are changed as well.
thanks for your help Jan
- If you can modify the individual files, what would help is insert a sheet with a simple table which lists the values that you need to pull out, using formulas. Then using the solution I mentioned above would work beautifully. Change a file (or add or remove one), open the overview book and press refresh on the data tab.
