Previous Topic Next Topic

A IF(istext) formula that needs a trimming, please help?

Solved/Closed
Bluntobject Posts 1 Registration date Monday January 2, 2017 Status Member Last seen January 2, 2017 - Jan 2, 2017 at 10:51 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jan 3, 2017 at 11:50 AM
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

Related:

1 reply

Answer 1 / 1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jan 3, 2017 at 11:50 AM
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
1