Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

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

  1. #11
    Board Regular
    Join Date
    Feb 2002
    Posts
    3,184
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    Free Excel based Web Toolbar available here.

    Jack in the UK
    J & R Excel Solutions
    "making Excel work for you"

  2. #12
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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


    - Posting guidelines, forum rules and terms of use

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes

    -----------------------------------------------------------------------------------------------------------------------------------------
    ANDREAS ( WINDOWS 7 PRO, MICROSOFT EXCEL 2010)

  3. #13
    New Member
    Join Date
    Apr 2002
    Location
    Robert F. Cavedo
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  4. #14
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, NateO

    [ This Message was edited by: NateO on 2002-05-06 15:50 ]

  5. #15
    Board Regular sen_edp's Avatar
    Join Date
    Mar 2002
    Location
    Hellas
    Posts
    555
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  6. #16
    New Member
    Join Date
    Apr 2002
    Location
    Robert F. Cavedo
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  7. #17
    Board Regular
    Join Date
    Apr 2002
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #18
    New Member
    Join Date
    Apr 2002
    Location
    Robert F. Cavedo
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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





  9. #19
    New Member
    Join Date
    Apr 2002
    Location
    California
    Posts
    26
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  10. #20
    Board Regular
    Join Date
    Apr 2002
    Posts
    53
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •