How should the formula decide which column to look at? Should it look at
any particular place within the column?

Perhaps this may give an idea.

Assume that the dates are in sheet2!A:A and the prices in sheet2!B:B.
Further assume that Sheet1!A1 will be either 0 or 1. If it's 0, then the
formula in A2 should return the date from the same row as the calling
cell, if it's 1, the A2 should return the price.

Then

=IF(A1=0,TEXT(Sheet2!A1,"dd mmmm yyyy"), TEXT(Sheet2!B1,"$#,##0.00"))

or, equivalently

=OFFSET(Sheet2!$A$1,ROW()-1,A1)




In article ,
sparx wrote:

Hello All, I have a worksheet that in one cell I want either a price or
date shown - at present I have a formula that looks within a
spreadsheet 2 from either 2 columns - one column is a date formatted
and the other column is price formatted - is there a way to have a
formula that will change its view automatically to either price or date
depending upon which column it looks at.