+ Reply to Thread
Results 1 to 12 of 12

Inserting a cell depending on what value I type in current cell

  1. #1
    Registered User
    Join Date
    09-20-2012
    Location
    Western Australia, Australia
    MS-Off Ver
    Excel 2003
    Posts
    44

    Inserting a cell depending on what value I type in current cell

    Hi Again

    I have a spreadsheet and on that spreadsheet I would like to make it easier to insert ticks and crosses. What I would like to happen is that if i type the number '1' in Column 'C' (e.g. 'C3') then I would like that cell to populate with a tick (aka cell 'O44') or if I enter a '0' i would like the current cell to populate with a cross (aka cell 'O43').

    I would also like the same thing to happen if i type a '1' or '0' in Columns D,J,K.

    Thanks for any help


    Rhiannon

    Reward Scratchies Tracking Spreadsheet Example.xls

  2. #2
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a cell depending on what value I type in current cell

    Not exactly sure where you want what, a couple of examples would help here
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  3. #3
    Registered User
    Join Date
    09-20-2012
    Location
    Western Australia, Australia
    MS-Off Ver
    Excel 2003
    Posts
    44

    Re: Inserting a cell depending on what value I type in current cell

    Reward Scratchies Tracking Spreadsheet Example.xls

    Hi Dredwolf

    Basically what I want to do is in columns C,D,J and K, it is going to just get tick or cross in the appropriate box.

    For Example:

    On line one if the person is an employee I would like a tick to come under the 'employee' column and if they are a non employee I would like a tick to come under the 'Other' column. I have updated the spreadsheet to show some examples. I want to just be able to enter a number and have it populate with either a 'tick' or 'cross' so that I dont have to copy and paste it every time.

    Does that help??
    Last edited by Rhiannon25; 04-04-2013 at 01:34 AM.

  4. #4
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a cell depending on what value I type in current cell

    I think you may need VBA to accomplish this, unfortunately, that's definitely not my strong suit..probably a cell/sheet change event sub though (to make it automatic)...after that, it would probably take me a few days to come up with a solution others here could come up with in a few minutes

  5. #5
    Registered User
    Join Date
    09-20-2012
    Location
    Western Australia, Australia
    MS-Off Ver
    Excel 2003
    Posts
    44

    Re: Inserting a cell depending on what value I type in current cell

    Yeah im not very good with VBA either (i havent used it in years). I was hoping even if it is not done the exact way I have described that there might be some way to use a formula. Hopefully someone can help me

    Thank-you anyway

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a cell depending on what value I type in current cell

    Well, to change the value you type into a cell to a different value, I'm pretty sure you are going to need VBA, sorry

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a cell depending on what value I type in current cell

    a formula solution that would work is (for column c for example) insert new column( or use a different column), sy a new D column, format the column as wingdings, enter this formula in D3:
    =IF(C3="","",IF(OR(C3=1,C3=0),CHAR(C3+251),""))
    the first if stops blanks being returned as 0's, the next if changes the 1's and 0's to 252,251 for the wingdings font to display proper symbol, but it also adds 4 extra columns...maybe something like that if you want to avoid VBA

    Edit-
    Moved ")" to proper spot
    Last edited by dredwolf; 04-04-2013 at 03:08 AM.

  8. #8
    Forum Guru
    Join Date
    03-12-2010
    Location
    Canada
    MS-Off Ver
    2010 and 2013
    Posts
    4,418

    Re: Inserting a cell depending on what value I type in current cell

    Have a look at the attachment. I used a couple of simple formulas and two helper columns for you to make your 0 and 1 choices for employee and safety. Let me know if this suffices your needs.

    abousetta
    Attached Files Attached Files
    Please consider:

    Thanking those who helped you. Click the star icon in the lower left part of the contributor's post and add Reputation.
    Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.

  9. #9
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a cell depending on what value I type in current cell

    I think you could even get rid of the VLOOKUP, just an

    =IF(M3,CHAR(252),CHAR(251))

    may work in that case, but in any case is WAY better than the 4 columns I suggested

    Thanks abousetta for helping out on what was starting to send me batty (I know I didn't have far to got, but still...I'd like to remain semi-sane...lol )

  10. #10
    Registered User
    Join Date
    09-20-2012
    Location
    Western Australia, Australia
    MS-Off Ver
    Excel 2003
    Posts
    44

    Re: Inserting a cell depending on what value I type in current cell

    Reward%20example(1).xlsThanks Abousetta and dredwolf.

    Abousetta's solution gave me an idea for the spreadsheet and it works really well now, I inserted a column before "employee' and 'other' and created an if statement that works well. But if nothing is written in the new column it comes up with a cross in the first column and a tick in the second column, how do you make it just stay blank unless you enter something??

  11. #11
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Inserting a cell depending on what value I type in current cell

    for either solution :
    =IF(A3="","",your formula)
    dont forget too remove the '=' from your formula



    Edit-
    or :
    =IF(C3="","",IF(C3=1,$Q$44,$Q$43))
    for C column
    Last edited by dredwolf; 04-04-2013 at 03:38 AM.

  12. #12
    Registered User
    Join Date
    09-20-2012
    Location
    Western Australia, Australia
    MS-Off Ver
    Excel 2003
    Posts
    44

    Re: Inserting a cell depending on what value I type in current cell

    That works perfectly, thank-you

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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