=SUBSTITUTE(LEFT(A5,FIND(" ",A5)+2)," ","-",1)

and copy down]]>

=SUBSTITUTE(LEFT(A5,FIND(" ",A5)+2)," ","-",1)

and copy down

]]>which sheet? the first in the file? data ("gegevens") ?]]>

which sheet? the first in the file? data ("gegevens") ?

]]>Linear regression analysis in Excel]]>

It was my mistake. The closed workbook wasn't updated for some days.

STUPID ME.]]>

It was my mistake. The closed workbook wasn't updated for some days.

STUPID ME.

]]>Example:

A1 = 1 B1='J:\Data\2021\Dec\[1.xlsx]Prelim'!$D$7 C1='J:\Data\2021\Dec\[1.xlsx]Audit Entry'!$M$10

A2 = 2 B2='J:\Data\2021\Dec\[2.xlsx]Prelim'!$D$7 C2='J:\Data\2021\Dec\[2.xlsx]Audit Entry'!$M$10

A3 = 3 B3='J:\Data\2021\Dec\[3.xlsx]Prelim'!$D$7 C3='J:\Data\2021\Dec\[3.xlsx]Audit Entry'!$M$10

Is it possible to dynamically link the external worksheet such as [1.xlsx] somehow? I tried entering as [A1.xlsx] but wouldn't work and haven't been able to find a solution. Any assistance would be greatly appreciated.

]]>Thank you. I will make a calculation.]]>

This is a case of getting the maths clear..

For example ...

Lets assume the selling price = 1.00 (or 100%) and the selling price = all costs and all commissions and the desired profit.

If thew desired profit = 25 cents in every dollar then the assumption is that costs and commissions are 75 cents and the markup % is 33%.

Are you able to calculate the total of costs and commissions THEN add your desired markup?

Of course ..if the commissions are based on the end selling price it is a little more complicated but the same rationalse applies and it may be necessary (but not good practice) to enable iterative calculations (Options / Formulas).

From a maths (algebra) perspective you should be able to apply the maths in either direction.

Let me know if I can help further.

Thank you. I will make a calculation.

]]>I have put the code into the attached]]>

Code

- Option Explicit
- Function VLookupMulti(ByVal strIndex As String, ByVal rng As Range, _
- Optional ref As Integer = 1, Optional myJoin As String = " ", _
- Optional myOrd As Boolean = True) As String
- '
- ' jindon
- ' http://www.mrexcel.com/forum/showthread.php?t=344561&highlight=muvlookup
- '
- ' =VLookUpMulti(A1, C1:D100, 2, ",", False)
- '
- Dim a, b(), i As Long, n As Long
- a = rng.Value
- ReDim b(1 To UBound(a, 1), 1 To 2)
- With CreateObject("Scripting.Dictionary")
- .CompareMode = vbTextCompare
- For i = 1 To UBound(a, 1)
- If a(i, 1) = strIndex Then
- If Not .exists(a(i, ref)) Then
- .Add a(i, 2), Nothing
- n = n + 1: b(n, 1) = a(i, 2)
- b(n, 2) = IIf(IsNumeric(a(i, 2)), a(i, 2), UCase(a(i, 2)))
- End If
- End If
- Next
- End With
- VSortM b, 1, n, 2, False
- For i = 1 To n
- VLookupMulti = VLookupMulti & IIf(VLookupMulti = "", "", myJoin) & b(i, 1)
- Next
- End Function

I have put the code into the attached

]]>In report, worksheet time will be summed up based on ID from the Downtime Sheet.

Thanks.]]>

In report, worksheet time will be summed up based on ID from the Downtime Sheet.

Thanks.

]]>Very good point, thank you.. I'll rearrange the layout and try again.

Much appreciated..

Deryn]]>

Very good point, thank you.. I'll rearrange the layout and try again.

Much appreciated..

Deryn

]]>