Posts by Anri

Re: Change Time AM PM to Duration only

No problem. Thank you for your advise.

Re: Change Time AM PM to Duration only

Okay, thank you.
How do I escalate that there is no resolve yet? Should I repost this to get more eyes on the question?

Re: Change Time AM PM to Duration only

Pastel pulls through what is in the fx field, not what is displayed. I show below what it looks like on my side.
[ATTACH=CONFIG]72173[/ATTACH]

Re: Change Time AM PM to Duration only

Thank you, I did use this, but it does not remove the "AM" and "PM" in the cell.

I have to import the duration of our machine cutting times from Excel to Pastel, but the time format in Excel is shown in [h]:mm:ss AM and PM. How do I change this to just show time duration?. There are no formulas. I am just filling in the duration with each ended process.

Product Code Product Description Machine Duration ([h]:mm:ss)

Re: "tilde" in vlookup formula

I have found an easier way to look for values with a the tilde in VLOOKUP:

=VLOOKUP(SUBSTITUTE(A2,"~","~~"),Table_array,Col_index_num,Range_lookup)

This is a bit less complex, but your way works as well.

Thank you very much for all your help.

Re: "tilde" in vlookup formula

Oh, okay.

What do I do if that is constantly changing. People who will be working in this document will not know how to change it.

Re: "tilde" in vlookup formula

Noted. Thanks.

What does the "8" and "6" mean that you used?

Re: "tilde" in vlookup formula

Thanks.

I edited your formula a bit in PM2.3.xlsm[ATTACH=CONFIG]71901[/ATTACH] to read the full column.
When I copy and paste (values) to change the pastel codes - the Description changes to #NA error PM2.4.xlsm[ATTACH=CONFIG]71902[/ATTACH]

Please advise.

Re: "tilde" in vlookup formula

Thank you - when I click in the cell with your formula and then click out of it I get an error #VALUE
I notice if I click on the cell with your formula you have these brackets in front and after { }
When I click in the sell it goes away. If I then click enter - I get a #VALUE error. Replacing it in front and back does not resolve the problem.

I want to edit the formula to read the full column instead of set cells.

Please advise and thank you for assisting me.

Re: "tilde" in vlookup formula

Thank you - I will test this now.

Hi. I saw this post from 2005 and would like to know if you can give me the correct sum for my document. I do not understand the formula "Batman" posted. I do not want to remove the tilde as the document has to have the exact code we use in Pastel.
http://www.ozgrid.com/forum/showthread.php?t=36548

forum.ozgrid.com/index.php?attachment/71895/

"SubstoreProductMaster/Description (B2; B27 etc)" is reading from "SubstoreBuyPlan/PastelCode/"

Re: Functions do not work the cell contains "~" sign??

forum.ozgrid.com/index.php?attachment/71851/Hello

I hope that if I post a reply it goes back to the top ... of the list...

I have this same problem, but do not want to replace the tilde. I can not make sense of the formula Batman posted. Please advise on the attached document.
Sheet in document: "SubstoreProductMaster/Description (B2; B27 etc)" is reading from "SubstoreBuyPlan/PastelCode"

Regards,
Anri

Re: Import codes from once sheet to the other

Success! You are my favourite person today! Thank you sooo much.

Re: Import codes from once sheet to the other

Hi. This is not working - please advise what I am doing wrong.
https://1drv.ms/x/s!AskK8ngh660bgbNuooNMWwBsaWCc0w

The data from sheet: "SubstoreBuyPlan" should show in every 25th row in "SubstoreProductMaster"

Sub ImportProdCode()
    Dim x, i As Long, cnt As Long
    x = Application.Transpose(SubstoreBuyPlan.Range("A2:A" & SubstoreBuyPlan.Range("A" & Rows.Count).End(xlUp).Row))
    cnt = 1
    For i = LBound(x) To UBound(x)
        SubstoreProductMaster.Cells(i + cnt, 1).Value = x(i)
        cnt = cnt + 25
    Next i
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Re: Import codes from once sheet to the other

Great - thank you.

Re: Import codes from once sheet to the other

My apologies - I just joined. Will keep this in mind.

.. and thank you - I will test this.

Copy every row from sheet 2 to every 25th row in sheet1 in excel

Sheet I want the information in:
[ATTACH=CONFIG]71816[/ATTACH]

Sheet I am getting the codes from:
[ATTACH=CONFIG]71817[/ATTACH]