Help with formula

[007juk]

Hi,

I have created a spreadsheet but, have encountered a problem with the
formula or maybe the format, not to sure.

I have a workbook containing 5 worksheets. The first sheet is my
'results' page and the other four contain data, set-up like a database.
I'm using the vlookup formula to populate my cells (column) in my first
sheet.

Column c of sheet 1 looks up the data and populates that column, column
d is for a refresher date and has the following formula -
=IF(ISTEXT(C2),"",IF(C2<>0,DATE(YEAR(C2)+1,MONTH(C2),DAY(C2)),""))

If data is found and column c is populated with a date then column d
creates a date one year on, which works ok. But when I copied the
formula into column d only some of the cells with dates produced a
future date. The only way I can get it to display a date in column d is
to go into the database and re-type the date, when I switch back to me
results page column d is populated.

Thank you.

 

[Dave Peterson]

The easiest thing to check first is tools|Options|calculation tab. Make sure
that it's set for automatic.

If it is automatic, then I'd bet that the things that look like dates aren't
really dates--they're just text that looks like dates.

If you used
=istext(c999)
(but against against a cell that is "broken")
do you see True or False?

If you see True, you could try this:
Select the range of "dates"
Edit|replace
what: / (slash)
with: / (slash)
Replace all

Then check the results of the formula.

 

[007juk]

The easiest thing to check first is tools|Options|calculation tab. Make
sure
that it's set for automatic.

If it is automatic, then I'd bet that the things that look like dates
aren't
really dates--they're just text that looks like dates.

If you used
=istext(c999)
(but against against a cell that is "broken")
do you see True or False?

If you see True, you could try this:
Select the range of "dates"
Edit|replace
what: / (slash)
with: / (slash)
Replace all

Then check the results of the formula.

007juk wrote:-

Dave,

Calculation box is enabled and the formula populated the cell with
'false'

 

[Dave Peterson]

Try one more thing:

Select all the cells on the sheet that are having trouble recalculating:

Edit|Replace
what: = (equal sign)
with: =
replace all

Maybe it'll wake excel up??????