multiple ifs & ands indirect/offset...need help desperately please

[se7098]

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

 

[Shane Devenshire]

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

 

[se7098]

hi Shane...sorry - the folders are the inbox, selected etc. & the values are
the numbers under each. i will try to make a better example...i see it didn't
translate well below.

what i am doing is pulling numbers from a database and exporting them into
an excel spreadsheet which is my raw data/source file. the database is
broken down by the folders in which the candidates sit...inbox; scheduled for
interview;selected...etc. Source info is set up as in the table below:

Inbox Intv Sel Hird
city st jc
atl ga 123 55 30 17 10


from that i am populating a summary sheet for my clients which are assigned
multiple locations (cities) and titles (job codes).

So i have a client that is assigned atlanta, ga for customer assistants
which is job code 123...and i need to retrieve the number of candidates in
each folder/category in a snapshot/summary and i want to do this for each
location/client/job code

i need a formula to go out into the raw data to find the city...then look
for a specified job code then populate the cell below each category (inbox,
intv, sel, hired) with the corresponding number of candidates in each of
these categories. so that i can summarize each of my clients locations.

does that make sense?