Help on Lookup

[rowlo-efc]

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?

 

[Bob Phillips]

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)

--
HTH

Bob Phillips

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

 

[Dav]

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

 

[rowlo-efc]

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!!!

 

[Bob Phillips]

which is what I gave you.

--
HTH

Bob Phillips

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