Hi am new here and am trying to figure out a more dynamic way of pulling information in. My basic layout is I have data that is are in folders labeled 2009, 2010, 2011. Within each folder is a file named - Month Year.csv
Now in my main spread sheet I am trying to do it so that if the date is FEB 11 it goes to the 2011 folder and pulls the data from FEB 11.csv using a lookup.
I am currently using a CONCATENATE to make the file reference in cell name cLookup on data sheet Calculations. Which looks like this:
=CONCATENATE("'",$A$4,$C$2,"\[",$B$2," ",$D$2,".csv]",$B$2," ",$D$2,"'!$B$2:$B$6666")
and another in cell cName on data sheet Calculations that looks like this:
=CONCATENATE("'",$A$4,$C$2,"\[",$B$2," ",$D$2,".csv]",$B$2," ",$D$2,"'!$C$2:$C$6666")
$A$4 = C:\Users\wisper\Documents\Facility Transaction Sheets\
$C$2 = 2011
$B$2 = FEB
$D$2 = 11
Which renders as
'C:\Users\wisper\Documents\Facility Transaction Sheets\2011\[FEB 11.csv]FEB 11'!$B$2:$B$6666 and 'C:\Users\wisper\Documents\Facility Transaction Sheets\2011\[FEB 11.csv]FEB 11'!$C$2:$C$6666
These work fine and do what there suppose to do. Then in a cell that I want the data in on another datasheet I am using a LOOKUP that looks like this:
=LOOKUP($A2,cLookup,cName)
This formula gives me a #N/A. I can see why as the cLookup and cName render with " on each end and it doesn't know how to process this. I tried using INDIRECT but that didn't seem to work.
Can anyone help me.
Now in my main spread sheet I am trying to do it so that if the date is FEB 11 it goes to the 2011 folder and pulls the data from FEB 11.csv using a lookup.
I am currently using a CONCATENATE to make the file reference in cell name cLookup on data sheet Calculations. Which looks like this:
=CONCATENATE("'",$A$4,$C$2,"\[",$B$2," ",$D$2,".csv]",$B$2," ",$D$2,"'!$B$2:$B$6666")
and another in cell cName on data sheet Calculations that looks like this:
=CONCATENATE("'",$A$4,$C$2,"\[",$B$2," ",$D$2,".csv]",$B$2," ",$D$2,"'!$C$2:$C$6666")
$A$4 = C:\Users\wisper\Documents\Facility Transaction Sheets\
$C$2 = 2011
$B$2 = FEB
$D$2 = 11
Which renders as
'C:\Users\wisper\Documents\Facility Transaction Sheets\2011\[FEB 11.csv]FEB 11'!$B$2:$B$6666 and 'C:\Users\wisper\Documents\Facility Transaction Sheets\2011\[FEB 11.csv]FEB 11'!$C$2:$C$6666
These work fine and do what there suppose to do. Then in a cell that I want the data in on another datasheet I am using a LOOKUP that looks like this:
=LOOKUP($A2,cLookup,cName)
This formula gives me a #N/A. I can see why as the cLookup and cName render with " on each end and it doesn't know how to process this. I tried using INDIRECT but that didn't seem to work.
Can anyone help me.
Last edited: