How to display and print a value different from that in cell

[rcavedo]

How does one display and print a value that is different from that in a cell?

For extensive calculation purposes that involve both the column and the row a cell occupies, I have numeric values in all cells. However, in some cells that numeric value actually repersents text. There are only seven different values that represent text. The complication is that for further calucations the values MUST remain numeric.

Is there a way to retain the numeric value in the cell, but display the text it represents?

Thanks

Bob

 

[Jack in the UK]

ok to slect just text tyr ctrl+g
click special
now a box appears tou can pick up what evere selection you want and convert them

do this in the copy thus protecting you original just in case

 

[sen_edp]

May be i got it wrong, but i think you can draw an autoshape with the text you need over the cell with the formula , so when you print it ,it prints the text

 

[rcavedo]

No. Each cell may or may not contain a value to be displayed as text. There are only seven possible text messages. But, each message may occur a large number of times. The text to be displayed will depend on the value of the cell, computed by the formula inthe cell.

 

[NateO]

If there's only seven, why not create a new sheet and a table: numeric values and corresponding text. Do a lookup or index function (use the wizard (fx button)) with the sheet & table and convert the numbers to text via dynamic formulae and print from the new sheet.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>

This message was edited by NateO on 2002-05-06 15:50

 

[sen_edp]

Give me an example of the cell formula
,the condition you want, and the txt to appear too

 

[rcavedo]

I am going to have think about all the possible answere. I also have to get off the computer now. I get back on tomorrow after I have tried to solve the problem using your suggestions. I'll let you know what happens.

 

[Michael Pettinicchi]

I posted this last night but it seems it didn't get posted. Here it is again.

Set-up a hidden column next to the one with the codes. In this column, make the cell equal to the code cell and where applicable convert to text instead of carring the code, i.e., if(code = 1,"text 1",if code =2,"text 2)..etc,code))))))))
Then use a macro to print and in this macro, hide the code column and unhide the hidden column which contains the Code or the Text as applicable.

Regards,
Michael

 

[rcavedo]

Based on all of the recommendations I think I understand a solution.
The following is my understanding of the steps involved:
1. Create a Sheet2 spreadsheet.
2. At cell Sheet2!A1 (using a cell reference to Sheet1!A1) create a nested IF statement checking for the special values and changing the Sheet2!A1 to that text, else Sheet2!A1=Sheet1!A1.
3. Then copy Sheet2!A1 to all cells covering an area equal to Sheet1.
4. Then Copy all cells in Sheet1.
5. Using Paste Special copy only the Formats and Comments to Sheet2.
6. Sheet2 should now look like Sheet1 with only the special values converted to text.
7. Sheet2 should now dynamically change with the changes to Sheet1.

A more elegant solution would be to use a VLOOKUP instead of the nested IF statement. It would be of the form IF (Sheet1!A1 is in VLOOKUP table), convert to text, else use Sheet1!A1. I just don't know how to code the "in table" concept.
Any suggestions?

Thanks,

Bob

 

[artslave]

Have you examined the TEXT and VALUE functions?

I'm not clear enough on your needs to suggest exactly how to implement them, but:

"VALUE converts a text string that represents a number to a number."

"=VALUE(text) where 'text' is the text enclosed in quotation marks or a reference to a cell containing the text you want to convert. Text can be in any of the constant number, date, or time formats recognized by Microsoft Excel. If text is not in one of these formats, VALUE returns the #VALUE! error value."

You might be able to convert your numbers to text in the cells, then add the Value function to the formulas that use those numbers, forcing them to interpret your text as numbers instead -- i.e.,

"=VALUE(A1)/10" instead of just "=A1/10"

Don't know if that would be more trouble than it's worth, but it's at least another avenue to explore.


Catherine

 

[Michael Pettinicchi]

If you only have 7 codes to convert, use the IF method. It will be less complex and faster to execute.

If you want to use the VLOOKUP:

=IF(ISNA(VLOOKUP(Code,Table,TableTextCol,False),Code,VLOOKUP(Code,Table,tableTextCol,False))
e.g. =IF(ISNA(VLOOKUP(C1,ConvTbl,2,False),C1,VLOOKUP(C1,ConvTbl,2,False))

Table with Table Name Range of "ConvTbl"
Col.1 Col.2
7 Text7
18 Text18
etc.
Your table must be in Code sequence.
Regards,
Michael