I am trying to reference a cell offset from another cell in the sheet I am
working on. The formula below is generating an error. The named range
"SUMRNGE" is a set of non contiguous cells.

Any ideas?

Thanks in advance.

=OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2)

--
Remove 7 numbers to email
AD108

Just another failed attempt on this one.

=OFFSET(CELL("address",OFFSET(A1,MATCH(SMALL(SUMRN GE,2),$AO$3:$EA$3,0)-1,0))
,2,2)

Still looking for help on this...

Thanks

"AD108" wrote in message
...
I am trying to reference a cell offset from another cell in the sheet I am
working on. The formula below is generating an error. The named range
"SUMRNGE" is a set of non contiguous cells.

Any ideas?

Thanks in advance.

=OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2)

--
Remove 7 numbers to email
AD108

You didn't specify a range "to offset from" (first argument)...
--
Regards,
Luc.

"Festina Lente"


"AD108" wrote:

I am trying to reference a cell offset from another cell in the sheet I am
working on. The formula below is generating an error. The named range
"SUMRNGE" is a set of non contiguous cells.

Any ideas?

Thanks in advance.

=OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2)

--
Remove 7 numbers to email
AD108

Thanks,

This task was driving me nuts for a while there, but I came up with the
following. I was trying to get the top 4 prices from a list of up to 15
comparisons, and return the vendor names, which are offset from the values.
Findnth is a custom function that takes (Table, Lookupvalue, occurance) and
returns the column of the nth occurance.

I was having alot of problems when vendors tied with a price, so I had to
logically deal with all types of ties, including ones that did not make it
into the top 4. I had to come up with a unique formula for each rank (1-4)

The 1st was easy

=IF(EA3="","",INDEX($AO$1:$EA$1,ROW(EA3)-(ROW(EA3)-1),MATCH(EA3,$AO3:$DZ3,0)
-3))

The 2nd

=IF(ED3="","",INDEX($AO$1:$EA$1,ROW(ED3)-(ROW(ED3)-1),findnth($AO3:$DZ3,ED3,
IF(AND((COUNTIF($AO3:$DZ3,ED3)1),EA3=ED3),2,1))-43))

The 3rd

=IF(EF3="","",INDEX($AO$1:$EA$1,ROW(EF3)-(ROW(EF3)-1),findnth($AO3:$DZ3,EF3,
IF(AND(COUNTIF($AO3:$DZ3,EF3)=2,ED3=EF3,EA3<EF3) ,2,IF(AND(COUNTIF($AO3:$DZ
3,EF3)=3,EA3=ED3,ED3=EF3),3,IF(AND(COUNTIF($AO3:$D Z3,EF3)=4,EA3=ED3,ED3=EF3,
EF3=EH3),3,1))))-43))

The 4th

=IF(EH3="","",INDEX($AO$1:$EA$1,ROW(EH3)-(ROW(EH3)-1),findnth($AO3:$DZ3,EH3,
IF(AND(COUNTIF($AO3:$DZ3,EH3)=2,EF3=EH3,EH3<ED3, EH3<EA3),2,IF(AND(COUNTIF
($AO3:$DZ3,EH3)=3,ED3=EF3,EF3=EH3),3,IF(AND(COUNT IF($AO3:$DZ3,EH3)=4,EA3=ED
3,ED3=EF3,EF3=EH3),4,1))))-43))

I've tested it for a few minutes and it seem to be working.

Thanks again,

Ariel
"PapaDos" wrote in message
...
You didn't specify a range "to offset from" (first argument)...
--
Regards,
Luc.

"Festina Lente"


"AD108" wrote:

I am trying to reference a cell offset from another cell in the sheet I
am
working on. The formula below is generating an error. The named range
"SUMRNGE" is a set of non contiguous cells.

Any ideas?

Thanks in advance.

=OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2)

--
Remove 7 numbers to email
AD108

Glad it works !

But it could probably be simpler.
For example, the row argument to the index function is always 1
(ROW(EF3)-(ROW(EF3)-1) is always 1)...

What are you trying to find ?
The 4 highest values in a table ?
You already have them in EA3, ED3, EF3, EH3 and you only need to get the
vendor names ?


--
Regards,
Luc.

"Festina Lente"


"AD108" wrote:

Thanks,

This task was driving me nuts for a while there, but I came up with the
following. I was trying to get the top 4 prices from a list of up to 15
comparisons, and return the vendor names, which are offset from the values.
Findnth is a custom function that takes (Table, Lookupvalue, occurance) and
returns the column of the nth occurance.

I was having alot of problems when vendors tied with a price, so I had to
logically deal with all types of ties, including ones that did not make it
into the top 4. I had to come up with a unique formula for each rank (1-4)

The 1st was easy

=IF(EA3="","",INDEX($AO$1:$EA$1,ROW(EA3)-(ROW(EA3)-1),MATCH(EA3,$AO3:$DZ3,0)
-3))

The 2nd

=IF(ED3="","",INDEX($AO$1:$EA$1,ROW(ED3)-(ROW(ED3)-1),findnth($AO3:$DZ3,ED3,
IF(AND((COUNTIF($AO3:$DZ3,ED3)1),EA3=ED3),2,1))-43))

The 3rd

=IF(EF3="","",INDEX($AO$1:$EA$1,ROW(EF3)-(ROW(EF3)-1),findnth($AO3:$DZ3,EF3,
IF(AND(COUNTIF($AO3:$DZ3,EF3)=2,ED3=EF3,EA3<EF3) ,2,IF(AND(COUNTIF($AO3:$DZ
3,EF3)=3,EA3=ED3,ED3=EF3),3,IF(AND(COUNTIF($AO3:$D Z3,EF3)=4,EA3=ED3,ED3=EF3,
EF3=EH3),3,1))))-43))

The 4th

=IF(EH3="","",INDEX($AO$1:$EA$1,ROW(EH3)-(ROW(EH3)-1),findnth($AO3:$DZ3,EH3,
IF(AND(COUNTIF($AO3:$DZ3,EH3)=2,EF3=EH3,EH3<ED3, EH3<EA3),2,IF(AND(COUNTIF
($AO3:$DZ3,EH3)=3,ED3=EF3,EF3=EH3),3,IF(AND(COUNT IF($AO3:$DZ3,EH3)=4,EA3=ED
3,ED3=EF3,EF3=EH3),4,1))))-43))

I've tested it for a few minutes and it seem to be working.

Thanks again,

Ariel
"PapaDos" wrote in message
...
You didn't specify a range "to offset from" (first argument)...
--
Regards,
Luc.

"Festina Lente"


"AD108" wrote:

I am trying to reference a cell offset from another cell in the sheet I
am
working on. The formula below is generating an error. The named range
"SUMRNGE" is a set of non contiguous cells.

Any ideas?

Thanks in advance.

=OFFSET(SMALL(SUMRNGE,2),-(ROW(ED3))+1,2)

--
Remove 7 numbers to email
AD108