Developer Forum

I have one data sheet with information in columns a-j

i have a separate report that i need to populate from the data sheet

i need a formula that will go look for "c *** te" in column b of the data
sheet and if found then look for "customer assistant" in column d and if
found then find "job seeker" in the same data sheet and if found return the
value listed under "job seeker" to the report

so i think it should be a combo of vlookup and if statements but i am not
familiar with if statements and not very good with formulas.

C *** te is in the data sheet along with customer assistant and job
seeker...customer assistant will be found in column D and job seeker may be
found potentially in columns E-I.

Example:

C *** te
High Volume Title Job Seeker Testing Evaluation
Sales Associates 3 6 2
Cusomer Assistant 9 7 1

I need to look for c *** te and if found then look in the rows below for
customer assistant and then look for job seeker then if found look one row
down and return the value found directly below job seeker and return that
number to my report in a separate worksheet.

this is all very new to me so i will need it broken down in simple terms
please.

Thank for your help...this discussion board is the best excel resource i
have ever found.

[snipped]

You were not quite consistent in describing what you want, but I think
what you are saying is you want the value under "Job Seeker", in the row
for "Cusomer Assistant" [sic], under the heading "C *** te".

Here is one way that uses a few "helper columns". Since you say you are
new to some of this I will describe each formula.

I'm guessing you might want to extend this to look for other cities,
other titles, so I worked that in too.

Put the following in K1:N1
C *** te Cusomer Assistant Job Seeker Value

In K2
=MATCH(K$1,$B:$B,0)
This looks for the city name in column B and tells which row has it.

In L2
=MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1
This looks for the title (Cusomer Assistant) in column D, and tells how
many rows below the city name it appears.

In M2
=MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1
This scans the row below the city name for the opportunity (Job Seeker)
and tells how many columns to the right of B is appears.

In N2
=OFFSET(INDIRECT("B"&K2),L2,M2)
Finally, the value you are looking for! It uses the coordinates we
determined in L2 and M2 to locate the correct value.

With that done, should you want to find a value for another city, title,
etc., just change the cells K1:M1.

Hope this helps!

Thanks for the response...i have a few questions.

Do i enter these formulas in the data sheet or the report i am trying to
populate?
Also, how do i get it to return the value into the correct cell on my report?

Thanks again for your help!

Theoretically you can put the formulas anywhere you prefer, but the
references will need to be changed manually. The final value cell can go
anywhere, just make sure it points to the other three supporting
formulas. And kudos to you by the way for trying to plow through
this--these are not the easiest formulas to master (I'm still trying
myself!)

Formula #1
=MATCH(K$1,$B:$B,0)
^^^ ^^^^^
K$1 points to the "selector cell" where you enter a city name.
$B:$B points to the data sheet. If the formulas are in a different
worksheet this will look something like 'Data Sheet'!$B:$B. If you use
the formula wizard the correct sheet name will be filled in for you.

Formula #2
=MATCH(L$1,OFFSET(INDIRECT("B"&K2),0,2,999),0)-1
^^^ ^ ^^
L$1 points to the title selector, similar to above.
INDIRECT("B"&K2) -- Things get trickier with the INDIRECT formulas.
"B" refers to the left-most column in the data sheet, the "anchor
column" if you will. Again, if the data and formulas are in different
worksheets you again need to ensure the sheet reference is correct,
unfortunately the wizard will not help you with this one, so you need to
insert the correct reference yourself e.g.,
INDIRECT("'Data Sheet'!B"&K2)
K2 points to formula #1, easily enough.

The remaining formulas are similar to above. These are the spots you
will need to watch:

Formula #3
=MATCH(M$1,OFFSET(INDIRECT("B"&K2),1,0,1,8),0)-1
^^^ ^ ^^

Formula #4
=OFFSET(INDIRECT("B"&K2),L2,M2)
^ ^^ ^^ ^^

Thanks again for your help...you are correct in that these formulas are WAY
over my head...but i love this stuff and am determined to learn it. :) so i
REALLY appreciate your help and patience. the 1st formula is working.
however the remainder are not. Below is my version of formula 2:

=MATCH(B380,OFFSET(INDIRECT("[In Process Reqs.xls]Summary by City and
Step'!$B:$B"&BV380),0,2,999),0)-1

b380=title of position
in process reqs=my data sheet
bv380=value of my first formula

Any ideas? Thanks again for your help!

LOL I knew I should not have deleted the test worksheet I built for this
project!

So, on the fly, I think you could try this:

=MATCH(B380,OFFSET(INDIRECT("'[In Process Reqs.xls]Summary by City and
Step'!B"&BV380),0,2,999),0)-1

Two issues to note:
Missing tick mark ' in front of external workbook reference

Do not reference the whole column $B:$B in the INDIRECT function. If you
look at how we are using INDIRECT, we are building up a string that will
serve as a cell reference. E.g., if BV380 has the value 10, then

INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B"&BV380)

becomes

INDIRECT("'[In Process Reqs.xls]Summary by City and Step'!B10)

which boils down to

='[In Process Reqs.xls]Summary by City and Step'!B10

... just a simple cell reference! The beauty of it is we can change the
row (or column) by inserting other formulas and wrapping it with INDIRECT.

Whoops! I got off track here.

INDIRECT('[In Process Reqs.xls]Summary by City and Step'!B10)

which boils down to the range

'[In Process Reqs.xls]Summary by City and Step'!B10

... just a simple reference! The beauty of it is we can change the
row (or column) by inserting other formulas and wrapping it with INDIRECT.

Sorry for the confusion.

sorry...i'm sure you are sick of me, but i am still getting a #ref! error

so if the result of bv380 is 33 does that mean the formula in bw380 is
looking in line 33 of my data sheet?

would it help if i sent you my actual spreadsheet or would that be asking
too much or too cumbersome?

thanks again...

OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!

I DID IT!!!

Well with signficiant eversomuchappreciated help from you!

this is the most awesome thing ever!

I still can't believe it actually worked and that i was able to actually
troubleshoot a few things on my own...i present this to my boss tomorrow.

i cannot possibly tell you how much i appreciate your help with
this...honestly, i never thought any of it would ever work but it does and it
is beautiful!

thanks SO VERY MUCH again for your help! you are THE BEST!!!