Formula Assistance

  • Thread starter Thread starter jsofillas
  • Start date Start date
J

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!
 
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.
Click to expand...

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


Regards
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
Click to expand...

I get "false" with this formula. When I hit CTRL+Shift+Enter it enters a "{" infront of the "=". Thoughts?
 
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?
Click to expand...



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.
 
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?
Click to expand...

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
 
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
Click to expand...

Ok, got it to result a number now, however the # is 1. In what I have set up, it should be 25,000.
 
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.
Click to expand...



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?
 
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?
Click to expand...




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