Menu
Ask a question Report

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

Bluntobject 1 Posts Monday January 2, 2017Registration date January 2, 2017 Last seen - Jan 2, 2017 at 10:51 AM - Latest reply: TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen
- 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 Respond to TrowaD
TrowaD 2369 Posts Sunday September 12, 2010Registration dateModeratorStatus June 21, 2018 Last seen - Jan 3, 2017 at 11:50 AM
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

Thank you, TrowaD 1

Something to say? Add comment

CCM has helped 1818 users this month

Ask a question