vlookup and multiple ifs
OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OMG!OM G!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!!!
"smartin" wrote:
smartin wrote:
se7098 wrote:
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
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.
|