Why can't I link to a cell that contains a date or a time?

  • Thread starter Thread starter Meenie
  • Start date Start date
M

Meenie

I have Excel 2003.
I have a list of Meetings that my 2 bosses attend.
I have one list that lists all the meetings and is alphabatized.
Then I have another sheet in the same workbook that lists the meetings for
each boss individually that has more info (ie: names of people attending if
the boss is running the meeting - for emailing invites, minutes, etc, also
info if the meeting changes meeting places on different dates - same place
not always available every month)
I have these columns:
Meeting Date Time Contact name Contact Number Who attends Room

Under "date" it usually says something like "2nd Monday each Month"

I wanted to link from the main page of all meetings to each bosses meetings
so if the date or time changed, it would change on their page also but here's
what happened:
I linked from the main page of all meetings to each bosses page under
"date" simply by entering an equal sign on the bosses page and then clicking
on the main page under date for each meeting. This worked fine unless there
was an actual date in the cell (instead of text - like 1st Monday each
month). If there was an actual date, then in the bosses sheet the link would
be written out instead of the info from the first sheet. (example:
='Recurring Mtgs Alphabetized'!B37:C37)

I also wanted to link the times, but they do the same thing.

Why can't I link to a date or time?
Thanks ... Meenie
 
Sounds like the cells have been hard formatted for you. Easiest way to fix is
to type 1 into a blank cell. Copy that 1, highlight your date column, right
click, select multiply and hit ok. This should chnage them to the values
instead...
 
hmmm when I highlight the column and right click, "multiply" isn't an option
in the box that opens up.
 
the columns that contain the info as well as the columns that the info links
to are formatted as "text" not as dates or times.
 
Sean forgot one step. Right click, select Paste Special..., Multiply and OK
out.

Regards,
Fred.
 
That works! Well, I'd have never have figured that one out! How odd. I
suppose there's some meaning behind it.

Thank you!
 
As explained, your source data was text. Numeric formulas, date
manipulation, etc. don't work with text. They need numbers. However, one of
the quirks of Excel is that there are many circumstances where you can force
it to convert text to numbers. Paste Special/Multiply is one. Text to
columns is another one.

Regards,
Fred.
 
thank you, Fred.
I had those columns set to "text" because in some I wasn't putting an actual
date or time. I didn't know that linking to them would be a problem :)
and I sure would never have thought to use "multiply" to fix it!!
Thanks so much for your help, I really appreciate it.
Next time I suppose I should just make another column, but was saving space
for printing purposes, hehe. At least now I know to try the 1 w/paste
specian/multiply!
Thanks again!
 
Back
Top