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

0
votes

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))
1
excelexcel-2007excel-formula
Why don't you just... drag the formula ? – ApplePie
Oh and you shouldn't use entire columns as your range, they will slow your workbook. – ApplePie
Haha I am so embarrassed, this worked. I didn't think it would know to increment that! Thanks! embarrassed emoticon – tob88
No worries. Happens to the best of us sometimes. – ApplePie

1 Answers

2
votes

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