Help on Lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rowlo-efc - 25 Jul 2006 11:10 GMT

I have a spreadsheet that contains two worksheets (a & b).

In worksheet b (which is hidden) all of the data is kept for a test
that is run each day. (user form is used for data entry). from this
data, a total is calculated (nothing serious, just =sum function).

The data is entered in columns and the total is calculated at the
bottom of every other column (i.e every day data is entered into two
columns, C&D, with the total being calculated at the foot of column D
and so on).

On Worksheet (a) I want to create a summary of the days data -
including the total result. Can I make a cell show the latest total
without having to manually tell the cell in WS (a) where to find the
data every day?

i.e. can I make a cell lookpup data in the last unavailable cell across
a row?

Reply to this Message

Bob Phillips - 25 Jul 2006 11:46 GMT

Got a bit confused as to whether you wanted a column or a row. This finds
the last value in a column

=LOOKUP(2,1/(Sheet2!C1:C1000<>""),Sheet2!C1:C1000)

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I have a spreadsheet that contains two worksheets (a & b).
>
[quoted text clipped - 14 lines]
> i.e. can I make a cell lookpup data in the last unavailable cell across
> a row?

Reply to this Message

Dav - 25 Jul 2006 11:50 GMT

Accross a row, te following entered as an array. shft Ctrl Enter The row
is row6 in the example
=OFFSET(A6,0,MAX(IF(6:6>0,COLUMN(6:6),0)-1))

But do you mean down a column, in which case it would be
=OFFSET(A1,MAX(IF(A:A>0,row(A:A),0)-1),0)

Again entered as an array

Regards

Dav

Signature

Dav

Reply to this Message

rowlo-efc - 25 Jul 2006 13:36 GMT

okay - thinking of a similar analogy.

Imagine a golf handicap system.

the user enters data from each round of golf played in columns (a score
for each hole). This is then calculated as a total and then the total
is used to work out a 'running' handicap that is worked out from the
previous handicap value in the cell before (in the same row).

What I have is...

We run a test on oil samples that gives various data - calculated
together (in a simple equation), gives the amount of nitrogen in the
oil (boring!)

We do this daily and from the calculated nitrogen - we have a running
total that works out the difference between todays result and
yesterdays result.

For example.

We have a two columns for each day. each column runs from row 3 - row
10. Then in row 11 the data is added together to give a total. row 12 -
a simple calculation gives us another figure - the amount of nitrogen.
Row 13 takes the row 12 figure away from the previous row 12 figure.

The user inputs data using an excel user form. I would like the first
worksheet to have a cell that shows the running (latest) value
calculated in row 13...

few this is a bit complicated!!!

Reply to this Message

Bob Phillips - 25 Jul 2006 13:47 GMT

which is what I gave you.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> okay - thinking of a similar analogy.
>
[quoted text clipped - 27 lines]
>
> few this is a bit complicated!!!

Reply to this Message