FINDING THE LAST ENTERED NUMBER IN A ROW OF DATA
in [Functions]
|
From: Morton Detwyler Morton on 5 Jul 2008 14:19 I have a table of data that is used to input a product metric every month. Product names are in column [A], and number headings are in Row [1] (i.e. 1,2,3,4.....). We do not use month names. Metrics are then entered into each row horizontally, for the list of products. Because of certain minimum rules, not all products receive a metric every month, so each product has a varied amount of metrics. To the right of these metrics, is a column that performs a calculation on only the "last" entered metric for each product. I'd like my formula in this far right column to be able to look to the left and find the "last" entered metric. I'm stumped as to how to do this. Any help would be greatly appreciated. Thank you....
From: Pete_UK on 5 Jul 2008 15:13 Assuming your metrics can be entered in columns C to X, put this in a cell to the right on row 2 to give you the last number entered in that range: =LOOKUP(10^10,C2:X2) Adjust the range to suit, and then copy down. Hope this helps. Pete On Jul 5, 7:19 pm, Morton Detwyler <Morton Detwy...(a)discussions.microsoft.com> wrote: > I have a table of data that is used to input a product metric every month.. > Product names are in column [A], and number headings are in Row [1] (i.e. > 1,2,3,4.....). We do not use month names. Metrics are then entered into each > row horizontally, for the list of products. Because of certain minimum > rules, not all products receive a metric every month, so each product has a > varied amount of metrics. To the right of these metrics, is a column that > performs a calculation on only the "last" entered metric for each product.. > I'd like my formula in this far right column to be able to look to the left > and find the "last" entered metric. I'm stumped as to how to do this. Any > help would be greatly appreciated. Thank you....
From: Rick Rothstein (MVP - VB) on 5 Jul 2008 15:22 Whatever column you put your formula in, use the column before it for the end of the row range. For example, if put your formula in AA, then use this formula to get the contents of the last filled in cell before Column AA... =LOOKUP(2,1/(A2:Z2<>""),A2:Z2) You can copy this formula down. Rick "Morton Detwyler" <Morton Detwyler(a)discussions.microsoft.com> wrote in message news:56F580D6-BA8A-486E-BF09-C44EA271B0EA(a)microsoft.com... >I have a table of data that is used to input a product metric every month. > Product names are in column [A], and number headings are in Row [1] (i.e. > 1,2,3,4.....). We do not use month names. Metrics are then entered into > each > row horizontally, for the list of products. Because of certain minimum > rules, not all products receive a metric every month, so each product has > a > varied amount of metrics. To the right of these metrics, is a column that > performs a calculation on only the "last" entered metric for each product. > I'd like my formula in this far right column to be able to look to the > left > and find the "last" entered metric. I'm stumped as to how to do this. Any > help would be greatly appreciated. Thank you....
From: Morton Detwyler on 5 Jul 2008 16:44 Thank you so much....it works perfectly! Can I ask why the expression "10^10" is used in the formula? "Pete_UK" wrote: > Assuming your metrics can be entered in columns C to X, put this in a > cell to the right on row 2 to give you the last number entered in that > range: > > =LOOKUP(10^10,C2:X2) > > Adjust the range to suit, and then copy down. > > Hope this helps. > > Pete > > > On Jul 5, 7:19 pm, Morton Detwyler <Morton > Detwy...(a)discussions.microsoft.com> wrote: > > I have a table of data that is used to input a product metric every month.. > > Product names are in column [A], and number headings are in Row [1] (i.e. > > 1,2,3,4.....). We do not use month names. Metrics are then entered into each > > row horizontally, for the list of products. Because of certain minimum > > rules, not all products receive a metric every month, so each product has a > > varied amount of metrics. To the right of these metrics, is a column that > > performs a calculation on only the "last" entered metric for each product.. > > I'd like my formula in this far right column to be able to look to the left > > and find the "last" entered metric. I'm stumped as to how to do this. Any > > help would be greatly appreciated. Thank you.... > >
From: Morton Detwyler on 5 Jul 2008 16:46
Thank you so much....it works perfectly! Can I ask why "/(A2:Z2<>"")" is used in the formula? "Rick Rothstein (MVP - VB)" wrote: > Whatever column you put your formula in, use the column before it for the > end of the row range. For example, if put your formula in AA, then use this > formula to get the contents of the last filled in cell before Column AA... > > =LOOKUP(2,1/(A2:Z2<>""),A2:Z2) > > You can copy this formula down. > > Rick > > > "Morton Detwyler" <Morton Detwyler(a)discussions.microsoft.com> wrote in > message news:56F580D6-BA8A-486E-BF09-C44EA271B0EA(a)microsoft.com... > >I have a table of data that is used to input a product metric every month. > > Product names are in column [A], and number headings are in Row [1] (i.e. > > 1,2,3,4.....). We do not use month names. Metrics are then entered into > > each > > row horizontally, for the list of products. Because of certain minimum > > rules, not all products receive a metric every month, so each product has > > a > > varied amount of metrics. To the right of these metrics, is a column that > > performs a calculation on only the "last" entered metric for each product. > > I'd like my formula in this far right column to be able to look to the > > left > > and find the "last" entered metric. I'm stumped as to how to do this. Any > > help would be greatly appreciated. Thank you.... > > |