Can you format cells to transfer text automatically?

Posted by Jeff on January 07, 2002 9:50 AM

I've created a monthly workbook with several different report sheets in it. Although I'm still very much a novice with Excel.

What I would like to do is to format different cells on different sheets in this workbook, so that when I type in "Jan. 2002" in the month/year cell on my first sheet report, that it will automatically show up in the month/year cells on all other reports in my monthly workbook.

Although those are different cells on each of these report sheets.

I can do this with numbers, but don't know how to with "text" like the word "January". I've been told that I might could create a link, but I've never done that. Could someone tell me how to do this? I would need instructions for the "simple-minded".

thanks,
Jeff

Posted by Chris D on January 07, 2002 1:27 PM

Hi Jeff,

the theory with text is the same as numbers....

Let's say your January date is in the first sheet called, say, "Summary" in cell A1, and you want this month to appear in your second sheet (called "Personnel") in cell B5, and also your third sheet (called "Planning") in cell C2 (all for example)

You just go to your Personnel sheet and in cell B5 you type:

+
then use your mouse to click on cell A1 in your Summary sheet and hit enter

this tells cell B5 in your Personnel sheet that it is equal to the value of A1 in your summary sheet. Whenever A1 is changed, the link will update itself and display the new value accordingly (see, as with a number).

Then, repeat this in all the other sheets you want to read the original info in the Summary sheet - in this example :

+
then use your mouse to click on cell C2 in your Planning sheet and hit enter

this tells cell C2 in your Planning sheet that it is equal to the value of A1 in your summary sheet. Whenever A1 is changed, the link will update itself and display the new value accordingly (again, as with a number).

If your first sheet (ie Summary) has January 2002 in it and you find that these new links show something weird like "34587" it is just that the new links haven't been formatted from the Microsoft "Star Date" to normal date : just click on :

format
cell
date

and pick a date format that suits you (ie mmmm/yyyy)

I use this in many of my worksheets, where there are 50 sheets that I print out, all of which are headed with the date - rather than type the date in 50 times, I type it once into my "summary" sheet and then just link the other 50 to it...

Hope this helps
Chris

Posted by Jeff on January 07, 2002 4:35 PM

wow Chris, thanks!......this worked great!......I stumbled just for a minute due to my first sheet's cell being two cells merged......and my second sheet's cell wasn't......and it gave me the ol "#value" thing.......but when I deleted the second half of the merged cell out of the formula, bingo!........appreciate it very much!........Jeff

Posted by Jeff on January 08, 2002 8:44 AM

here's a ps.........I have fixed up this workbook for several of my fellow officers..........and when I went to fix up one of the other workbooks, I had to leave both cells code of the merged cell in the formula in order for it to work on the second sheet's cell which is a merged cell also.....which was exactly the same thing that was making the first workbook's formula not work!.........go figure!........but either way, they are all working now........so thanks much! wow Chris, thanks!......this worked great!......I stumbled just for a minute due to my first sheet's cell being two cells merged......and my second sheet's cell wasn't......and it gave me the ol "#value" thing.......but when I deleted the second half of the merged cell out of the formula, bingo!........appreciate it very much!........Jeff

Posted by Chris D on January 08, 2002 9:38 AM

anytime, Jeff ! (NT)