Menu
Ask a question Report

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

Posts
1
Registration date
Monday January 2, 2017
Status
Member
Last seen
January 2, 2017
 - - Latest reply: TrowaD
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
 - Jan 3, 2017 at 11:50 AM
Excel Ask a question Report
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

Best answer
Report
Posts
2576
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 10, 2019
 383
1
Thank you
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

Say "Thank you" 1

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

CCM 5736 users have said thank you to us this month

Ask a question