Have a formula print to different worksheet

[Jack]

In Sheet1, I have a list of 20 products, each with a unique referenc
number in column A. In Sheet2, I have a list of every potentia
reference number, also in column A. I need a formula somewhere i
Sheet1 that matches each reference number to its counterpart in Sheet
and copies the data from that row (Sheet1) to the correct row i
Sheet2.

The biggest problem I'm having with this, is to create a formula in on
cell (or sheet) and have the result appear in another cell/sheet.

Would appreciate any help...

 

[Harald Staff]

Hi

A formula can do one thing only: Display a result in the cell where it is.
It can not push things to remote locations or otherwise alter a spreadsheet.

 

[Jack]

Thanks for the help Harald. That has saved me a whole lot of searching
)

That being the case, can anyone help me edit the following UDF. Thi
appears in every cell in column B (20,000 cells) Sheet2. I
cross-references the value of (Sheet1!A1) with column A, Sheet2 an
prints that row's data to the correct row in Sheet2.
I would need to surround this code with some loop or other tha
increments strg1 to Range A2,A3.....A20 and so copies all 20 product
in Sheet1 to Sheet2 by simultaneously incrementing the data cells a
the end (B2,C2,D2,E2,F2 etc).

I hope that's clear. And thanks in advance for any assistance.

Sub CopyData()

Dim strg1 As Long

Sheets("Sheet1").Select

strg1 = CLng(Range("A1").Value)

Sheets("Sheet2").Select

row_index = 2

Do Until strg1 = Cells(row_index, 1)

row_index = row_index + 1

Loop

Cells(row_index, 2) = Sheets("Sheet1").Range("B1").Value
Cells(row_index, 3) = Sheets("Sheet1").Range("C1").Value
Cells(row_index, 4) = Sheets("Sheet1").Range("D1").Value
Cells(row_index, 5) = Sheets("Sheet1").Range("E1").Value
Cells(row_index, 6) = Sheets("Sheet1").Range("F1").Value

End Su

 

[jammy]

if i understand correctly, you want to get some current info from sheet1
and copy it into a kind of master record in sheet2.

so given that assumption, and bearing in mind that i'm a noob with
excel, i think the following macro will do what you want.

it goes into sheet1, copies the first cell in column A, also copies the
entire row then goes into sheet2 where it looks for the serial number
(or whatever it is) and pastes the copied row from sheet1 into the row
where it finds a match.

being a macro you'll have to run it each time u want it to do
something, but it (hopefully) does what you want...

============================================
Sub CopyData()
'
'
Dim strg1 As Long, crntSel As Object, copyRow As Object

' Get first row of sheet1, copy the ref value and the entire row
Sheets("Sheet1").Select
Range("A1").Select

Set crntSel = Range("A1")

Do Until crntSel.Value = ""
strg1 = CLng(crntSel.Value)
Set copyRow = Rows(ActiveCell.Row)
copyRow.Copy

' Go into sheet2, use the copied ref to find the appropriate row and
paste
Sheets("Sheet2").Select
Columns("A:A").Select

Selection.Find(What:=strg1, After:=ActiveCell,
LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns,
SearchDirection:=xlNext, _
MatchCase:=False).Activate

ActiveCell.PasteSpecial Paste:=xlValues

Sheets("Sheet1").Select
crntSel.Offset(1, 0).Select
Set crntSel = crntSel.Offset(1, 0)
Loop

End Sub
============================================

to use something as a function in a cell like you seem to be suggesting
you'd need to use Function instead of Sub i think. but like i said i'm
a noob so forgive my ignorance if thats not the case.

 

[Jack]

To Jammy:

I cannot thank you enough for that code. It was exactly what I needed
You are no Excel noob. That is incredible.


Regards,

Jac