• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Retrieve one numerical value in a row spanning multiple columns [SOLVED]

F

flywhiz

New Member
I have a row that spans several columns. In that row, there will only be one numerical amount in one of the multiple columns. On another sheet (sheet 2), how can I create a formula that spans the row of columns (on sheet 1) to find and display the single numerical amount regardless of which column it may be in?
 
Hello flywhiz,

You could try something like

=LOOKUP(9999,A1:H1)


Replace 9999 with a value that is larger than anything you would find in that row.


If the row does not contain any numbers, the formula would return an error. You can use IFERROR(...) to trap it, if desired.


Cheers,

Sajan.
 
If I understand your question enter this formula on Sheet2 A1 and drag to copy it to the right and it will compare the cells in Sheet1 and show the Value or a "-" if it is not a number. Let me know if this is what you are needing or if you need more than that.


Code: 
=IF(ISNONTEXT(Sheet1!1:1),Sheet1!A1,"-")


EDIT* Lookup()
from Sajan is much better!
 
Hi, thanks for the replies. If I use the lookup formula, how do I reverence going to sheet 1 and looking in a46:z46? Also, what I am looking for is a value >0. Thanks.
 
Hello flywhiz,

=LOOKUP(9999,Sheet1!A46:Z46)


will return any value in those columns (of course, assuming that that value is less or equal to 9999 in this case).


However, if you want to only return a value if the number found is greater than 0, then you could use something like the following:

=LOOKUP(9999, IF(Sheet1!A46:Z46+0>0, Sheet1!A46:Z46+0))


enter with Ctrl + Shift + Enter


(Please remember to enclose your sheet name in quotes if it contains spaces. Or better yet, when writing the formula, select the relevant cells from Sheet1, and Excel will do the work for you!)


Cheers,

Sajan.
 
Thanks Sajan! I copied and pasted your formula above and changed Sheet1 to the actual name of the sheet. It looks like this:


=LOOKUP(9999, IF(Budget!A46:Z46+0>0, Budget!A46:Z46+0))


But, it returns:


#VALUE!


Don't know why.
 
Hi flywhiz,

You need to enter the formula and end with Ctrl + Shift + Enter


i.e. type in the formula, but instead of pressing enter, press the key combination Ctrl + Shift + Enter


Cheers,

Sajan.
 
Hi, flywhiz!


Would you please clarify this for me?

a) The row that spans multiple columns is the header row and the data in one of the columns is in the next rows, or is it in the very same row?

b) If the last option, how did the data get there and how would you update it?


Consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


Regards!
 
Thanks everyone. With your help I figured it out and it is working like a charm!


Your help has been wonderful!
 
Hi, flywhiz!

Maybe you want to share your solution with the community, so as people who read this would have the issue or question and the solution or answer as well.

Regards!
 
Hi ,


Shouldn't this work ?


=MAX(IF(ISNUMBER(Sheet1!A10:Z10),Sheet1!A10:Z10))


entered as an array formula , using CTRL SHIFT ENTER.


@Sajan , the test >0 will not catch negative numbers !


Narayan
 
Hi Narayan,

I think the OP wanted the value only if it was greater than zero.


Regards,

Sajan.
 
Hi Narayan,

:) Agreed! ISNUMBER() certainly would be a more generic approach, and MAX() is more logical to use than LOOKUP().


By the way, in case the numbers are formatted as text, I would still recommend adding a zero so that the result is as expected:

=MAX(IF(ISNUMBER(Sheet1!A10:Z10+0),Sheet1!A10:Z10+0))


Regards,

-Sajan.
 
Sajan,


Interesting aspect of is that ISNUMBER does not evaluate the formatting of cells. It checks the VALUE or VALUE returned by FORMULA of cell.


I tried to check the following case

1

2

3

4


and then manually formatted them to text and tested it gives TRUE for all of them. So formatting has no impact.


Instead write a formula.

=2&""

or

'2

and then test it, it gives FALSE.


So if all numbers are formatted as Text Narayan's formula shall work as it is. But small overhead of adding zero shouldn't hurt either if we do not know OP's data and where it is coming from :)
 
Hi Shrivallabha ,


I think we should start referring to you as Chandoopedia ! Thanks for the expert touch.


These are the fine points that should be consolidated into one single document , or in one separate place in this forum , with the appropriate tags.


Narayan
 
Hi everyone. I'm happy to share what I found that worked for me. First a little explanation on what I'm doing. This is a budget spreadsheet. Each column represents a pay period - two per month. As time goes on and I move from pay period to pay period, I move the check book balance figure to the next column. The savings account running total is directly below this. So from another sheet in the file, I was trying to read which column displayed the check book balance and then read what the savings account balance was directly below. The following worked perfectly with your help. Thanks again!


=LOOKUP(MAX(Budget!C46:Z46),Budget!C46:Z46,Budget!C47:Z47)
 
flywhiz,


There are some formulas which will also work just for your info.

=HLOOKUP(MAX(Budget!C46:Z46),Budget!C46:Z47,2,0)

and ubiquitous INDEX/MATCH

=INDEX(Budget!C47:Z47,1,MATCH(MAX(Budget!C46:Z46),Budget!C46:Z46,0))
 
Thanks shrivallabha! Good to know. Copied them and pasted them in the spreadsheet just to try them out and they both worked perfectly.
 
You must log in or register to reply here.
Back
Top