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'.

[vba]
=Compras!K2 [/vba]
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'.

[VBA]
=Ordem_Compra!K2 [/VBA]
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.
[vba]=VLOOKUP($K$2,Compras!$A$3:$AR$27,4)[/vba]
In cell C4.
[vba]=VLOOKUP($K$2,Compras!$A$3:$AR$27,3)[/vba]
In cell B9.
[vba]=VLOOKUP($K$2,Compras!$A$3:$AR$27,5)[/vba]
In cell F9.
[vba]=VLOOKUP($K$2,Compras!$A$3:$AR$27,6)[/vba]
In cell H9.
[vba]=VLOOKUP($K$2,Compras!$A$3:$AR$27,7)[/vba]
In cell I9.
[vba]=VLOOKUP($K$2,Compras!$A$3:$AR$27,8)[/vba]
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!

[vba]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[/vba]
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).

[vba]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[/vba]
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!

[VBA]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[/VBA]

Cross-Post
http://www.mrexcel.com/forum/excel-q...rize-code.html