Formula Assistance

jsofillas · External Since: Jul 19, 2012 Posts: 6

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.

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!

jsofillas · External Since: Jul 19, 2012 Posts: 6

On Thursday, July 19, 2012 9:49:27 AM UTC-4, (unknown) wrote:
> 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 :
> >
> > > 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
> > --
> > 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 it enters 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.

jsofillas · External Since: Jul 19, 2012 Posts: 6

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 :
>
> > 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.

jsofillas · External Since: Jul 19, 2012 Posts: 6

On Thursday, July 19, 2012 10:02:15 AM UTC-4, (unknown) wrote:
> 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 :
> >
> > > 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 · External Since: Jul 19, 2012 Posts: 6

On Thursday, July 19, 2012 10:15:53 AM UTC-4, (unknown) wrote:
> On Thursday, July 19, 2012 10:02:15 AM UTC-4, (unknown) wrote:
> > 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 :
> > >
> > > &gt; I get &quot;false&quot; with this formula. When I hit CTRL+Shift+Enter it enters a &quot;{&quot; infront of the &quot;=&quot;. Thoughts?
> > >
> > > that&#39;s correct. When you hit CTRL+Shift+Enter you have to get &quot;{&quot; in
> > > front and &quot;}&quot; 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!

Claus Busch · External Since: Oct 27, 2009 Posts: 113

Hi,

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

> 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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

jsofillas · External Since: Jul 19, 2012 Posts: 6

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 :
>
> > 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
> --
> 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 it enters a "{" infront of the "=". Thoughts?

Claus Busch · External Since: Oct 27, 2009 Posts: 113

Hi,

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

> 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

Spencer101 · External Since: Apr 01, 2012 Posts: 29

;1603795 Wrote:
> 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.
>
> 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,

Perhaps an example workbook would yield a quicker answer on this for
you...

--
Spencer101