[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 70
  • Last Modified:

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’

=INDIRECT(ADDRESS(COLUMN( )+28,19))

Would appreciate an experts help with this please.
0
Jagwarman
Asked:
Jagwarman
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
Missus Miss_SellaneusCommented:
To make it negative:
=INDIRECT(ADDRESS(COLUMN( )+28,19)) * -1
0
 
Rob HensonIT & Database AssistantCommented:
To answer the question to make the result negative, Miss_Sellaneus has given a solution; alternative would be to just put a minus sign in front of the INDIRECT function:

=-INDIRECT(ADDRESS(COLUMN( )+28,19))

However, this seems a bizarre way round; a simple =$S$35 in G40 would do the same.

What relation is there between the column in which the formula is held and the row that holds the result? If you expand on your scenario, there might be a better way.

Thanks
Rob H
0
 
Pierre CorneliusCommented:
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...
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
JagwarmanAuthor Commented:
The reason I can't just put in a simple =$S$35 is because as I go down the column the next cell does not increment by 1 so it is not = $S$36 it is $S$37. Nothing is ever what it seems.
0
 
JagwarmanAuthor Commented:
Thanks for all your help all very good solutions.
0
 
Pierre CorneliusCommented:
Glad to help.

Just for clarity, then My answer should be the accepted solution and Rob the assisted one...
0
 
Rob HensonIT & Database AssistantCommented:
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.

So I repeat my question, what are you trying to achieve?
0
 
Pierre CorneliusCommented:
Rob, he's fetching a value from another row which is not always the same relative rows above/or below it so just referencing a cell and copying it down won't work.
0
 
JagwarmanAuthor Commented:
I will soon be gone from EE as I am retiring next Friday. Thank you for all your help over the years.
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now