Did you change the C1 in my formula? The range has to start at C1 in order
for the index to count down to the correct cell.

--
Rick (MVP - Excel)


"Johnnyboy5" wrote in message
...
On 15 Nov, 17:24, Johnnyboy5 wrote:

Hi, tried it but it didn't work in the workbook I have set up.

All I get is #REF! is there something else I should be adding to
the index formula ?

On 15 Nov, 16:44, "Rick Rothstein"



wrote:
Given that OFFSET and INDIRECT are both volatile functions, I think I
would
opt for the non-volatile INDEX function...

=INDEX(C1:C9,A70)

--
Rick (MVP - Excel)

"Gary''s Student" wrote in
message

...

=INDIRECT("C" & A70)

You don't need any IF's
Just use the value in A70 as in index into column C.

You could also use either OFFSET() or INDEX()
--
Gary''s Student - gsnu200908

"Johnnyboy5" wrote:

I need to use the IF 53 times ! I have 53 rows which I am using
filter
and Subtotal (row70) I need to get one of the columns "C" to enter
its text (names) into the subtotal row 70.

However I can only use the brackets up to 9 times ( I think) can does
anyone know how to reduce the formula size. I need to get A70 up to
=53 and likewise with the C column.

=IF(A70=3,C3,IF(A70=4,C4,IF(A70=5,C5,IF(A70=6,C6,I F(A70=6,C6,IF
(A70=7,C7,IF(A70=8,C8,
IF(A70=9,C9))))))))

Row 70 works like this
if the subtotal in column A row 70 = 5 then in the subtotal column C
(where the above formula is) it puts the name of the person, all the
other subtotal columns are for data and they work fine. This does
what I need but I need but only up to number 9, I need to get the
"equals" up to number 53 and therefore the C part of the formula up
to
53.

thanks everyboy how reads these posts.
.