Need help on four different topics.
 

I know I might not get all four of these answered, but any help on any
one of these will help.

1) When I use the formula to reference the sheet name (tab name), in a
cell --- =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) ---
It assigns ALL cells with this instance, even on different sheets.
For instance, I have a workbook that contains 20+ sheets. I want it
to display the name of each sheet, in a particular cell of the
corresponding sheets. But when I entered this formula into a second,
third or forth worksheet cell, it changes all results to the last
worksheet, I’ve updated. So Sheet1, Sheet2 and Sheet3 will all have
the result -Sheet3- in each of their A1 cells. And If I go back and
update Sheet2's formula, then all three Sheet cells will say -Sheet2-
Is there another formula that will give the results I'm looking for?
Or is there something I'm entering wrong that is causing this to act
this way?



2)When paste the "value" from another cell into a new cell, in some
instances, it does not recognize it as a value. It give me the error
#NAME? It's not until I select the cell and press enter. Then it
will recognize the contents as what it really is. Example is, I use
the "CONCATENATE" function which combines the results of multiple cells
into on.


Cell: A1 Formula: '=Sum( Result: =Sum(
Cell: A2 Formula: '1+ Result: 1+
Cell: A3 Formula: '2) Result: 2)
Cell: A4 Formula: =CONCATENATE(A1,A2,A3) Result: =Sum(1+2)

So then I copy and paste the result of A4 into a new cell by just
pasting the value and it just displays =Sum(1+2) in the cell. Not the
result of the formula (3). Its not until I select the cell again
and press enter, that it updates the result with the number 3.

I know this sounds like a small deal, but I have over 5,000 cells I've
pasted these types of values in, and short of selecting each cell and
pressing enter, I cant find another way to update these cells. When
I try to select all worksheets and update them all at one time, it will
of course, change all cells to the same formula.

Any Ideas?



3)Is there a way to change the color of a cell, just during the
editing/data entry stage? But change it back during the print stage?
I have sheets that require a lot of info to be entered in adjacent
cells, and it's easy to lose track on where you are. So it would be
nice to shade the cells in which data needs to be entered but when
printing, I want it all to be white.


4)Is there a way to temporarily disable the formula transition. When
dragging the formulas into adjacent cells, it also changes the cells in
which the info is coming from. Basically, I want to have the same
EXACT formula in each cell by just dragging up/down or side to side.





Again, thanks for any help that I can get on any of these four
questions.


--
mrmag2000
------------------------------------------------------------------------
mrmag2000's Profile: http://www.excelforum.com/member.php...o&userid=35878
View this thread: http://www.excelforum.com/showthread...hreadid=556725

Need help on four different topics.
 

Answering question 1: Try
=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255), the A1 in the
formula could be any cell reference on the sheet but it makes sure that the
current sheet is referenced.
Answering question 2: Maybe don't use concatenate, just use =A1&" "&A2&" "&A3.
Answering question 3: Not that I know of, and the workarounds are more
complicated than just keeping track of where you are.
Answering question 4: Make cell references absolute, use $A$1 as opposed to
A1.
--
Hope this helps,
MarkN


"mrmag2000" wrote:


I know I might not get all four of these answered, but any help on any
one of these will help.

1) When I use the formula to reference the sheet name (tab name), in a
cell --- =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) ---
It assigns ALL cells with this instance, even on different sheets.
For instance, I have a workbook that contains 20+ sheets. I want it
to display the name of each sheet, in a particular cell of the
corresponding sheets. But when I entered this formula into a second,
third or forth worksheet cell, it changes all results to the last
worksheet, Ive updated. So Sheet1, Sheet2 and Sheet3 will all have
the result -Sheet3- in each of their A1 cells. And If I go back and
update Sheet2's formula, then all three Sheet cells will say -Sheet2-
Is there another formula that will give the results I'm looking for?
Or is there something I'm entering wrong that is causing this to act
this way?



2)When paste the "value" from another cell into a new cell, in some
instances, it does not recognize it as a value. It give me the error
#NAME? It's not until I select the cell and press enter. Then it
will recognize the contents as what it really is. Example is, I use
the "CONCATENATE" function which combines the results of multiple cells
into on.


Cell: A1 Formula: '=Sum( Result: =Sum(
Cell: A2 Formula: '1+ Result: 1+
Cell: A3 Formula: '2) Result: 2)
Cell: A4 Formula: =CONCATENATE(A1,A2,A3) Result: =Sum(1+2)

So then I copy and paste the result of A4 into a new cell by just
pasting the value and it just displays =Sum(1+2) in the cell. Not the
result of the formula (3). Its not until I select the cell again
and press enter, that it updates the result with the number 3.

I know this sounds like a small deal, but I have over 5,000 cells I've
pasted these types of values in, and short of selecting each cell and
pressing enter, I cant find another way to update these cells. When
I try to select all worksheets and update them all at one time, it will
of course, change all cells to the same formula.

Any Ideas?



3)Is there a way to change the color of a cell, just during the
editing/data entry stage? But change it back during the print stage?
I have sheets that require a lot of info to be entered in adjacent
cells, and it's easy to lose track on where you are. So it would be
nice to shade the cells in which data needs to be entered but when
printing, I want it all to be white.


4)Is there a way to temporarily disable the formula transition. When
dragging the formulas into adjacent cells, it also changes the cells in
which the info is coming from. Basically, I want to have the same
EXACT formula in each cell by just dragging up/down or side to side.





Again, thanks for any help that I can get on any of these four
questions.


--
mrmag2000
------------------------------------------------------------------------
mrmag2000's Profile: http://www.excelforum.com/member.php...o&userid=35878
View this thread: http://www.excelforum.com/showthread...hreadid=556725

Need help on four different topics.
 

See answers in-line


On Wed, 28 Jun 2006 18:47:59 -0500, mrmag2000
wrote:


I know I might not get all four of these answered, but any help on any
one of these will help.

1) When I use the formula to reference the sheet name (tab name), in a
cell --- =MID(CELL("filename"),FIND("]",CELL("filename"))+1,255) ---
It assigns ALL cells with this instance, even on different sheets.
For instance, I have a workbook that contains 20+ sheets. I want it
to display the name of each sheet, in a particular cell of the
corresponding sheets. But when I entered this formula into a second,
third or forth worksheet cell, it changes all results to the last
worksheet, I’ve updated. So Sheet1, Sheet2 and Sheet3 will all have
the result -Sheet3- in each of their A1 cells. And If I go back and
update Sheet2's formula, then all three Sheet cells will say -Sheet2-
Is there another formula that will give the results I'm looking for?
Or is there something I'm entering wrong that is causing this to act
this way?

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,255)

See Bob Phillips' site for more info on why to add the "A1" or any cell
reference.

http://www.xldynamic.com/source/xld.xlFAQ0002.html




2)When paste the "value" from another cell into a new cell, in some
instances, it does not recognize it as a value. It give me the error
#NAME? It's not until I select the cell and press enter. Then it
will recognize the contents as what it really is. Example is, I use
the "CONCATENATE" function which combines the results of multiple cells
into on.


Cell: A1 Formula: '=Sum( Result: =Sum(
Cell: A2 Formula: '1+ Result: 1+
Cell: A3 Formula: '2) Result: 2)
Cell: A4 Formula: =CONCATENATE(A1,A2,A3) Result: =Sum(1+2)

Concatenate returns a text string. =SUM(1+2) is text when pasted as a value.

So then I copy and paste the result of A4 into a new cell by just
pasting the value and it just displays =Sum(1+2) in the cell. Not the
result of the formula (3). Its not until I select the cell again
and press enter, that it updates the result with the number 3.

D-click or Edit serves to re-enter and hitting ENTER lets Excel see it as a
formula



I know this sounds like a small deal, but I have over 5,000 cells I've
pasted these types of values in, and short of selecting each cell and
pressing enter, I cant find another way to update these cells. When
I try to select all worksheets and update them all at one time, it will
of course, change all cells to the same formula.

To do a great whack of these use EditReplace

What: =

With: =

Replace all.



Any Ideas?



3)Is there a way to change the color of a cell, just during the
editing/data entry stage? But change it back during the print stage?
I have sheets that require a lot of info to be entered in adjacent
cells, and it's easy to lose track on where you are. So it would be
nice to shade the cells in which data needs to be entered but when
printing, I want it all to be white.

Check out Conditional Formatting to change color of a cell dpending upon value.

Set your Printer to Black and White before printing.

FilePage SetupSheetBlack and White


4)Is there a way to temporarily disable the formula transition. When
dragging the formulas into adjacent cells, it also changes the cells in
which the info is coming from. Basically, I want to have the same
EXACT formula in each cell by just dragging up/down or side to side.


Use $ signs in your formulas to anchor the cell references.

$A$1 is absolute column and row. $A1 is absolute column and relative row.

A$1 is relative column and absolute row. A1 is relative column and row.




Again, thanks for any help that I can get on any of these four
questions.


Gord Dibben MS Excel MVP

Need help on four different topics.
 

Perfect!!!

Thanks a lot MarkN This helps a lot!!


--
mrmag2000
------------------------------------------------------------------------
mrmag2000's Profile: http://www.excelforum.com/member.php...o&userid=35878
View this thread: http://www.excelforum.com/showthread...hreadid=556725

Need help on four different topics.
 

The concatenate substitution didnt work but the other ones did. Thanks
again.


--
mrmag2000
------------------------------------------------------------------------
mrmag2000's Profile: http://www.excelforum.com/member.php...o&userid=35878
View this thread: http://www.excelforum.com/showthread...hreadid=556725

Need help on four different topics.
 

Wow Gord, you solved them all! :cool: Everything is working fine.


For number 3, I'll have to figure out something, cause there are areas
on the page wich need to be color. Just not the data thats entered.




Thanks both you you!! :)


--
mrmag2000
------------------------------------------------------------------------
mrmag2000's Profile: http://www.excelforum.com/member.php...o&userid=35878
View this thread: http://www.excelforum.com/showthread...hreadid=556725