+ Reply to Thread
Results 1 to 5 of 5

Excel how to display text in the cell but that cell have a different value ? HELP

  1. #1
    Registered User
    Join Date
    07-30-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Question Excel how to display text in the cell but that cell have a different value ? HELP

    excel help.jpg

    Hello guys,

    Basically it is a training matrix.
    I know this is possible and probably easy to implement but i haven't got a clue lol


    basically I need A30, A32, A34 and A36 cells to display the characters they are currently displaying but when i copy and past any of them into a new cell on the matrix for example into cell B11 I want to be able to SUM the total of that column and display it in cell B24

    So to recap, I would like A30, A32, A34 and A36 cells to hold 25, 50, 75 and 100 as there value but this can not be displayed it is just a value which i can use to calculate.

    I am ok if i have to link the cells with another worksheet but i have tried doing this also i dont know if i need to use VBA.

    Please advise me to the best solution. Thanks
    Last edited by larner; 07-31-2013 at 05:06 AM.

  2. #2
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Excel how to display text in the cell but that cell have a different value ? HELP

    in cell B24 type:

    =(COUNTIF($B$11:B23,$D$30)*$E$30)+(COUNTIF($B$11:$B$23,$D$32)*$E$32)+(COUNTIF($B$11:$B$23,$D$34)*$E$34)+(COUNTIF($B$11:$B$23,$D$36)*$E$36)

    Where D30,32,34,36 hold the letter and
    E30,32,34,36 hold the value for that letter

    Not particularly pretty but will do the job.

    You could "hard" code the letters and values in i.e. replace $D$30 with "I" and $E$30 with 25 but in the way above if you ever wanted to "reassign" letters or numbers then you just type them in rather than having to edit the formula...

  3. #3
    Registered User
    Join Date
    07-30-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel how to display text in the cell but that cell have a different value ? HELP

    JungleJme,

    That seems to be the right approach to the problem, but when I copy and paste the different training symbols, the total is only displaying 25 as the total... even if i dont have anything in B11:B23 it still displays 25 as a result.

    I have checked your formula and there is not mistakes you did it perfect why it is not working I have no idea...

    What you thinking JungleJme?


    PICTURE: Please ignore the SUM lol I was trying to do something silly.

    excel help.png

    Thanks alot
    Last edited by larner; 07-31-2013 at 09:51 AM.

  4. #4
    Registered User
    Join Date
    07-30-2013
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    4

    Re: Excel how to display text in the cell but that cell have a different value ? HELP

    JungleJme, WORKING SOLUTION

    Please ignore my reply your formula is working perfect as i first mentioned, my problem is that i still had the previous attempt settings in place, like in the format cell menu which was they way I tried to do.

    An again thanks a lot for your help and hope this helps other people.

  5. #5
    Forum Contributor
    Join Date
    04-13-2010
    Location
    England
    MS-Off Ver
    Office 2016 365
    Posts
    211

    Re: Excel how to display text in the cell but that cell have a different value ? HELP

    No probs, happy to help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA - Import text from cell value and display in text box on cell mouse click
    By hemal89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-29-2013, 07:20 AM
  2. excel macro hyperlink-need text to display=d2(cell calue)
    By CLASSMATE in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-19-2012, 12:48 PM
  3. Replies: 1
    Last Post: 06-15-2009, 11:28 PM
  4. How can I display a cell range in a text box in excel?
    By Pat Hughes in forum Excel General
    Replies: 3
    Last Post: 09-22-2005, 01:05 PM
  5. Why Excel doens't display all text in a cell
    By Dan in forum Excel - New Users/Basics
    Replies: 14
    Last Post: 04-22-2005, 10:06 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1