Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
sparx
 
Posts: n/a
Default Cell Date / Currency Convert


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.


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488676

  #2   Report Post  
Posted to microsoft.public.excel.misc
JE McGimpsey
 
Posts: n/a
Default Cell Date / Currency Convert

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.

  #3   Report Post  
Posted to microsoft.public.excel.misc
sparx
 
Posts: n/a
Default Cell Date / Currency Convert


Thanks for your help - it works fine - I will use this for certain.


--
sparx
------------------------------------------------------------------------
sparx's Profile: http://www.excelforum.com/member.php...o&userid=16787
View this thread: http://www.excelforum.com/showthread...hreadid=488676

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
if data in one cell, then date in adjacent cell Jane Excel Worksheet Functions 8 December 22nd 07 04:34 AM
Need help: convert seconds to date and time misty1 Excel Discussion (Misc queries) 2 November 26th 05 05:12 AM
Change cell to red when date in other cell is red cityfc Excel Discussion (Misc queries) 3 November 10th 05 11:27 PM
Addition to Turn cell red if today is greater or equal to date in cell Rich New Users to Excel 2 December 9th 04 03:06 AM
Extract date from cell Eric Excel Worksheet Functions 3 November 4th 04 07:37 PM


All times are GMT +1. The time now is 10:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"