Formula Assistance

[jsofillas]

I have the following formula that currently works in my spreadsheet where Ihave a row of #'s that I need to return the one to the furthest right (new#'s get inserted into the range on a regular basis). The formula is =LOOKUP(9^99,46:46).

I now need to flip the formula so that it returns the # furthest to the left in row 46.

I'm not sure if I am explaining it right, but basically I need a cell to = say I46....where eventually new data will get inserted into column I and the # that was in I46 will get pushed to O46, but the formula needs to stay stuck on I46 and return the new value in I46.

Any help on this would be greatly appreciated. If I didn't explain this right or more info is needed, let me know.

Thanks!

 

[Claus Busch]

Hi,

Am Thu, 19 Jul 2012 06:35:50 -0700 (PDT) schrieb (e-mail address removed):

I have the following formula that currently works in my spreadsheet where I have a row of #'s that I need to return the one to the furthest right (new #'s get inserted into the range on a regular basis). The formula is =LOOKUP(9^99,46:46).

I now need to flip the formula so that it returns the # furthest to the left in row 46.

try:
=INDEX(46:46,MATCH(TRUE,ISNUMBER(46:46),0))
and enter with CTRL+Shift+Enter


Regards
Claus Busch

 

[jsofillas]

Hi,

Am Thu, 19 Jul 2012 06:35:50 -0700 (PDT) schrieb (e-mail address removed):

> I have the following formula that currently works in my spreadsheet where I have a row of #'s that I need to return the one to the furthest right (new #'s get inserted into the range on a regular basis). The formula is =LOOKUP(9^99,46:46).
>
> I now need to flip the formula so that it returns the # furthest to the left in row 46.

try:
=INDEX(46:46,MATCH(TRUE,ISNUMBER(46:46),0))
and enter with CTRL+Shift+Enter


Regards
Claus Busch

I get "false" with this formula. When I hit CTRL+Shift+Enter it enters a "{" infront of the "=". Thoughts?

 

[jsofillas]

On Thursday, July 19, 2012 9:45:01 AM UTC-4, Claus Busch wrote:
> Hi,
>
> Am Thu, 19 Jul 2012 06:35:50 -0700 (PDT) schrieb (e-mail address removed):
>
> > I have the following formula that currently works in my spreadsheet where I have a row of #'s that I need to return the one tothe furthest right (new #'s get inserted into the range on a regular basis). The formula is =LOOKUP(9^99,46:46).
> >
> > I now need to flip the formula so that it returns the # furthest to the left in row 46.
>
> try:
> =INDEX(46:46,MATCH(TRUE,ISNUMBER(46:46),0))
> and enter with CTRL+Shift+Enter
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

I get "false" with this formula. When I hit CTRL+Shift+Enter itenters a "{" infront of the "=". Thoughts?

I'm sorry, I left out that the data in Columns A-F will not change This formula is getting put in D43 and the range starts in G and goes all the way right.

 

[Claus Busch]

Hi,

Am Thu, 19 Jul 2012 06:49:27 -0700 (PDT) schrieb (e-mail address removed):

I get "false" with this formula. When I hit CTRL+Shift+Enter it enters a "{" infront of the "=". Thoughts?

that's correct. When you hit CTRL+Shift+Enter you have to get "{" in
front and "}" behind the formula. The formula is an array formula.
Paste the formula into your cell, press F2 and then CTRL+Shift+Enter


Regards
Claus Busch

 

[jsofillas]

Hi,

Am Thu, 19 Jul 2012 06:49:27 -0700 (PDT) schrieb (e-mail address removed):

> I get "false" with this formula. When I hit CTRL+Shift+Enter it enters a "{" infront of the "=". Thoughts?

that's correct. When you hit CTRL+Shift+Enter you have to get "{" in
front and "}" behind the formula. The formula is an array formula.
Paste the formula into your cell, press F2 and then CTRL+Shift+Enter


Regards
Claus Busch

Ok, got it to result a number now, however the # is 1. In what I have set up, it should be 25,000.

 

[jsofillas]

On Thursday, July 19, 2012 9:56:29 AM UTC-4, Claus Busch wrote:
> Hi,
>
> Am Thu, 19 Jul 2012 06:49:27 -0700 (PDT) schrieb (e-mail address removed):
>
> > I get "false" with this formula. When I hit CTRL+Shift+Enter it enters a "{" infront of the "=". Thoughts?
>
> that's correct. When you hit CTRL+Shift+Enter you have to get "{" in
> front and "}" behind the formula. The formula is an array formula.
> Paste the formula into your cell, press F2 and then CTRL+Shift+Enter
>
>
> Regards
> Claus Busch
> --
> Win XP PRof SP2 / Vista Ultimate SP2
> Office 2003 SP2 /2007 Ultimate SP2

Ok, got it to result a number now, however the # is 1. In what I have set up, it should be 25,000.

Do you think it has anything to do with the formula looking at the entire row, instead of it just looking at Column G where the data that counts is starting?

 

[jsofillas]

56:29 AM UTC-4, Claus Busch wrote:
> > Hi,
> >
> > Am Thu, 19 Jul 2012 06:49:27 -0700 (PDT) schrieb (e-mail address removed):
> >
> > > I get "false" with this formula. When I hit CTRL+Shift+Enter it enters a "{" infront of the "=". Thoughts?
> >
> > that's correct. When you hit CTRL+Shift+Enter you have to get "{" in
> > front and "}" behind the formula. The formula is an array formula.
> > Paste the formula into your cell, press F2 and then CTRL+Shift+Enter
> >
> >
> > Regards
> > Claus Busch
> > --
> > Win XP PRof SP2 / Vista Ultimate SP2
> > Office 2003 SP2 /2007 Ultimate SP2
>
> Ok, got it to result a number now, however the # is 1. In what I have set up, it should be 25,000.



Do you think it has anything to do with the formula looking at the entire row, instead of it just looking at Column G where the data that counts is starting?

Yep, that was it. I changed it to G and now it is working properly. Thank you!