+ Reply to Thread
Results 1 to 4 of 4

How to copy only checked checkboxes into new cell?

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    2

    How to copy only checked checkboxes into new cell?

    Need help in solving formula type or VBA function to copy only checked check boxes into new cell. I have used the True / False statement and it copies information correctly. The problem occurs when 5 check box options available and only 1 and 3 are selected. I need a formula or method to list only the checked items in a new rows without blank rows between selected items:

    CK:B1=APPLES
    __:B2=ORANGES
    CK:B3=PEARS
    __:B4=GRAPES
    __:B5=FIGS

    A100 =IF(A1=TRUE,B1,IF(A2=TRUE,B2,IF(A3=TRUE,B3),IF(A4=TRUE,B4,IF(A5=TRUE,B5,"")
    A101 =IF(A2=TRUE,B2,IF(A3=TRUE,B3),IF(A4=TRUE,B4,IF(A5=TRUE,B5,"")
    A102 =IF(A3=TRUE,B3),IF(A4=TRUE,B4,IF(A5=TRUE,B5,"")
    A103 =IF(A4=TRUE,B4,IF(A5=TRUE,B5,"")
    A104 =IF(A5=TRUE,B5,"")

    RESULT:
    A100 = APPLES
    A101 = PEARS
    A102 = PEARS
    A103 =
    A104 =

    DESIRED RESULT:
    A100 = APPLES
    A101 = PEARS
    A102 =
    A103 =
    A104 =
    Last edited by GTX2013; 12-05-2013 at 06:27 PM.

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

    Re: How to copy only checked checkboxes into new cell?

    Maybe something like this in A100:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Drag down

    Hope this helps
    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
    12-05-2013
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    2

    Re: How to copy only checked checkboxes into new cell?

    Perfect solution. Just for future reference if you move the A1:A5 data outside the range the response received is # REF. I tried numerous methods for correcting but opted to create hidden column. If you can please explain why the A1:A5 is the only place this data works it would be helpful for future projects.

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

    Re: How to copy only checked checkboxes into new cell?

    It only works for A1:a5 range because it is Absolute referenced (the '$' signs) to those cells, to use other areas, you need to change the cell references, for example, if you wanted to include to row to the the references that read $A$1:$A$5 would have to change to $A$1:$A$10 or if the data was in B column starting in row 2 $B$2:$B$6 or $B$2:$B$11, and etc..

    Basically, the the '$' locks the reference to a row or column, so: $A$1 will always reference Row 1,Column 1, $A1 will always reference Column A, but the row will change depending on which row the formula is copied to or extended to, A$1 will always reference Row 1, but the column will change as you coy or move the formula across the work sheet, and finally A1 will change both row and column as the formula is moved across or down the worksheet
    (this is a basic explanation of the way referencing affects moving formulas)

    Look into Absolute vs Relative referencing for more info on how this works

    I hope that explains it ?

+ 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. [SOLVED] Combining SUMPRODUCT with Checked Checkboxes
    By pstewart in forum Excel General
    Replies: 6
    Last Post: 08-11-2012, 04:49 PM
  2. How to run a macro if checkboxes are checked in a range
    By franksonata in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 02-07-2012, 07:31 AM
  3. Checkboxes checked based on cell value
    By humboldtguy in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 05-23-2010, 09:48 AM
  4. [SOLVED] My checkboxes will not stay checked.
    By NUMBnut in forum Excel General
    Replies: 0
    Last Post: 01-09-2006, 05:10 PM
  5. [SOLVED] Assessing whether any checkboxes are checked within frame
    By ExcelMonkey in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2005, 05:05 PM

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