|
Formula Assistance
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! |
Formula Assistance
|
Formula Assistance
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? |
Formula Assistance
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 |
Formula Assistance
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. |
Formula Assistance
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? |
Formula Assistance
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 : > > > > > 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! |
Quote:
Hi, Perhaps an example workbook would yield a quicker answer on this for you... |
| All times are GMT +1. The time now is 10:13 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com