function within a function

J

jay d

hi

my function is
Code:
--------------------
=IF(MONTH(TODAY())=(MONTH($H$4)),H6,)
--------------------
.. if i were to add a function in the false statement, it will only
allow this 6 or 7 times. is there a way to get around this using
functions?

eg.
Code:
 
S

SteveG

Jay

You are only allowed 7 nested functions but if you post your data an
what you want to do with it (your OP has the same IF over and over) i
would be easier to help.


Regards,

Stev
 
J

JMB

I am assuming the data you want returned is in row 6 (beginning in column H),
the dates are in row 4 (beginning in column H). As mentioned, your example
simply repeats the same range references, which is not helpful.

=INDEX(trends!H6:L6,MATCH(MONTH(TODAY()),MONTH(trends!H4:L4),0))

must use Control+Shift+Enter after typing in the formula, change ranges as
needed.
 
J

jay d

hi

i want to have this

Code:
--------------------
=IF(MONTH(TODAY())=(MONTH(Trends!$f$4)),Trends!f6,IF(MONTH(TODAY())=(MONTH(Trends!$g$4)),Trends!g6, IF(MONTH(TODAY())=(MONTH(Trends!$H$4)),Trends!H6, IF(MONTH(TODAY())=(MONTH(Trends!$i$4)),Trends!i6, IF(MONTH(TODAY())=(MONTH(Trends!$j$4)),Trends!j6, IF(MONTH(TODAY())=(MONTH(Trends!$k$4)),Trends!k6, IF(MONTH(TODAY())=(MONTH(Trends!$l$4)),Trends!l6,IF(MONTH(TODAY())=(MONTH(Trends!$m$4)),Trends!m6, IF(MONTH(TODAY())=(MONTH(Trends!$n$4)),Trends!n6, IF(MONTH(TODAY())=(MONTH(Trends!$o$4)),Trends!o6,)
--------------------


but its more than 7 functions...

thanks
 
J

JMB

You should be able to adjust the ranges as follows:

=INDEX(trends!F6:06,MATCH(MONTH(TODAY()),MONTH(trends!F4:O4),0))

Again, it is an array formula, so you will need to hit Control+Shift+Enter
when you key it in, not just the Enter key. If you do it right, Excel will
put braces { } around the formula.
 
Top