Developer Forum

I need to populate a master spreadsheet from a source file of raw data.

I need a formula that will look first for the city, then for the job code
then if both match then return the corresponding value for each of the
folders...

i.e., look for atlanta...if found then look for job code 12345...if found
then return value of inbox here; value of interview here; value of selected
here...etc.

is that possible? i have played with ifs & ands etc but cannot make it work.


the raw data is in a spreadsheet set up as follows:

Inbox Interview
Selected In Process Hired
City State Job Code Recruiter 361 49 22
16 10

Hi,

"from each of the folders"? What folders, where, what corresponding values?
Do you have a set of files in a set of folders and the files in those
folders have a lookup table and based on the two variables you mentioned you
want to return values from these (closed?) files.

How does city and job code tell you which folder to look at? You would need
to set up some sort of lookup table listing the path and file name of the
folder, file, sheet, and range that each set of city and job code indicate.
However, to use this in a lookup against closed files is a problem because
INDIRECT only works with open files.

Cheers,
Shane Devenshire