Solved: Data_from_another_Spreadsheet

[marreco]

Hi.

I need to return data from worksheet named 'Compras' based cell 'K2' spreadsheet 'Ordem_Compra'

the problem is I can not change the the appearance of the spreadsheet 'Compras'

[PAB]

Hi marreco,

What data do you want to transfer from the Worksheet named 'Compras', what cells or range, and where to you want it to go in the Worksheet named 'Ordem_Compra'?

Regards,
PAB

[marreco]

Hi.
to enter in the worksheet 'Ordem_Compra' the number '2 ' (Cell K2)is returned then the spreadsheet data 'Compras'

See the line with the number '2 'in the worksheet 'Compras'

my attachment was all done manually

[PAB]

Hi marreco,

Do you mean something like this?
Copy and paste this into any cell in the Worksheet named 'Ordem_Compra'.


=Compras!K2
Regards,
PAB

[marreco]

Hi.
is in the worksheet 'Ordem_Compra'
but according to the data

look at my attachment and better understand

[PAB]

Hi marreco,

You want "K2" in the Worksheet named 'Ordem_Compra' copied to where in the Worksheet named 'Compras'?

Regards,
PAB

[marreco]

Hi.
yes!

But it has to be automatic.

If I enter another númeor in 'K2'

[PAB]

Hi marreco,

Copy and paste this into the cell in the Worksheet named 'Compras'.


=Ordem_Compra!K2
Regards,
PAB

[marreco]

Hi.
Not so!!!!!
imagine you are in the worksheet 'Ordem_Compra'

then 'K2' you type the number '9 '

In 'C3' will return 'Bebeto'

In 'C4' will return 'FornNumb_3221'

In 'B9' will return 'Laptop'
In 'F9' will return 'Unidade'
In 'H9' is returned '9 '
In 'I9' is returned '777 '

[PAB]

Hi marreco,

I am having a problem with your Workbook, I think it might be the language or version you are using so I will post the formulas to use.

In cell C3.
=VLOOKUP($K$2,Compras!$A$3:$AR$27,4)
In cell C4.
=VLOOKUP($K$2,Compras!$A$3:$AR$27,3)
In cell B9.
=VLOOKUP($K$2,Compras!$A$3:$AR$27,5)
In cell F9.
=VLOOKUP($K$2,Compras!$A$3:$AR$27,6)
In cell H9.
=VLOOKUP($K$2,Compras!$A$3:$AR$27,7)
In cell I9.
=VLOOKUP($K$2,Compras!$A$3:$AR$27,8)
I hope this helps!

Regards,
PAB

[marreco]

Hi.
PAB can do this with VBA?

I need via MACRO!

Thank you!!!

[PAB]

Hi marreco,

Do the formulas NOT work then?

Regards,
PAB

[marreco]

works well!
the problem 'that my project needs to be based on MACRO.

could you help me? :(

Thank you!

[PAB]

Is this a school or work project?

[marreco]

I had trouble for having formulas in the worksheet because users ended up having to change and for not practicing, did anything wrong.

so I decided to use VBA.

[PAB]

Hi marreco,

Here you go!

Option Explicit
Sub Retrieve_Data()
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
With Sheets("Ordem_Compra").Select
Range("C3:C4, B9:J18").ClearContents
Range("C3").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,4)")
Range("C4").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,3)")
Range("B9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,5)")
Range("F9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,6)")
Range("H9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,7)")
Range("I9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,8)")
End With
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
You still didn't say whether this is a school or work project?
Let me know how this works.

Regards,
PAB

[marreco]

Hi.
I needed to use automatically run without a button.

How do I work through Sub Worksheet_Change (ByVal Target As Range)?



You still didn't say whether this is a school or work project?
Let me know how this works.

Use in my work

[PAB]

Hi marreco,

Put this code in Sheet1(Ordem_Compra).

Sub Worksheet_Change(ByVal Target As Range)
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem_Compra").Select
Range("C3:C4, B9:J18").ClearContents
Range("C3").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,4)")
Range("C4").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,3)")
Range("B9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,5)")
Range("F9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,6)")
Range("H9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,7)")
Range("I9").Value = Evaluate("=VLOOKUP($K$2,Compras!$A$3:$AR$27,8)")
End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
Regards,
PAB

[Teeroy]

Hi marreco,

PAB's code should meet your requirements but will put some (quite small) processing overhead on the PC as it runs every time there's a change to any cell on the sheet. Is there any reason why you couldn't just use formula's (per PAB #10), unlock only the data entry cells, and password protect the sheet?

[marreco]

Hi.
I had to change the formula.
I can help reduce large as the code and make it work?
Thank you!

Sub Worksheet_Change(ByVal Target As Range)
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem de Compra").Select
Range("C3:C4, B9:J18").ClearContents
Range("C3").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),4),"""")")
Range("C4").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),3),"""")")

Range("B9").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),5),"""")")
Range("F9").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),6),"""")")
Range("H9").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),7),"""")")
Range("I9").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),8),"""")")

Range("B10").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),9),"""")")
Range("F10").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),10),"""")")
Range("H10").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),11),"""")")
Range("I10").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),12),"""")")

Range("B11").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),13),"""")")
Range("F11").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),14),"""")")
Range("H11").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),15),"""")")
Range("I11").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),16),"""")")

Range("B12").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),17),"""")")
Range("F12").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),18),"""")")
Range("H12").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),19),"""")")
Range("I12").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),20),"""")")

Range("B13").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),20),"""")")
Range("F13").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),21),"""")")
Range("H13").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),22),"""")")
Range("I13").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),23),"""")")

Range("B14").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),24),"""")")
Range("F14").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),25),"""")")
Range("H14").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),26),"""")")
Range("I14").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),27),"""")")

Range("B15").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),28),"""")")
Range("F15").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),29),"""")")
Range("H15").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),30),"""")")
Range("I15").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),31),"""")")

Range("B16").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),32),"""")")
Range("F16").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),33),"""")")
Range("H16").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),34),"""")")
Range("I16").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),35),"""")")

Range("B17").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),36),"""")")
Range("F17").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),37),"""")")
Range("H17").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),38),"""")")
Range("I17").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),39),"""")")

Range("B18").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),40),"""")")
Range("F18").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),41),"""")")
Range("H18").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),42),"""")")
Range("I18").Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0),43),"""")")



End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

Cross-Post
http://www.mrexcel.com/forum/excel-questions/657178-summarize-code.html

[Teeroy]

This should work up to cell I14. You can extend the arrays to include the rest of the lines (sorry but got bored with data entry).
I've copied in your match locations but please note you appear to have matched col 20 in two places (this may be an error?).

Sub Worksheet_Change(ByVal Target As Range)
Dim aRange
Dim aIndex
Dim i As Integer

With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem de Compra").Select
Range("C3:C4, B9:J18").ClearContents
aRange = Array("C3", "C4", "B9", "F9", "I9", "B10", "F10", "H10", "I10", _
"B11", "F11", "H11", "I11", "B12", "F12", "H13", "I13", _
"B14", "F14", "H14", "I14")
aIndex = Array(4, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 20, 21, 22)

For i = 0 To UBound(aRange)
Range(aRange(i)).Value = Evaluate("=IFERRO(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0)," & aIndex(i) & "),"""")")
Next i

End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

[marreco]

Hi.
is giving the following error
# NAME?

Thank you!!

[Teeroy]

Are you using a different language version of excel (other than English)? When I did this I checked the method and then copied in your statements (not testing on your spreadsheet). After your last post I tested on your spreadsheet and found that within your statements both the sheet name and function name needed to change. The following tests fine for me.

Sub Worksheet_Change(ByVal Target As Range)
Dim aRange
Dim aIndex
Dim i As Integer

With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem_Compra").Select
Range("C3:C4, B9:J18").ClearContents
aRange = Array("C3", "C4", "B9", "F9", "I9", "B10", "F10", "H10", "I10", _
"B11", "F11", "H11", "I11", "B12", "F12", "H13", "I13", _
"B14", "F14", "H14", "I14")
aIndex = Array(4, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 20, 21, 22)

For i = 0 To UBound(aRange)
Range(aRange(i)).Value = Evaluate("=IFERROR(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0)," & aIndex(i) & "),"""")")
Next i

End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

[marreco]

My God what is happening?

I tried adding the rest of the columns in the spreadsheet 'Shopping' and the worksheet rows "Ordem_Compra"

and is giving error 1004
Sub Worksheet_Change(ByVal Target As Range)
Dim aRange
Dim aIndex
Dim i As Integer

With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem de Compra").Select
Range("C3:C4, B9:J18").ClearContents
aRange = Array("C3", "C4", "B9", "F9", "H9", "I9", "B10", "F10", "H10", "I10", _
"B11", "F11", "H11", "I11", "B12", "F12", "H13", "I13", _
"B14", "F14", "H14", "I14", "B15", "F15", "H15", "I15", "B16", "F16", "H16", "I16", _
"B17", "F17", "H17", "I17", "B18", "F18", "H18", "18")
aIndex = Array(4, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 20, 21, 22, 23, _
24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44)

For i = 0 To UBound(aRange)
Range(aRange(i)).Value = Evaluate("=IFERROR(INDEX(Compras!$A$3:$AR$270,MATCH($K$2,Compras!$A$3:$A$270,0)," & aIndex(i) & "),"""")")
Next i

End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub

[PAB]

This works for me.

Sub Worksheet_Change(ByVal Target As Range)
Dim aRange
Dim aIndex
Dim i As Integer
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem de Compra").Select
Range("C3:C4, B9:J18").ClearContents
aRange = Array("C3", "C4", "B9", "F9", "H9", "I9", "B10", "F10", "H10", "I10", _
"B11", "F11", "H11", "I11", "B12", "F12", "H12", "I12", "B13", "F13", "H13", "I13", _
"B14", "F14", "H14", "I14", "B15", "F15", "H15", "I15", "B16", "F16", "H16", "I16", _
"B17", "F17", "H17", "I17", "B18", "F18", "H18", "I18")
aIndex = Array(4, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _
24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44)
For i = 0 To UBound(aRange)
Range(aRange(i)).Value = Evaluate("=IFERROR(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0)," & aIndex(i) & "),"""")")
Next i
End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
Regards,
PAB

[marreco]

Hi.

PAB and Teeroy, you were brilliant, I thank the two for helping me.:clap:

thank you very much!!

[PAB]

If you didn't want the ZERO's to show you could use this.

Sub Worksheet_Change(ByVal Target As Range)
Dim aRange
Dim aIndex
Dim i As Integer
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual:
.EnableEvents = False: .DisplayAlerts = False
End With
With Sheets("Ordem de Compra").Select
Range("C3:C4, B9:J18").ClearContents
aRange = Array("C3", "C4", "B9", "F9", "H9", "I9", "B10", "F10", "H10", "I10", _
"B11", "F11", "H11", "I11", "B12", "F12", "H12", "I12", "B13", "F13", "H13", "I13", _
"B14", "F14", "H14", "I14", "B15", "F15", "H15", "I15", "B16", "F16", "H16", "I16", _
"B17", "F17", "H17", "I17", "B18", "F18", "H18", "I18")
aIndex = Array(4, 3, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, _
24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44)
For i = 0 To UBound(aRange)
Range(aRange(i)).Value = Evaluate("=IFERROR(INDEX(Compras!$A$3:$AR$27,MATCH($K$2,Compras!$A$3:$A$27,0)," _
& aIndex(i) & "),"""")")
If Range(aRange(i)).Value = 0 Then Range(aRange(i)).Value = ""
Next i
End With
With Application
.DisplayAlerts = True: .EnableEvents = True:
.Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Sub
Regards,
PAB

[marreco]

Hi PAB

as I had many questions, so I was embarrassed to ask "how to dispose of ZEROS"

as you told me, I am very very happy to help me again! :rotlaugh:

Thank you very much!