This project has moved. For the latest updates, please go here.

Formula for comparing values from two different worksheets

Jul 1, 2016 at 9:55 PM
I am creating a workbook that consists of two worksheets. The data from the first sheet is just output from the database for certain weeks, whereas the second worksheet contains a different set of data from different weeks from the database and additionally columns added with formulas to compare to the data in the first worksheet to see the differences.

When writing the code

Worksheet2.Cells(row, newColumn).Formula = "=" & Worksheet2.Cells(row, column).FullAddress & "-" & WorkSheet1.Cells(row, column).FullAddress

When using .Value for the Cell instead of .Formula, I get the correct formula outputted as text to the worksheet, but when I switch it to .Formula, it seems to remove the worksheet names.

.Text version: ='2016-03-27 to 2016-04-09'!H2-'2016-03-13 to 2016-03-26'!H2
'2016-03-27 to 2016-04-09' is the worksheet name

.Formula version: =H2-H2

It appears that the Formula only supports inter-worksheet cells being specified. Is there a way to get the Formulas to work with across worksheets within a same workbook?
Developer
Jul 8, 2016 at 3:55 PM
I am not sure if I understand your problem entirely, but there should not be any problem to reference data between worksheets.

Anyways, you should remove the leading "=" from the formula.

/Mats
Aug 2, 2016 at 5:50 PM
I think you searching for function VLOOKUP
external link
Aug 18, 2016 at 2:50 PM