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!

"smartin" wrote:

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