Formula Decreasin Request

HSAR

Banned - Rules violations
Joined
Jul 6, 2020
Messages
37
Office Version
  1. 2016
Platform
  1. Windows
Using below formula to get desired result from attendance sheet i just added this &" // "& in formula so that i could separate them while making report from (Text to Column) and now
Exceed 8192 char in formula
I would request in this regard please decrease this or make it correct.




=Data!C$1&" => "&IF(Data!C3="","",IF(ISNUMBER(LEFT(Data!C3)*1),"*Late* - ",IF(Data!C3="L","*Leave* - ",IF(Data!C3="A","*Absent* - "))))&IF(OR(Data!C3<>"",Data!D3<>""),TEXT(Data!$A$3,"MM/DD/YY - "),"")&IF(OR(Data!C3<>"",Data!D3<>""),TEXT(Data!$B$3,"DDDD - "),"")&IF(Data!E3<>"",Data!E3,"")&" // "&
IF(Data!C4="","",IF(ISNUMBER(LEFT(Data!C4)*1),"*Late* - ",IF(Data!C4="L","*Leave* - ",IF(Data!C4="A","*Absent* - "))))&IF(OR(Data!C4<>"",Data!D4<>""),TEXT(Data!$A$4,"MM/DD/YY - "),"")&IF(OR(Data!C4<>"",Data!D4<>""),TEXT(Data!$B$4,"DDDD - "),"")&IF(Data!E4<>"",Data!E4,"")&" // "&
IF(Data!C5="","",IF(ISNUMBER(LEFT(Data!C5)*1),"*Late* - ",IF(Data!C5="L","*Leave* - ",IF(Data!C5="A","*Absent* - "))))&IF(OR(Data!C5<>"",Data!D5<>""),TEXT(Data!$A$5,"MM/DD/YY - "),"")&IF(OR(Data!C5<>"",Data!D5<>""),TEXT(Data!$B$5,"DDDD - "),"")&IF(Data!E5<>"",Data!E5,"")&" // "&
IF(Data!C6="","",IF(ISNUMBER(LEFT(Data!C6)*1),"*Late* - ",IF(Data!C6="L","*Leave* - ",IF(Data!C6="A","*Absent* - "))))&IF(OR(Data!C6<>"",Data!D6<>""),TEXT(Data!$A$6,"MM/DD/YY - "),"")&IF(OR(Data!C6<>"",Data!D6<>""),TEXT(Data!$B$6,"DDDD - "),"")&IF(Data!E6<>"",Data!E6,"")&" // "&
IF(Data!C7="","",IF(ISNUMBER(LEFT(Data!C7)*1),"*Late* - ",IF(Data!C7="L","*Leave* - ",IF(Data!C7="A","*Absent* - "))))&IF(OR(Data!C7<>"",Data!D7<>""),TEXT(Data!$A$7,"MM/DD/YY - "),"")&IF(OR(Data!C7<>"",Data!D7<>""),TEXT(Data!$B$7,"DDDD - "),"")&IF(Data!E7<>"",Data!E7,"")&" // "&
IF(Data!C8="","",IF(ISNUMBER(LEFT(Data!C8)*1),"*Late* - ",IF(Data!C8="L","*Leave* - ",IF(Data!C8="A","*Absent* - "))))&IF(OR(Data!C8<>"",Data!D8<>""),TEXT(Data!$A$8,"MM/DD/YY - "),"")&IF(OR(Data!C8<>"",Data!D8<>""),TEXT(Data!$B$8,"DDDD - "),"")&IF(Data!E8<>"",Data!E8,"")&" // "&
IF(Data!C9="","",IF(ISNUMBER(LEFT(Data!C9)*1),"*Late* - ",IF(Data!C9="L","*Leave* - ",IF(Data!C9="A","*Absent* - "))))&IF(OR(Data!C9<>"",Data!D9<>""),TEXT(Data!$A$9,"MM/DD/YY - "),"")&IF(OR(Data!C9<>"",Data!D9<>""),TEXT(Data!$B$9,"DDDD - "),"")&IF(Data!E9<>"",Data!E9,"")&" // "&
IF(Data!C10="","",IF(ISNUMBER(LEFT(Data!C10)*1),"*Late* - ",IF(Data!C10="L","*Leave* - ",IF(Data!C10="A","*Absent* - "))))&IF(OR(Data!C10<>"",Data!D10<>""),TEXT(Data!$A$10,"MM/DD/YY - "),"")&IF(OR(Data!C10<>"",Data!D10<>""),TEXT(Data!$B$10,"DDDD - "),"")&IF(Data!E10<>"",Data!E10,"")&" // "&
IF(Data!C11="","",IF(ISNUMBER(LEFT(Data!C11)*1),"*Late* - ",IF(Data!C11="L","*Leave* - ",IF(Data!C11="A","*Absent* - "))))&IF(OR(Data!C11<>"",Data!D11<>""),TEXT(Data!$A$11,"MM/DD/YY - "),"")&IF(OR(Data!C11<>"",Data!D11<>""),TEXT(Data!$B$11,"DDDD - "),"")&IF(Data!E11<>"",Data!E11,"")&"// "&
IF(Data!C12="","",IF(ISNUMBER(LEFT(Data!C12)*1),"*Late* - ",IF(Data!C12="L","*Leave* - ",IF(Data!C12="A","*Absent* - "))))&IF(OR(Data!C12<>"",Data!D12<>""),TEXT(Data!$A$12,"MM/DD/YY - "),"")&IF(OR(Data!C12<>"",Data!D12<>""),TEXT(Data!$B$12,"DDDD - "),"")&IF(Data!E12<>"",Data!E12,"")&" // "&
IF(Data!C13="","",IF(ISNUMBER(LEFT(Data!C13)*1),"*Late* - ",IF(Data!C13="L","*Leave* - ",IF(Data!C13="A","*Absent* - "))))&IF(OR(Data!C13<>"",Data!D13<>""),TEXT(Data!$A$13,"MM/DD/YY - "),"")&IF(OR(Data!C13<>"",Data!D13<>""),TEXT(Data!$B$13,"DDDD - "),"")&IF(Data!E13<>"",Data!E13,"")&" // "&
IF(Data!C14="","",IF(ISNUMBER(LEFT(Data!C14)*1),"*Late* - ",IF(Data!C14="L","*Leave* - ",IF(Data!C14="A","*Absent* - "))))&IF(OR(Data!C14<>"",Data!D14<>""),TEXT(Data!$A$14,"MM/DD/YY - "),"")&IF(OR(Data!C14<>"",Data!D14<>""),TEXT(Data!$B$14,"DDDD - "),"")&IF(Data!E14<>"",Data!E14,"")&" // "&
IF(Data!C15="","",IF(ISNUMBER(LEFT(Data!C15)*1),"*Late* - ",IF(Data!C15="L","*Leave* - ",IF(Data!C15="A","*Absent* - "))))&IF(OR(Data!C15<>"",Data!D15<>""),TEXT(Data!$A$15,"MM/DD/YY - "),"")&IF(OR(Data!C15<>"",Data!D15<>""),TEXT(Data!$B$15,"DDDD - "),"")&IF(Data!E15<>"",Data!E15,"")&" // "&
IF(Data!C16="","",IF(ISNUMBER(LEFT(Data!C16)*1),"*Late* - ",IF(Data!C16="L","*Leave* - ",IF(Data!C16="A","*Absent* - "))))&IF(OR(Data!C16<>"",Data!D16<>""),TEXT(Data!$A$16,"MM/DD/YY - "),"")&IF(OR(Data!C16<>"",Data!D16<>""),TEXT(Data!$B$16,"DDDD - "),"")&IF(Data!E16<>"",Data!E16,"")&" // "&
IF(Data!C17="","",IF(ISNUMBER(LEFT(Data!C17)*1),"*Late* - ",IF(Data!C17="L","*Leave* - ",IF(Data!C17="A","*Absent* - "))))&IF(OR(Data!C17<>"",Data!D17<>""),TEXT(Data!$A$17,"MM/DD/YY - "),"")&IF(OR(Data!C17<>"",Data!D17<>""),TEXT(Data!$B$17,"DDDD - "),"")&IF(Data!E17<>"",Data!E17,"")&" // "&
IF(Data!C18="","",IF(ISNUMBER(LEFT(Data!C18)*1),"*Late* - ",IF(Data!C18="L","*Leave* - ",IF(Data!C18="A","*Absent* - "))))&IF(OR(Data!C18<>"",Data!D18<>""),TEXT(Data!$A$18,"MM/DD/YY - "),"")&IF(OR(Data!C18<>"",Data!D18<>""),TEXT(Data!$B$18,"DDDD - "),"")&IF(Data!E18<>"",Data!E18,"")&" // "&
IF(Data!C19="","",IF(ISNUMBER(LEFT(Data!C19)*1),"*Late* - ",IF(Data!C19="L","*Leave* - ",IF(Data!C19="A","*Absent* - "))))&IF(OR(Data!C19<>"",Data!D19<>""),TEXT(Data!$A$19,"MM/DD/YY - "),"")&IF(OR(Data!C19<>"",Data!D19<>""),TEXT(Data!$B$19,"DDDD - "),"")&IF(Data!E19<>"",Data!E19,"")&" // "&
IF(Data!C20="","",IF(ISNUMBER(LEFT(Data!C20)*1),"*Late* - ",IF(Data!C20="L","*Leave* - ",IF(Data!C20="A","*Absent* - "))))&IF(OR(Data!C20<>"",Data!D20<>""),TEXT(Data!$A$20,"MM/DD/YY - "),"")&IF(OR(Data!C20<>"",Data!D20<>""),TEXT(Data!$B$20,"DDDD - "),"")&IF(Data!E20<>"",Data!E20,"")&" // "&
IF(Data!C21="","",IF(ISNUMBER(LEFT(Data!C21)*1),"*Late* - ",IF(Data!C21="L","*Leave* - ",IF(Data!C21="A","*Absent* - "))))&IF(OR(Data!C21<>"",Data!D21<>""),TEXT(Data!$A$21,"MM/DD/YY - "),"")&IF(OR(Data!C21<>"",Data!D21<>""),TEXT(Data!$B$21,"DDDD - "),"")&IF(Data!E21<>"",Data!E21,"")&" // "&
IF(Data!C22="","",IF(ISNUMBER(LEFT(Data!C22)*1),"*Late* - ",IF(Data!C22="L","*Leave* - ",IF(Data!C22="A","*Absent* - "))))&IF(OR(Data!C22<>"",Data!D22<>""),TEXT(Data!$A$22,"MM/DD/YY - "),"")&IF(OR(Data!C22<>"",Data!D22<>""),TEXT(Data!$B$22,"DDDD - "),"")&IF(Data!E22<>"",Data!E22,"")&" // "&
IF(Data!C23="","",IF(ISNUMBER(LEFT(Data!C23)*1),"*Late* - ",IF(Data!C23="L","*Leave* - ",IF(Data!C23="A","*Absent* - "))))&IF(OR(Data!C23<>"",Data!D23<>""),TEXT(Data!$A$23,"MM/DD/YY - "),"")&IF(OR(Data!C23<>"",Data!D23<>""),TEXT(Data!$B$23,"DDDD - "),"")&IF(Data!E23<>"",Data!E23,"")&" // "&
IF(Data!C24="","",IF(ISNUMBER(LEFT(Data!C24)*1),"*Late* - ",IF(Data!C24="L","*Leave* - ",IF(Data!C24="A","*Absent* - "))))&IF(OR(Data!C24<>"",Data!D24<>""),TEXT(Data!$A$24,"MM/DD/YY - "),"")&IF(OR(Data!C24<>"",Data!D24<>""),TEXT(Data!$B$24,"DDDD - "),"")&IF(Data!E24<>"",Data!E24,"")&" // "&
IF(Data!C25="","",IF(ISNUMBER(LEFT(Data!C25)*1),"*Late* - ",IF(Data!C25="L","*Leave* - ",IF(Data!C25="A","*Absent* - "))))&IF(OR(Data!C25<>"",Data!D25<>""),TEXT(Data!$A$25,"MM/DD/YY - "),"")&IF(OR(Data!C25<>"",Data!D25<>""),TEXT(Data!$B$25,"DDDD - "),"")&IF(Data!E25<>"",Data!E25,"")&" // "&
IF(Data!C26="","",IF(ISNUMBER(LEFT(Data!C26)*1),"*Late* - ",IF(Data!C26="L","*Leave* - ",IF(Data!C26="A","*Absent* - "))))&IF(OR(Data!C26<>"",Data!D26<>""),TEXT(Data!$A$26,"MM/DD/YY - "),"")&IF(OR(Data!C26<>"",Data!D26<>""),TEXT(Data!$B$26,"DDDD - "),"")&IF(Data!E26<>"",Data!E26,"")&" // "&
IF(Data!C27="","",IF(ISNUMBER(LEFT(Data!C27)*1),"*Late* - ",IF(Data!C27="L","*Leave* - ",IF(Data!C27="A","*Absent* - "))))&IF(OR(Data!C27<>"",Data!D27<>""),TEXT(Data!$A$27,"MM/DD/YY - "),"")&IF(OR(Data!C27<>"",Data!D27<>""),TEXT(Data!$B$27,"DDDD - "),"")&IF(Data!E27<>"",Data!E27,"")&" // "&
IF(Data!C28="","",IF(ISNUMBER(LEFT(Data!C28)*1),"*Late* - ",IF(Data!C28="L","*Leave* - ",IF(Data!C28="A","*Absent* - "))))&IF(OR(Data!C28<>"",Data!D28<>""),TEXT(Data!$A$28,"MM/DD/YY - "),"")&IF(OR(Data!C28<>"",Data!D28<>""),TEXT(Data!$B$28,"DDDD - "),"")&IF(Data!E28<>"",Data!E28,"")&" // "&
IF(Data!C29="","",IF(ISNUMBER(LEFT(Data!C29)*1),"*Late* - ",IF(Data!C29="L","*Leave* - ",IF(Data!C29="A","*Absent* - "))))&IF(OR(Data!C29<>"",Data!D29<>""),TEXT(Data!$A$29,"MM/DD/YY - "),"")&IF(OR(Data!C29<>"",Data!D29<>""),TEXT(Data!$B$29,"DDDD - "),"")&IF(Data!E29<>"",Data!E29,"")&" // "&
IF(Data!C30="","",IF(ISNUMBER(LEFT(Data!C30)*1),"*Late* - ",IF(Data!C30="L","*Leave* - ",IF(Data!C30="A","*Absent* - "))))&IF(OR(Data!C30<>"",Data!D30<>""),TEXT(Data!$A$30,"MM/DD/YY - "),"")&IF(OR(Data!C30<>"",Data!D30<>""),TEXT(Data!$B$30,"DDDD - "),"")&IF(Data!E30<>"",Data!E30,"")&" // "&
IF(Data!C31="","",IF(ISNUMBER(LEFT(Data!C31)*1),"*Late* - ",IF(Data!C31="L","*Leave* - ",IF(Data!C31="A","*Absent* - ")))) OR(Data!C31<>"",
 
I used it for whole but not exceeding the formula limit.
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Kashif => Early - Due // to // Rain
Until now, there was nothing to suggest multiple words in any of the criteria cells, spaces are used to identify the end of the line so a space in the cell text has the same effect.

You're trying to do too much with one formula, it is not going to work. You need to break it down to a formula for each row, which would be easier done with this in Data!H3 (if column H is in use then use a different one), confirm the array then fill down.

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(LEFT(Data!C3)*1),"£",Data!C3)&IF(ISNUMBER(LEFT(Data!D3)*1),"$",Data!D3)&IF(OR(Data!C3:D3<>""),TEXT(Data!$A3,"MM/DD/YY-DDDD"),"")&Data!E3,"L","Leave - "),"A","Absent - "),"£","Late - "),"$","Early - ")

Then join the result of the formulas in column H together using

=Data!C$1&" => "&IF(Data!H3="","",Data!H3&" // ")&IF(Data!H4="","",Data!H4&" // ")

Do you understand how that will work?
 
Upvote 0
Here is the sheet i am using and in Row34 applying the formula.

1595257553885.png
 
Upvote 0
You need 4 columns per employee, not 3. Right click column F, then insert a new column, add the first formula from post 12 to F3 then fill down. It appears that the sheet above is the 'Data' sheet, you do not need to include the sheet name in the formula when you refer to the same sheet, only a different sheet.

In C34 enter this formula, extend it for F5, F6, F7 etc.

=C1&" => "&IF(F3="","",F3&" // ")&IF(F4="","",F4&" // ")

Repeat the above steps for the remaining employees, adjusting the formulas as necessary.
 
Upvote 0
a value used in this formula is of the wrong data type error

This error is occurring. while mention this fromula in F3

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(IF(ISNUMBER(LEFT(Data!C3)*1),"£",Data!C3)&IF(ISNUMBER(LEFT(Data!D3)*1),"$",Data!D3)&IF(OR(Data!C3:D3<>""),TEXT(Data!$A3,"MM/DD/YY-DDDD"),"")&Data!E3,"L","Leave - "),"A","Absent - "),"£","Late - "),"$","Early - ")
 
Upvote 0
The only reason for that would be that you didn't array confirm the formula.
 
Upvote 0
I also make a new sheet for single entry but still this error is showing.
 
Upvote 0
I made it thank you so much for your ever lasting help. You are truly great man indeed.

i am using this below formula row wise.

=IF(Data!C3<>"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(IF(ISNUMBER(LEFT(Data!C3)*1),"£",Data!C3)&IF(OR(Data!C3<>""),TEXT(Data!A3,"MM/DD/YY-DDDD-"),"")&Data!E3&" ")," "," //"),"L","*Leave*-"),"A","*Absent*-"),"£","*Late*-"),"-"," - "),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(
IF(ISNUMBER(LEFT(Data!D3)*1),"£",Data!D3)&IF(OR(Data!D3<>""),TEXT(Data!A3,"MM/DD/YY-DDDD-"),"")&Data!E3&" "
)," "," //"),"£","*Early*-"),"-"," - "))

can this above formula is right to use or there would be other easy way.

and this in C34
=C1&" => "&IF(C3="","",C3&" // ")&IF(C4="","",C4&" // ")&IF(C5="","",C5&" // ")&IF(C6="","",C6&" // ")&IF(C7="","",C7&" // ")&IF(C8="","",C8&" // ")&IF(C9="","",C9&" // ")&IF(C10="","",C10&" // ")&IF(C11="","",C11&" // ")&IF(C12="","",C12&" // ")&IF(C13="","",C13&" // ")&IF(C14="","",C14&" // ")&IF(C15="","",C15&" // ")&IF(C16="","",C16&" // ")&IF(C17="","",C17&" // ")&IF(C18="","",C18&" // ")&IF(C19="","",C19&" // ")&IF(C20="","",C20&" // ")&IF(C21="","",C21&" // ")&IF(C22="","",C22&" // ")&IF(C23="","",C23&" // ")&IF(C24="","",C24&" // ")&IF(C25="","",C25&" // ")&IF(C26="","",C26&" // ")&IF(C27="","",C27&" // ")&IF(C28="","",C28&" // ")&IF(C29="","",C29&" // ")&IF(C30="","",C30&" // ")&IF(C31="","",C31&" // ")&IF(C32="","",C32&" // ")&IF(C33="","",C33&" // ")
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,541
Latest member
iparraguirre89

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top