Menu
  1. Accueil
  2. Tech Forum
  3. Office Software
  4. Excel

A IF(istext) formula that needs a trimming, please help? [Solved/Closed]

Report
Posts
1
Registration date
Monday January 2, 2017
Status
Member
Last seen
January 2, 2017
 -
Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
 -
Hello,

Am really struggling with a IF(is text) formula, it works its just really large and I need to be able to trim it a bit as I need to replicate this formula for several columns its a nightmare, here it is:

=if(istext('Request Log'!B54),'Request Log'!B54,if(istext('Request Log'!B53),'Request Log'!B53,if(istext('Request Log'!B52),'Request Log'!B52,if(istext('Request Log'!B51),'Request Log'!B51,if(istext('Request Log'!B50),'Request Log'!B50,if(istext('Request Log'!B49),'Request Log'!B49,if(istext('Request Log'!B48),'Request Log'!B48,if(istext('Request Log'!B47),'Request Log'!B47,if(istext('Request Log'!B46),'Request Log'!B46,if(istext('Request Log'!B45),'Request Log'!B45,if(istext('Request Log'!B44),'Request Log'!B44,if(istext('Request Log'!B43),'Request Log'!B43,if(istext('Request Log'!B42),'Request Log'!B42,if(istext('Request Log'!B41),'Request Log'!B41,if(istext('Request Log'!B41),'Request Log'!B41,if(istext('Request Log'!B40),'Request Log'!B40,if(istext('Request Log'!B39),'Request Log'!B39,if(istext('Request Log'!B38),'Request Log'!B38,if(istext('Request Log'!B37),'Request Log'!B37,if(istext('Request Log'!B36),'Request Log'!B36,if(istext('Request Log'!B35),'Request Log'!B35,if(istext('Request Log'!B34),'Request Log'!B34,if(istext('Request Log'!B33),'Request Log'!B33,if(istext('Request Log'!B32),'Request Log'!B32,if(istext('Request Log'!B31),'Request Log'!B31,if(istext('Request Log'!B30),'Request Log'!B30,if(istext('Request Log'!B29),'Request Log'!B29,if(istext('Request Log'!B28),'Request Log'!B28,if(istext('Request Log'!B27),'Request Log'!B27,if(istext('Request Log'!B26),'Request Log'!B26,if(istext('Request Log'!B25),'Request Log'!B25,if(istext('Request Log'!B24),'Request Log'!B24,if(istext('Request Log'!B23),'Request Log'!B23,if(istext('Request Log'!B22),'Request Log'!B22,if(istext('Request Log'!B21),'Request Log'!B21,if(istext('Request Log'!B20),'Request Log'!B20,if(istext('Request Log'!B19),'Request Log'!B19,if(istext('Request Log'!B18),'Request Log'!B18,if(istext('Request Log'!B17),'Request Log'!B17,if(istext('Request Log'!B16),'Request Log'!B16,if(istext('Request Log'!B15),'Request Log'!B15,if(istext('Request Log'!B14),'Request Log'!B14,if(istext('Request Log'!B13),'Request Log'!B13,if(istext('Request Log'!B13),'Request Log'!B13,if(istext('Request Log'!B12),'Request Log'!B12,if(istext('Request Log'!B11),'Request Log'!B11,if(istext('Request Log'!B10),'Request Log'!B10,if(istext('Request Log'!B9),'Request Log'!B9,if(istext('Request Log'!B8),'Request Log'!B8,if(istext('Request Log'!B7),'Request Log'!B7,if(istext('Request Log'!B6),'Request Log'!B6,if(istext('Request Log'!B5),'Request Log'!B5,if(istext('Request Log'!B4),'Request Log'!B4,if(istext('Request Log'!B3),'Request Log'!B3,'Request Log'!B3))))))))))))))))))))))))))))))))))))))))))))))))))))))

Basically, what is does is if the value from B54 is available then it is placed in another sheet's cell if there is no value on B54 then it will place the value of B53 and so forth.

Thank you for your help

See more

1 reply

Answer 1 / 1
Posts
2631
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
July 6, 2020
 428
Hi Bluntobject,

Give this formula a try:
=INDIRECT("B" &COUNTIF(B3:B54,"*")+2)

It looks for text values in the given range, so as long as you don't have empty cells between data in the range it will work.

Best regards,
Trowa
Thank you
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 3491 users have said thank you to us this month

Ask a question