Calgarypuck Forums - The Unofficial Calgary Flames Fan Community
Old 12-19-2016, 04:13 PM   #1
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default Excel Lookup Help

Hey all,
Looking for some expert advice here.

So I have a bunch of forms filled out in workbooks in a bunch of random folders:
Filepath1\Form1.xlsx
Filepath2\Form2.xlsx
Filepath1\Form3.xlsx
Filepath3\Form4.xlsx
Filepath2\Form5.xlsx
etc.

So currently I have a workbook containing the file names i.e. the sheet contains:
Filepath1\Form1.xlsx
Filepath2\Form2.xlsx
Filepath1\Form3.xlsx
Filepath3\Form4.xlsx
Filepath2\Form5.xlsx

How do tell excel to, in Column B, open up each of these workbooks, go to sheet 2, and extract info from cell D4?

Probably need some excellent programming skills.

Thanks guys!
jwslam is offline   Reply With Quote
Old 12-20-2016, 08:06 AM   #2
Fuzz
Franchise Player
 
Fuzz's Avatar
 
Join Date: Mar 2015
Exp:
Default

The simplest form of this is to open both workbooks, click the cell you want the data in, enter = then click the cell the data is in on the other workbook. Excel will link to the file path, you have to close the source workbook to see this. If needed you can edit this formula to use your lookup columns.

It will look something like this
='C:\data\files\[Master samples.xlsx]Sheet1'!$C$14
Fuzz is offline   Reply With Quote
Old 12-20-2016, 08:18 AM   #3
Northendzone
Franchise Player
 
Northendzone's Avatar
 
Join Date: Aug 2009
Exp:
Default

^ you could likely make the formula an indirect reference and then copy the formula down to work with each individual file.

if you do it this way and someone moves the files your formulas will no longer work
__________________
If I do not come back avenge my death
Northendzone is offline   Reply With Quote
Old 12-20-2016, 08:59 AM   #4
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

Quote:
Originally Posted by Fuzz View Post
The simplest form of this is to open both workbooks, click the cell you want the data in, enter = then click the cell the data is in on the other workbook. Excel will link to the file path, you have to close the source workbook to see this. If needed you can edit this formula to use your lookup columns.

It will look something like this
='C:\data\files\[Master samples.xlsx]Sheet1'!$C$14
There's 2000+ rows... no thanks.
Quote:
Originally Posted by Northendzone View Post
^ you could likely make the formula an indirect reference and then copy the formula down to work with each individual file.

if you do it this way and someone moves the files your formulas will no longer work
I've gotten it down to making a formula through CONCATENATE, but then it's a text string and doesn't evaluate. I've tried the macros found on google to evaluate but they don't seem to work.

I can't figure out how INDIRECT works even after reading the help.
jwslam is offline   Reply With Quote
Old 12-20-2016, 09:55 AM   #5
Northendzone
Franchise Player
 
Northendzone's Avatar
 
Join Date: Aug 2009
Exp:
Default

Here is an example of an indirect I set up for something I was working on:

SUM(INDIRECT("'"&$AM$3&"'!Y"&AL6&":Y"&AM6))

AM3 is the filetab name in my workbook
AL6 = 3
AM6 = 6

So this formula adds up the values in Y3:Y6 on the file tab entitled "Whatever"

In my case the file tab Whatever was in the same workbook - you will have to do a little extra work because your are referencing other workbooks.

it is hard to wrap your head around - but once you get it it could change your life
__________________
If I do not come back avenge my death
Northendzone is offline   Reply With Quote
Old 12-20-2016, 11:44 AM   #6
psyang
Powerplay Quarterback
 
Join Date: Jan 2010
Exp:
Default

Quote:
Originally Posted by Northendzone View Post
Here is an example of an indirect I set up for something I was working on:

SUM(INDIRECT("'"&$AM$3&"'!Y"&AL6&":Y"&AM6))

AM3 is the filetab name in my workbook
AL6 = 3
AM6 = 6

So this formula adds up the values in Y3:Y6 on the file tab entitled "Whatever"

In my case the file tab Whatever was in the same workbook - you will have to do a little extra work because your are referencing other workbooks.

it is hard to wrap your head around - but once you get it it could change your life
While INDIRECT can reference an external excel spreadsheet, that external excel spreadsheet must be open.

Here's a working example I just did.

values.xlsx (the top spreadsheet) just has values 5, 10, and 20 in the top row.

test2.xlsx has the file name (A1), Sheet name (B1), Cell name (C1), a formula that creates a string that refers to values.xlsx (D1), and then the evaluation of that string using indirect (E1).



The formula for D1 is displayed - it just concatenates the file, sheet, and cell, and adds the square brackets and ! where needed.

The formula for E1 is simply: =INDIRECT(D1), and you can see it displays the proper value, 20.

However, if values.xlsx is closed, then you will get a #REF error.

I think the only way around this is to use a macro to open each file and retrieve the value for you.
psyang is online now   Reply With Quote
Old 03-31-2017, 01:16 PM   #7
jwslam
Scoring Winger
 
Join Date: Jul 2014
Exp:
Default

So I found the solution to this, but it's fairly dumb and takes a lot of computing time.

Column C: Use concatenate and vlookup to get it a formula that looks like ='C:\data\files\[Master samples.xlsx]Sheet1'!$C$14

Column D: Copy and paste the values so it shows up as text

Use Replace, replace = with = and it'll evaluate as each cell formula is replaced
jwslam is offline   Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT -6. The time now is 06:28 AM.

Calgary Flames
2023-24




Powered by vBulletin® Version 3.8.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright Calgarypuck 2021