CODEWITHSUNDEEP
excelexcel-2007excel-formula
tob88
tob88

Reputation: 2211

How to reference value in cell to the left within a formula

I have 2 sheets in my spreadsheet. Sheet2 pulls information from Sheet1. In sheet2, there are 2 columns. Column A has company names. Column B has a formula which searches for the company name of that row within sheet1, and sums the values from that row in sheet1.

I have been able to achieve this with the following formula.

=SUMPRODUCT((Sheet1!B:B=A1)*(Sheet1!F:F))

This works fine, however I have to manually type "A1" into the formula. For the other rows, I would have to write B1, C1, D1 etc.

I have searched for how to reference the cell to the left, and I found this formula...

=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -1)

This seems to work as a standalone formula in a cell, but I cannot figure out how to incorporate this into the SUMPRODUCT formula. Anything I try gives errors. I need something like this.

=SUMPRODUCT((Sheet1!B:B=(=OFFSET(INDIRECT(ADDRESS(ROW(), COLUMN())), 0, -1)))*(Sheet1!F:F))

Upvotes: 0

Views: 12356

Answers (1)

ApplePie
ApplePie

Reputation: 8942

As per pnuts' suggestion to make it an answer:

The answer is just to drag the formula around. Just make sure to fix (either use F4 or add dollar signs) your range if it is not an entire column or an entire row

Upvotes: 2

Related Questions