Hi,

You dont need to change the formula you can lookup that last value in a row
like this:-

=LOOKUP(2,1/(Sheet1!A2:Z2<""),Sheet1!A2:Z2)

Finding the second to last involves an array formula, see below

INDEX(Sheet1!A2:Z2,LARGE(IF(Sheet1!A2:Z2<"",COLUM N(Sheet1!A2:Z2)-COLUMN(A2)+1),2))

Note both these formula work on the range A2 to Z2 so change to suit. In the
second formula the last 2 denotes find the 2nd to last value so change to a 3
for the third to last etc but note the array formula instructions below.

This is an array formula which must be entered by pressing CTRL+Shift+Enter
'and not just Enter. If you do it correctly then Excel will put curly brackets
'around the formula {}. You can't type these yourself. If you edit the formula
'you must enter it again with CTRL+Shift+Enter.

Mike


"JackRosieMaisie" wrote:

A sheet contains a list of prices that change weekly - 30 products vertically
each with a column for each weeks price.

Each week a new column is added to the end of the sheet with that week's
price.

Another sheet has a summary of the products and the starting price and
finishing price on a monthly basis.

As the columns with the two prices change inconsistently each month I need
an easy way for an Excel novice to change the column references in the
summary formulas for the starting and finishing prices.

Question: Can a formula contain a column variable which is lookedup from
another cell. E.g. two new reference cells for the starting and finishing
price which contain the two column reference values that all relevant
formulas would automatically update to.

I appreciate any help.