Evaluate referring other File

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Evaluate referring other File

Post by adeel1 »

Hi All
i am unable to use evaluate function to referring other file

1st file (opened) code exists in this file (named SFA)
Set m = ThisWorkbook.Sheets("sheet2")

2nd file (will search file name)
x.name is file name (which is opened)
Set sf = Workbooks(x.Name)
Set sf2 = sf.Sheets("Configurations")

below code isn't working if i made small changes it is workable with in file sheet to sheet but not working with referring file

Code: Select all

sf2.Cells(i, "j") = Evaluate("Sheet2!INDEX(a2:a" & p & ",SMALL(IF(""" & sf2.Cells(i, "n") & """=Sheet2!e2:e" & p & ",ROW(Sheet2!E2:E" & p & "),""""),COUNTIF('[x.name]Configurations'!n3:n" & i & ",""" & sf2.Cells(i, "n") & """)))")
without sample may its look hard and with my poor English also but i have to delete lot of data for this ,i am sure great mind always here

Adeel

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Evaluate referring other File

Post by HansV »

Shouldn't it be

sf2.Cells(i, "j") = Evaluate("INDEX(Sheet2!a2:a" & p & ",SMALL(IF(""" & sf2.Cells(i, "n") & """=Sheet2!e2:e" & p & ",ROW(Sheet2!E2:E" & p & "),""""),COUNTIF('[x.name]Configurations'!n3:n" & i & ",""" & sf2.Cells(i, "n") & """)))")
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Evaluate referring other File

Post by adeel1 »

thnx for quick reply like always

now its returning Ref error, may still i am missing something
Capture.PNG
Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Evaluate referring other File

Post by HansV »

I'd have to see sample workbooks.
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Evaluate referring other File

Post by adeel1 »

thnx for help, need to delete lot of data/formulas for this, let tight my mind for a while will come here with sample books, :thankyou:

User avatar
rory
5StarLounger
Posts: 817
Joined: 24 Jan 2010, 15:56

Re: Evaluate referring other File

Post by rory »

You've got x.name as a literal part of the formula. I think it should be:

Code: Select all

sf2.Cells(i, "j") = Evaluate("INDEX(Sheet2!a2:a" & p & ",SMALL(IF(""" & sf2.Cells(i, "n") & """=Sheet2!e2:e" & p & ",ROW(Sheet2!E2:E" & p & "),""""),COUNTIF('[" & x.name & "]Configurations'!n3:n" & i & ",""" & sf2.Cells(i, "n") & """)))")
Regards,
Rory

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Evaluate referring other File

Post by adeel1 »

thnx for your help :clapping: , will give it try

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Evaluate referring other File

Post by adeel1 »

my apology to continue here as this is almost similar to this one

correct results are in Col e and f but with code i am able to get these with evaluate function

Code: Select all

Sub eva()
Columns("g:h").Clear
p = Cells(Rows.Count, 4).End(3).Row
pp = Cells(Rows.Count, 1).End(3).Row

For i = 2 To p
Cells(i, "g") = Evaluate("SUM(IFERROR(INDEX(B2:B" & pp & ",IF(" & Cells(i, "d") & "=A2:A" & pp & ",ROW(A2:A" & pp & ")-1,"""")),""""))")

Cells(i, "h") = Evaluate("TEXTJOIN("","",,IFERROR(INDEX(c2:c" & pp & ",IF(" & Cells(i, "d") & "=A2:A" & pp & ",ROW(A2:A" & pp & ")-1,"""")),""""))")
Next i
End Sub
Adeel
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Evaluate referring other File

Post by HansV »

A much simpler formula for column E is

=SUMIF($A$2:$A$16,D2,$B$2:$B$16)

And for column H:

=TEXTJOIN(",",TRUE,IF($A$2:$A$16=D2,$B$2:$B$16,""))

Based on this, you can use

Code: Select all

Sub eva()
    Dim p As Long
    Dim pp As Long
    Dim i As Long
    p = Cells(Rows.Count, "D").End(3).Row
    pp = Cells(Rows.Count, "A").End(3).Row
    Range("G:H").ClearContents
    For i = 2 To p
        Cells(i, "G").Value = Evaluate("SUMIF(A2:A" & pp & ",D" & i & ",B2:B" & pp & ")")
        Cells(i, "H").Value = Evaluate("=TEXTJOIN("","",TRUE,IF(A2:A" & pp & "=D" & i & ",B2:B" & pp & ",""""))")
    Next i
End Sub
Best wishes,
Hans

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Evaluate referring other File

Post by adeel1 »

ohh No........ :hairout: :hairout:
yes 110% correct, i unnecessary complicated it i should be think like this

may its not going over for a while, my will be back with more issues if found, much much thnx :clapping: :clapping:

Adeel