Formula Too Long

[Deano158]

I am not very experienced in excel functions, however I have taken basic programming classes.
Currently I have an excel workbook, one page prints out information from one of 6 sheets which have had information pasted to them from an outside source. The information is brought together by your requested input on a separate sheet.
I have a good amount of it finished, however, some of the coding is very long.. especially the following statement:

=IF(AND(ISTEXT(B8),(Input!I5=1)), (ISNUMBER(SEARCH("*30*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A5, IF(AND(ISNUMBER(SEARCH("*40*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A1, IF(AND(ISNUMBER(SEARCH("*60*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A2, IF(AND(ISNUMBER(SEARCH("*70*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A3, IF(AND(ISNUMBER(SEARCH("*80*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A4, IF(AND(ISTEXT(B8),(Input!I5=2)), (ISNUMBER(SEARCH("*30*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A5, IF(AND(ISNUMBER(SEARCH("*40*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A1, IF(AND(ISNUMBER(SEARCH("*60*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A2, IF(AND(ISNUMBER(SEARCH("*70*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A3, IF(AND(ISNUMBER(SEARCH("*80*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A4, IF(AND(ISTEXT(B8),(Input!I5=3)), (ISNUMBER(SEARCH("*30*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A5, IF(AND(ISNUMBER(SEARCH("*40*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A1, IF(AND(ISNUMBER(SEARCH("*60*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A2, IF(AND(ISNUMBER(SEARCH("*70*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A3, IF(AND(ISNUMBER(SEARCH("*80*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A4, IF(AND(ISTEXT(B8),(Input!I5=4)), (ISNUMBER(SEARCH("*30*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A5, IF(AND(ISNUMBER(SEARCH("*40*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A1, IF(AND(ISNUMBER(SEARCH("*60*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A2, IF(AND(ISNUMBER(SEARCH("*70*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A3, IF(AND(ISNUMBER(SEARCH("*80*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A4, IF(AND(ISTEXT(B8),(Input!I5=5)), (ISNUMBER(SEARCH("*30*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A5, IF(AND(ISNUMBER(SEARCH("*40*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A1, IF(AND(ISNUMBER(SEARCH("*60*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A2, IF(AND(ISNUMBER(SEARCH("*70*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A3, IF(AND(ISNUMBER(SEARCH("*80*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A4, IF(AND(ISTEXT(B8),(Input!I5=6)), (ISNUMBER(SEARCH("*30*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A5, IF(AND(ISNUMBER(SEARCH("*40*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A1, IF(AND(ISNUMBER(SEARCH("*60*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A2, IF(AND(ISNUMBER(SEARCH("*70*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A3, IF(AND(ISNUMBER(SEARCH("*80*",'Lawson 1'!B1)),(ISTEXT(B8))),Values!A4,”” )))))

I'm not even sure if this works, because I can't test it. The long and short of what I'm trying to accomplish is the function should look at the input page and see if the information is on sheet 1-6. Then pull over the corresponding information depending on which number is found in the search.

There must be an easier way to do it... I just don't know how.

I'd appreciate any help!

Thanks

[davesexcel]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Deano158]

I'm new, how would I go about doing that?

[Paul]

Reply to this post like you just did, then click Go Advanced. In that window click Manage Attachments and select your file by clicking the Browse button. Be sure to click the Upload button after browsing and selecting the file.

[JBeaucaire]

Give this a shot:

=IF(AND(ISTEXT(B8), OR(INPUT!I5={1,2,3,4,5,6})), IF(ISNUMBER(SEARCH("30",'LAWSON 1'!B1)), VALUES!A5, IF(ISNUMBER(SEARCH("40",'LAWSON 1'!B1)), VALUES!A1, IF(ISNUMBER(SEARCH("60",'LAWSON 1'!B1)), VALUES!A2, IF(ISNUMBER(SEARCH("70",'LAWSON 1'!B1)), VALUES!A3, IF(ISNUMBER(SEARCH("80",'LAWSON 1'!B1)), VALUES!A4, ""))))))