Indirect formula

I am using the below formula to get data from column ‘S’ however I need to turn the numbers prom positive to Negative.
I am using this formula because the numbers I need to get are not on the same row as the position it will end up in. i.e. in the example below the formula is in cell ‘G40’ and the data it is calling is in ‘S35’

Yes, changing to negative is easy but I note the problem based on your example. You are using column as the first parameter for the address function whereas it expects a row number there. Likewise for the second parameter, you probably give row instead of column.

So based on your example, if you want to have G40 be equal to negative S35 use this formula:
=-INDIRECT(ADDRESS(ROW()-5,COLUMN()+12))

The minus 5 is because row 40 (G40) less row 35 (S35) = 5
The +12 is because column G to S spans 12 columns

But Rob is Right, why not just reference the cell directly? Unless maybe it is not always 5 rows above and 12 columns to the right? Maybe you calculate the relative cell somehow where to fetch the data I guess...

With COLUMN()+28 and going down column G, you are still going to have row 35; column G is 7 and 7+28 =35

If as Pierre suggests you have the Row and Column parameters back to front in the ADDRESS function, adding a fixed value of 28 to row or column is still going to give the same relative reference; in the same way as copying and pasting a relative formula.

Copy =S35 from G40 and paste down two cells into G42 it will give =S37.

Featured Post

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

What is a Form List Box? (skip if you know this)
The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…