Please Help with INDIRECT
 

Hello All,

I do not understand why this would not work. When I type the following, I
get the #REF error

=MAX(INDIRECT(Brkt1!$D$4,Brkt1!$D$6))

So, I modified the above line with the following, then I get the follwoing
error #VALUE:

=MAX(INDIRECT("Brkt1!$D$4","Brkt1!$D$6"))

What I am trying to do is the get the Max number between two numbers on
another sheet and copy the name of the max number over to the current
sheet.

A B C D E
1
2
3
4 John 190
5
6 Karen 201
7

My original formula is the following:

=IF(MAX(Brkt1!$D$4,Brkt1!$D$6)=Brkt1!$D$4,Brkt1!$C $4,Brkt1!$C$6)

so my result should be Karen in the current sheet.

The above formula works great, but when I delete the sheet called brkt1 and
then re-create it, I get #REF inside the formula where Brkt1 is suppose to
be.

Any and all help in this matter is greatly appreciated

Argus

Please Help with INDIRECT
 

Hi

Try
=MAX(INDIRECT("'"&H1&"'!D4"),INDIRECT("'"&H1&"'!D6 "))

where cell H1 holds your sheet name Brkt1.
If you delete that sheet, the formula will return a #REF error, but as
soon as the Sheet is re-created, the formula will return the correct
value.

Note carefully the double and single quotes
INDIRECT(" ' " &H1& " ' ! D4 ")
--
Regards

Roger Govier


"OdAwG" wrote in message
...
Hello All,

I do not understand why this would not work. When I type the
following, I
get the #REF error

=MAX(INDIRECT(Brkt1!$D$4,Brkt1!$D$6))

So, I modified the above line with the following, then I get the
follwoing
error #VALUE:

=MAX(INDIRECT("Brkt1!$D$4","Brkt1!$D$6"))

What I am trying to do is the get the Max number between two numbers
on
another sheet and copy the name of the max number over to the current
sheet.

A B C D E
1
2
3
4 John 190
5
6 Karen 201
7

My original formula is the following:

=IF(MAX(Brkt1!$D$4,Brkt1!$D$6)=Brkt1!$D$4,Brkt1!$C $4,Brkt1!$C$6)

so my result should be Karen in the current sheet.

The above formula works great, but when I delete the sheet called
brkt1 and
then re-create it, I get #REF inside the formula where Brkt1 is
suppose to
be.

Any and all help in this matter is greatly appreciated

Argus