too long a formula, can someone cut it down

  • Thread starter Thread starter Johnnyboy5
  • Start date Start date
J

Johnnyboy5

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,IF(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.
 
Try this

=IF(AND(A70>=3,A70<=53),INDIRECT("C"&A70))
Click to expand...

You are a STAR... thank you so much it works really well...thanks
again, you have saved me from going mad
 
=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()
 
=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()
Click to expand...

Hi just tested - it works just as well, thank you as well, you to
are a STAR
 
Given that OFFSET and INDIRECT are both volatile functions, I think I would
opt for the non-volatile INDEX function...

=INDEX(C1:C9,A70)
 
Given that OFFSET and INDIRECT are both volatile functions, I think I would
opt for the non-volatile INDEX function...

=INDEX(C1:C9,A70)
Click to expand...

Hi, tried it but it didn't work in the workbook I have set up.
 
Please try to follow the same posting method as those that respond to you
(in this case, top post your new messages), it makes it easier for people
who come across this thread in a Google search to follow.

Now, as to your response that the INDEX formula doesn't work... I don't see
how that can be as the formula I posted produces the same results as the one
Gary''s Student posted.
 
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.
 
Back
Top