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

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
 

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

On Mar 2, 3:52*pm, se7098 wrote:
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
 

se7098 wrote:
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

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

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...

on the 1st file, i am using match etc...i just can't figure out how to
adjust for the different format of the source doc.

"Pete_UK" wrote:

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

On Mar 2, 3:52 pm, se7098 wrote:
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 pleas
 

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

On Mar 2, 5:30*pm, se7098 wrote:
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....

on the 1st file, i am using match etc...i just can't figure out how to
adjust for the different format of the source doc.



"Pete_UK" wrote:
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

On Mar 2, 3:52 pm, se7098 wrote:
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!- Hide quoted text -

- Show quoted text -

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

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" wrote:

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

On Mar 2, 5:30 pm, se7098 wrote:
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....

on the 1st file, i am using match etc...i just can't figure out how to
adjust for the different format of the source doc.



"Pete_UK" wrote:
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

On Mar 2, 3:52 pm, se7098 wrote:
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!- Hide quoted text -

- Show quoted text -

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

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

On Mar 2, 7:22*pm, se7098 wrote:
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" wrote:
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

On Mar 2, 5:30 pm, se7098 wrote:
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....

on the 1st file, i am using match etc...i just can't figure out how to
adjust for the different format of the source doc.

"Pete_UK" wrote:
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

On Mar 2, 3:52 pm, se7098 wrote:
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!- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -