Formula from one sheet to another

Question

I have an excel book with two sheets Sheet 1: Contains Date which is presented as a list and then a table with values from A-L Sheet2 : Contains a table with date and serial numbers and values for each col.

Query: I am doing Hlookup from sheet 1 to sheet 2, to get values from sheet 2. which I am successful.

I do not want to manually copy paste the Hlookup formula..as I have a dataset of 200 rows.

I am also writing a formula to display values from Sheet 2 if available if not just maintain Sheet 1 value...

I have the formula written out..I need a way to automatically update the formula in each cell value in sheet 1.

Please find the attached sheet for more information. I have attached a dummy dataset.

Actual formula looks like:

IF(HLOOKUP(C3,'Pec-29'!D3:$X$94,3,0)="","Desk 46",(HLOOKUP(C3,'Pec-29'!D3:$X$94,3,0)))

I have an index from 3-235... I don't want to paste this one by one

Can you please edit your question to include sample data and expected outcome (don't just link to a worksheet). If needed, you can upload screencaps to imgur.com and link to them here — cybernetic.nomad
Maybe use row(3:3) instead of 3, then it will change as you copy the rows down? Or just row() if the formula starts in row 3? Or possibly don't use dollars and just address each row in 'Pec-29' e.g. D3:X3 ? — Tom Sharpe
By the way at time of writing we can't find the attached sheet. — Tom Sharpe

HeD_pE HeD_pE · Accepted Answer · 2019-07-04T15:08:49

If your goal is to do this without copying the formula to the other cells then the only solution is to build a macro that loops all the rows and does the same operation as the formula. Below is a generic example of a VBA macro procedure, open the VBA editor with ALT+F11. Hope it helps.

Sub test()
    rng = ActiveSheet.Range("D7:D23")
    For Each cl In rng
        ' do something with this row
    Next cl
End Sub

Formula from one sheet to another

1 Answers