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)
^ ^^ ^^ ^^