Prev: Background or Header picture should cover entire A4 sheet when pri
Next: disable sounds when saving
From: Greg on 18 Jan 2010 08:41
Hi everyone.

My question has to do with the 2nd to last cell with data in a column. To
get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the
data from the cell right above that?

For instance, my data looks like this (multiple tabs for different entities):

# of Accounts 1/1/10 45
$ of Accounts 1/1/10 6300
# of Accounts 1/2/10 23
$ of Accounts 1/2/10 1550

So, I want my totals page to show the last 2 entries (# and $).

TIA, any help would be wonderful,

Thanks,

Greg
From: Jacob Skaria on 18 Jan 2010 08:52
Try the below to get the data from the cell right above that?

With data in ColA

=INDEX(A:A,MATCH(10^10,A:A)-1)

--
Jacob


"Greg" wrote:

> Hi everyone.
>
> My question has to do with the 2nd to last cell with data in a column. To
> get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the
> data from the cell right above that?
>
> For instance, my data looks like this (multiple tabs for different entities):
>
> # of Accounts 1/1/10 45
> $ of Accounts 1/1/10 6300
> # of Accounts 1/2/10 23
> $ of Accounts 1/2/10 1550
>
> So, I want my totals page to show the last 2 entries (# and $).
>
> TIA, any help would be wonderful,
>
> Thanks,
>
> Greg
From: Greg on 18 Jan 2010 08:59
Worked like a charm.

Thank you very much,

Greg

"Jacob Skaria" wrote:

> Try the below to get the data from the cell right above that?
>
> With data in ColA
>
> =INDEX(A:A,MATCH(10^10,A:A)-1)
>
> --
> Jacob
>
>
> "Greg" wrote:
>
> > Hi everyone.
> >
> > My question has to do with the 2nd to last cell with data in a column. To
> > get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the
> > data from the cell right above that?
> >
> > For instance, my data looks like this (multiple tabs for different entities):
> >
> > # of Accounts 1/1/10 45
> > $ of Accounts 1/1/10 6300
> > # of Accounts 1/2/10 23
> > $ of Accounts 1/2/10 1550
> >
> > So, I want my totals page to show the last 2 entries (# and $).
> >
> > TIA, any help would be wonderful,
> >
> > Thanks,
> >
> > Greg
From: Mike H on 18 Jan 2010 08:59
Greg,

You question is a bit muddled, you ask for the second to last value in a
column yet your formula is a row (A2:D2), which is it. Assumin no blanks try
these

Row
=INDEX(A2:D2,COUNT(A2:D2)-1)
column
=INDEX(A1:A6,COUNT(A1:A6)-1)
--
Mike

When competing hypotheses are equal, adopt the hypothesis that introduces
the fewest assumptions while still sufficiently answering the question.
Occam''''s razor (Abbrev)


"Greg" wrote:

> Hi everyone.
>
> My question has to do with the 2nd to last cell with data in a column. To
> get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the
> data from the cell right above that?
>
> For instance, my data looks like this (multiple tabs for different entities):
>
> # of Accounts 1/1/10 45
> $ of Accounts 1/1/10 6300
> # of Accounts 1/2/10 23
> $ of Accounts 1/2/10 1550
>
> So, I want my totals page to show the last 2 entries (# and $).
>
> TIA, any help would be wonderful,
>
> Thanks,
>
> Greg
From: Greg on 18 Jan 2010 09:19
You are correct. I used a copied formula template (A2:D2) for my example.
The actual number I was looking for was in a column.

My fault.

Thank you for your input. Having both formulas is great.

Greg

"Mike H" wrote:

> Greg,
>
> You question is a bit muddled, you ask for the second to last value in a
> column yet your formula is a row (A2:D2), which is it. Assumin no blanks try
> these
>
> Row
> =INDEX(A2:D2,COUNT(A2:D2)-1)
> column
> =INDEX(A1:A6,COUNT(A1:A6)-1)
> --
> Mike
>
> When competing hypotheses are equal, adopt the hypothesis that introduces
> the fewest assumptions while still sufficiently answering the question.
> Occam''''s razor (Abbrev)
>
>
> "Greg" wrote:
>
> > Hi everyone.
> >
> > My question has to do with the 2nd to last cell with data in a column. To
> > get the last data, I am using "=LOOKUP(10^100,A2:D2)" but how can I get the
> > data from the cell right above that?
> >
> > For instance, my data looks like this (multiple tabs for different entities):
> >
> > # of Accounts 1/1/10 45
> > $ of Accounts 1/1/10 6300
> > # of Accounts 1/2/10 23
> > $ of Accounts 1/2/10 1550
> >
> > So, I want my totals page to show the last 2 entries (# and $).
> >
> > TIA, any help would be wonderful,
> >
> > Thanks,
> >
> > Greg
 |  Next  |  Last
Pages: 1 2
Prev: Background or Header picture should cover entire A4 sheet when pri
Next: disable sounds when saving