PDA

View Full Version : [SOLVED] Use formula to grab data from sheets



YasserKhalil
07-23-2017, 04:53 PM
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
07-24-2017, 05:21 AM
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
07-24-2017, 05:37 AM
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
07-24-2017, 06:03 AM
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 footinmout
http://www.vbaexpress.com/forum/showthread.php?40035-Volatile-Functions-and-closed-workbooks

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

Tom Jones
07-24-2017, 06:28 AM
So, you want with INDIRECT?

YasserKhalil
07-24-2017, 06:40 AM
Thanks a lot for replies
Yes I need to refer to the sheet name according to column A ...

mdmackillop
07-24-2017, 06:40 AM
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
07-24-2017, 06:45 AM
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
07-24-2017, 07:12 AM
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
07-24-2017, 07:44 AM
Thanks a lot. I tried that but got no results

Tom Jones
07-24-2017, 08:30 AM
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
07-24-2017, 08:38 AM
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)),"")