Please help w/my INDIRECT formulas

  • Thread starter Thread starter bimmerman
  • Start date Start date
B

bimmerman

For some reason I can only get this to work in a simple SUM function and
not in my lengthier versions.

Here is what I tried, can someone please tell me where I went wrong?

Thanks.

Original Formula:
=RANK(K7,$K$7:$K$21)

My attempt at using indirect on it:
=RANK(INDIRECT(D1&7,D1&$7&":"&D1&$21))


And here's the other one I need help with. And for extra points, can
you figure out how to make the VLOOKUP return the column that goes
along with D1 also? See right now, it returns COL 11, but if D1 has 'A'
in it, I would want the VLOOKUP to return 1.

=IF(VLOOKUP(MIN($A$7:$A$21),$A$7:$K$21,11,FALSE)>0,VLOOKUP(MIN($A$7:$A$21),$A$7:$B$21,2,FALSE)&":
"&VLOOKUP(MIN($A$7:$A$21),$A$7:$K$21,11,FALSE),"All Domains are
negative")
 
...
...
Original Formula:
=RANK(K7,$K$7:$K$21)

My attempt at using indirect on it:
=RANK(INDIRECT(D1&7,D1&$7&":"&D1&$21))
Click to expand...

RANK requires two arguments. Your second formula only gives it one. Try

=RANK(INDIRECT(D1&7),INDIRECT(D1&7&":"&D1&21))
And here's the other one I need help with. And for extra points, can
you figure out how to make the VLOOKUP return the column that goes
along with D1 also? See right now, it returns COL 11, but if D1 has 'A'
in it, I would want the VLOOKUP to return 1.

=IF(VLOOKUP(MIN($A$7:$A$21),$A$7:$K$21,11,FALSE)>0,
VLOOKUP(MIN($A$7:$A$21),$A$7:$B$21,2,FALSE)&":"&
VLOOKUP(MIN($A$7:$A$21),$A$7:$K$21,11,FALSE),
"All Domains are negative")
Click to expand...

Unclear. More details, perhaps an example.
 
Yup, the rank one worked perfectly, thank you.


Now to explain this 2nd one:

Using my picture, the formula I already gave goes in cell C27. Cell C2
is the same thing except using the MAX function.

As you can see starting at H6 I have the days of the week going to th
right. This is where the important cell of D1 comes in. Each day I jus
want to change D1 to represent whatever day I am entering values for.

Also, see how the vlookup is returning row 8? That is because D1=H=8t
column in the array. Now for tomorrow, D1=I=9. So is there also a wa
to make the vlookup return the last column in the array?

Thanks a bunch

Attachment filename: excel.jpg
Download attachment: http://www.excelforum.com/attachment.php?postid=41316
 
Back
Top