if & ands and either indirect or offset - need help fast please

[se7098]

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

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.

i have tried ifs & ands & also looked at indirect & offset but am having no
luck...can anyone help me? thanks!

 

[Pete_UK]

In your source sheet you could insert a new column D, and in D3 you
can put this formula:

=A3&B3&C3

and copy this down by double-clicking the fill handle (the small black
square in the bottom right corner of the cursor).

Then in your summary sheet you could use columns A, B and C to record
the city, state and job code, and in D3 you could have this formula:

=VLOOKUP($A3&$B3&$C3,Source!$D:$H,COLUMN(B3),0)

Then copy this across to G3.

Hope this helps.

Pete

 

[Glenn]

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

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.

i have tried ifs & ands & also looked at indirect & offset but am having no
luck...can anyone help me? thanks!

An array formula without the helper column could be made to work. You could
also try a PivotTable. Take a look at this:


http://www.savefile.com/files/2023475

 

[se7098]

hi Pete,

thanks for the quick response.

i'm not sure this will work...this is a report i will be updating daily and
my summary sheet is preformatted to match another one that is already created.

unfortunately the source files are not identical to the other and that is
why i am having to come up with a different way to pull the data in.

without seeing the spreadsheets, i am sure this is difficult to picture...

 

[Pete_UK]

Well, if your MATCH formula is working, you could include it within an
INDEX function to bring data from those other columns across to your
summary sheet.

Please try to describe your actual layout in both sheets (i.e. columns
used etc), and then I don't have to guess too much.

Pete

 

[se7098]

Hi Pete,

it is working for one of the summary sheets but not the one i am working on
now.

is there a way i can send you a snippet of the actual reports i am working
with?

thanks for your help...it is greatly appreciated.

 

[Pete_UK]

Okay, send it to:

pashurst <at> auditel.net

(change the obvious). If you are using XL2007 please save your snippet
as a 97/03 compatible (i.e. .xls, not .xlsx)

Pete