Use formula to grab data from sheets

[YasserKhalil]

Hello everyone
I have the following code that enables me to grab data from three sheets to the sheet named "TABLEAU"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$G$1" Then
        Dim ws      As Worksheet
        Dim sh      As Worksheet
        Dim c       As Range
        Dim cc      As Range
        
        Set sh = Sheets("TABLEAU")
        
        Application.ScreenUpdating = False
            For Each c In sh.Range("A9:A11")
                For Each ws In ThisWorkbook.Worksheets
                    If InStr(ws.Name, c.Value) > 0 Then
                        For Each cc In ws.Range("B5:B" & ws.Cells(Rows.Count, 2).End(xlUp).Row)
                            If cc.Value = sh.Range("C5").Value Then
                                c.Offset(, 1).Resize(1, 5).Value = cc.Offset(, 1).Resize(1, 5).Value
                                Exit For
                            Else
                                c.Offset(, 1).Resize(1, 5).Value = ""
                            End If
                        Next cc
                    End If
                Next ws
            Next c
        Application.ScreenUpdating = True
    End If
End Sub

Is it possible to use formulas instead?

[Tom Jones]

Hi,

Cause I don't understand Arabian language to make more efficient formula:
try this formula in B9 and drag to right

=INDEX('January 2017 Wage'!$C$5:$G$9;MATCH($C$5;'January 2017 Wage'!$B$5:$B$9;0);COLUMN(C5)-2)

In B10 change 'January 2017 Wage' with 'February 2017 Wage' then drag to right formula then change to March...

[YasserKhalil]

Thanks a lot Mr. Tom and I am sorry for confusing
The formula is good and I can get correct results but I seek to use INDIRECT to refer to the sheet name based on range A9:A11 in sheet TABLEAU ...
So I need one formula for all months as there are other sheets for months and it will be exhausted to edit each formula to suit

[shrivallabha]

Just one word of caution. INDIRECT doesn't work with closed workbooks and may give incorrect results.

It's a lesson that I learnt the hard way
http://www.vbaexpress.com/forum/show...osed-workbooks

Gist: don't use it unless there's no other way!

[Tom Jones]

So, you want with INDIRECT?

[YasserKhalil]

Thanks a lot for replies
Yes I need to refer to the sheet name according to column A ...

[mdmackillop]

Get rid of your merged cells!!! Use "Centre Across Selection"

In B9 =INDEX(OFFSET((INDIRECT("'" & $A9 & "'!C:C")),,COLUMN()-2),MATCH($C$5,(INDIRECT("'" & $A9 & "'!$B:$B")),0))
Copy across and down

[YasserKhalil]

Thanks a lot Mr. MD for great help
I have made little change to suit

=IFERROR(INDEX(OFFSET((INDIRECT("'" & $A9 & " Wage'!C:C")),,COLUMN()-2),MATCH($C$5,(INDIRECT("'" & $A9 & " Wage'!$B:$B")),0)),"")

[Tom Jones]

Try this:

=IFERROR(INDEX(INDIRECT("'"&$A9&" Wage'!$C$5:$G$16");MATCH($C$5;INDIRECT("'"&$A9&" Wage'!$B$5:$B$16");0);MATCH(B$8;INDIRECT("'"&$A9&" Wage'!$C$4:$G$4");0));" - ")

with that is not necessary have in order H1...H5, it could be in any order.

[YasserKhalil]

Thanks a lot. I tried that but got no results

[Tom Jones]

just because you don't copy my formula...
your formula
=IFERROR(INDEX(INDIRECT("'"&$A10&" Wage'!$C$5:$G$16");MATCH($C$5:INDIRECT("'"&$A10&" Wage'!$B$5:$B$16");0);MATCH(B$8:INDIRECT("'"&$A10&" Wage'!$C$4:$G$4");0));"")

and mine:
=IFERROR(INDEX(INDIRECT("'"&$A9&" Wage'!$C$5:$G$16");MATCH($C$5;INDIRECT("'"&$A9&" Wage'!$B$5:$B$16");0);MATCH(B$8;INDIRECT("'"&$A9&" Wage'!$C$4:$G$4");0));" - ")

if you use US regional settings then that formula will be:

=IFERROR(INDEX(INDIRECT("'"&$A9&" Wage'!$C$5:$G$16"),MATCH($C$5,INDIRECT("'"&$A9&" Wage'!$B$5:$B$16"),0),MATCH(B$8;INDIRECT("'"&$A9&" Wage'!$C$4:$G$4"),0))," - ")

[YasserKhalil]

Thanks a lot for great help Mr. Tom
This is the last one that works fine

=IFERROR(INDEX(INDIRECT("'"&$A9&" Wage'!$C$5:$G$16"),MATCH($C$5,INDIRECT("'"&$A9&" Wage'!$B$5:$B$16"),0),MATCH(B$8,INDIRECT("'"&$A9&" Wage'!$C$4:$G$4"),0)),"")