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

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

Post by c2U3MDk » Wed, 04 Mar 2009 00:52:01


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!
 
 
 

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

Post by Pete_U » Wed, 04 Mar 2009 01:35:15

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


> city>st 爅c
> atl 爂a 123 ??5 ??gt;30>?17 ?10
>
> from that i am populating a summary sheet for my clients which>are assigned
> multiple locations (cities) and titles>(j>b codes).
>
> So i have a client that is assigned atlanta, ga for custom>r assistants
> which is job code 123...and i need to retrieve the number of >andidates in
> each folder/category in a snapshot/summary and i want to do >his for each
> location/cl>en>/job code
>
> i need a formula to go out into the raw data to find the cit>...then look
> for a specified job code then populate the cell below each cat>gory (inbox,
> intv, sel, hired) with the corresponding number of candidat>s in each of
> these categories. so that i can summarize each of my clien>s >ocations.
>
> i have tried ifs & ands & also looked at indirect & offset but>am having no
> luck...can anyone help me? 爐hanks!

 
 
 

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

Post by Glen » Wed, 04 Mar 2009 02:03:39


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.yqcomputer.com/