too long a formula, can someone cut it down
 

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.

too long a formula, can someone cut it down
 

Try this

=IF(AND(A70=3,A70<=53),INDIRECT("C"&A70))



On Nov 15, 4:09*pm, 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.

too long a formula, can someone cut it down
 

On 15 Nov, 11:39, muddan madhu wrote:
Try this

=IF(AND(A70=3,A70<=53),INDIRECT("C"&A70))

On Nov 15, 4:09*pm, 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.

You are a STAR... thank you so much it works really well...thanks
again, you have saved me from going mad

too long a formula, can someone cut it down
 

=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.
.

too long a formula, can someone cut it down
 

On 15 Nov, 11:57, Gary''s Student
wrote:
=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.
.

Hi just tested - it works just as well, thank you as well, you to
are a STAR

too long a formula, can someone cut it down
 

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.
.

too long a formula, can someone cut it down
 

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.
.

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

too long a formula, can someone cut it down
 

On 15 Nov, 17:24, Johnnyboy5 wrote:
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.
.

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 ?

too long a formula, can someone cut it down
 

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.

--
Rick (MVP - Excel)


"Johnnyboy5" wrote in message
...
On 15 Nov, 16:44, "Rick Rothstein"

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

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.

too long a formula, can someone cut it down
 

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.
.