Solved: Help understanding CONCATENATE formula

[coliervile]

I had come acrossed this workbook sometime ago on VBAEXPRESS and I can't understand part of this formula: =IF(COUNTIF(PTO!$B$13:$IV$13,Stats!G232)>0,"PTO",IF(COUNTIF(Holidays!$B$13:$IV$13,Stats!G232)>0,"Holiday",IF(VLOOKUP($A234,Schedules!$A$15:$H$22,G$1,FALSE)="Off","Off",IF(VLOOKUP($A234,Schedules!$A$15:$H$22,G$1,FALSE)="7a",CONCATENATE("7a-",$K234),CONCATENATE(VLOOKUP($A234,Schedules!$A$15:$H$22,G$1,FALSE),"-",Stats!$L234)))))

I understand what the formula does upto this part: CONCATENATE("7a-",$K234),CONCATENATE(VLOOKUP($A234,Schedules!$A$15:$H$22,G$1,FALSE),"-",Stats!$L234)))))

I can't understand what CONCATENATE is doing and how the cells in column K and L tie into it?????

Attached is a copy of the original file and would greatly appreciate it if someone could help me understand what CONCATENATE is doing in this particular formula. :bug:

Regards,
Charlie

[mdmackillop]

Concatenate joins 2 or more strings into 1. To see this, add some text into Sheets (Stat), columns K & L.

[coliervile]

Thanks MD for answering. I understand that CONCATENATE joins strings together, but what I don't understand is how the cell E4 on worksheet "Stats" is getting it value of "8a-" and if CONCATENATE is some how tied in to it? I typed data into column K and it added it to "7a-", but I typed in data in to column L, but it didn't add it????

Regards
Charlie

[mdmackillop]

Column L is joined to 8a- values.

[coliervile]

I think I got it column L is tied to "7a-" and column K is tied to 8a- and if cell E4 isn't = to "7a-" then this part of the formula ( "CONCATENATE(VLOOKUP($A3,Schedules!$A$15:$H$22,C$1,FALSE),"-",Stats!$L3)))))" )looks on the Schedule worksheet and gets "8a-" and if there's any data in column L it will add it to 8a- e.g. "8a-Trial". I think that's correct?

Best regards
Charlie

[mdmackillop]

Not only 8a as can be seen. VLookup provides the first part which could be 4p, 6:30p and possibly others. (I've not tried to follow all the variations)

[coliervile]

I think you're probably correct. I'll try it out and see if it works. Is this part of the formula "CONCATENATE(VLOOKUP($A3,Schedules!$A$15:$H$22,C$1,FALSE),"-",Stats!$L3" actually easier than that for "7a-", "IF(VLOOKUP($A234,Schedules!$A$15:$H$22,G$1,FALSE)="7a",CONCATENATE("7a-",$K234)"??? More streamed lined?

I'll mark this item closed.

Best regards,
Charlie