PDA

View Full Version : Concatenate Conditional Long String in Formula



elsuji
08-29-2019, 08:49 AM
I have a excel file whch is havind 2 work sheets (Data & Sheet1)

In sheet1 the following to be updated in merged place from Data

We here by certify that the Batching Plant Model H1J bearing Sl. No. 101 supplied by us to M/s. Sri Nandi Conmix., was calibrated on 04-02-2019 by our Service Engineer Mr. Senthil Kumar P in presence of Mr. Umesh (Partner - M/s. Sri Nandi Conmix), Mr. Sathish (Incharge - M/s. Sri Nandi Conmix) and the calibration details are enclosed herewith. We certify that all the parameters are well within the tolerance limit.


We here by certify that the Batching Plant Model (from Data!B5) bearing Sl. No. (from Data!B6) supplied by us to (from Data!B3)., was calibrated on (from Data!B1) by our Service Engineer Mr.(from Data!B15) in presence of (from Data!B16, C16, D16), (from Data!B17, C17, D17),(from Data!B18, C18, D18),(from Data!B19, C19, D19 ) and the calibration details are enclosed herewith. We certify that all the parameters are well within the tolerance limit.

If (from Data!B16, C16, D16), (from Data!B17, C17, D17), (from Data!B18, C18, D18), (from Data!B19, C19, D19) any of it is blank then it wont update here.

Pls tell me how to write the formula for the above condition.

The sample file is attached for reference

24896

paulked
08-29-2019, 09:25 AM
Sub CheckBlanks()
If WorksheetFunction.CountBlank(Sheets("Data").Range("B16:D19")) > 0 Then Exit Sub
MsgBox "No blanks"
End Sub

will check the range you specified

elsuji
08-29-2019, 09:48 PM
Thanks for your reply.

The above code is inky for check the blanks. But how can I copy from data sheet to sheet1

paulked
08-30-2019, 04:09 AM
You've almost written the code yourself, instead of brackets and from, use " and &, there are thousands of examples on this forum alone. Go on, give it a go and, if you struggle, post your code and we'll help you out.

I would check your spelling and grammar too, that wouldn't wash in the UK :wink:

elsuji
08-30-2019, 08:12 PM
Hi Paulked,

I write the below formula. But is not working. Can you please check this

=CONCATENATE (“We here by certify that the Batching Plant Model”, Data!B5, “bearing Sl. No.”, Data!B6, “supplied by us to”, Data!B3,”., was calibrated on”, Data!B1, “by our Service Engineer”, Data!B15, “in presence of” Data!B16, “(“, Data!C16, “-“, Data!D16, “)”,Data!B17, “(“, Data!C17, “-“, Data!D17, “)”,Data!B18, “(“, Data!C18, “-“, Data!D18, “)”, Data!B19, “(“, Data!C19, “-“, Data!D19, “)”, “and the calibration details are enclosed herewith. We certify that all the parameters are well within the tolerance limit.”

paulked
08-30-2019, 09:24 PM
=CONCATENATE("Hello ", D7, ". I'm here to help, not to do your job!")
If "Paul Ked" is in cell D7, what would be the output?