Solved

Indirect formula

Posted on 2016-11-15
9
45 Views
Last Modified: 2016-11-16
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
Comment
Question by:Jagwarman
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 12

Assisted Solution

by:Missus Miss_Sellaneus
Missus Miss_Sellaneus earned 125 total points
ID: 41887891
To make it negative:
=INDIRECT(ADDRESS(COLUMN( )+28,19)) * -1
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 250 total points
ID: 41887940
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
 
LVL 14

Assisted Solution

by:Pierre Cornelius
Pierre Cornelius earned 125 total points
ID: 41888024
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 41889231
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
 

Author Closing Comment

by:Jagwarman
ID: 41889243
Thanks for all your help all very good solutions.
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 41889334
Glad to help.

Just for clarity, then My answer should be the accepted solution and Rob the assisted one...
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41889345
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
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 41889365
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
 

Author Comment

by:Jagwarman
ID: 41889447
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Copy Sheet to New Workbook based on Cell Value 6 58
VBA routine modification/tweak needed 10 53
Vlookup Help 3 28
need count any combinaton of 4 numbers 7 23
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

732 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question