0

enter image description here

This is a screen clip of 2 sheets in Excel 2013. What I am trying to do is that if the checkbox corresponding with the row is checked, the text in the row shows up in the second sheet. There will be around 75 rows of text with checkboxes. Some number of these will be checked, and for each one checked, the text should show up on the second sheet (with the displayed rows contiguous; all of the text cells for the row copied but not the checkbox).

I have gotten as far as inserting the checkbox, assigning it to a column to show true/false that I will hide later, and trying to use a VLOOKUP. The problem is it just outputs the first value checked. What I need help with is making this work for multiple checkboxes. When I input it with multiple IF functions, it only shows the output for the first checked box and nothing past it.

I have tried formulas such as:

=VLOOKUP(TRUE,TEKS!$A$2:$K$77,4)     or 
=IF(VLOOKUP(TRUE,TEKS!$A$2:$K$77,4)=A1,VLOOKUP(TRUE,OFFSET(TEKS!$A$2:$K$77, 1, 0),4))

These I know do not work, but they are my best attempts. I figure that if I am able to get the cells from column D of the first sheet, I can get the other columns with an if statement.

  • Please explain more precisely what result you want and show what you have tried (and tell us exactly what result that is giving you). – Scott Mar 8 '15 at 18:42
  • It is hard to envision your problem from the description. Can you post a screenshot somewhere with a link here? – fixer1234 Mar 8 '15 at 19:16
  • dl.dropboxusercontent.com/u/78552577/excel%20question.pdf In the link it shows a screen clip of 2 sheets. What I would like is that if the checkbox corresponding with the row is checked it shows up in the second sheet. I have got as far as inserting the checkbox, assigning it to a column to show true false that I will hide later, and trying to use a VLOOKUP. The problem is it just outputs the first value checked. I want it to paste the row in the new sheet. Sorry if this is not clear; I am very new to excel Thanks – Clayjoe Mar 9 '15 at 17:50
  • @Clayjoe: It looks like the bottom half of the image is supposed to be the results you're currently getting. Your description sounds like you got farther than that. Can you add samples of your formulas to the question? Can I assume that the goal is for the copied rows to be contiguous on the output (if you have say five random rows checked, those will be the first five rows on the output sheet, as opposed to a duplicate of the sheet except the 70 unchecked rows are hidden in place)? BTW, you need to "address" comments like I did on this one or nobody will be aware of your posting. – fixer1234 Mar 10 '15 at 6:07
  • In your example, you have three columns of text. Do you want to carry the same structure to the output, or are you looking to have it all consolidated into one cell for the row? The type, length, and internal structure of the text would make it impractical to try to retain that if you combine it into a single cell. – fixer1234 Mar 10 '15 at 6:13
0

Try to use a hidden row/column where you evaluate the individual "X"s and if "true" place a text you like to see for each. Then on the result cell, you just combine the output:

Row example:

=A1 & B1 & C1 ...

-or-
Column example:

=A1 & A2 & A3 ...

UPDATE (to address comment):

The "IF" function has three parameters, two of which are optional

=IF(criteria [, true statement][, false statement])

If you do not include any of the optional statements it will return the words TRUE or FALSE depending on the outcome.

So, if you like to have a specific text returned, you need to put it in surrounded by quotation marks. If you want to have nothing returned (e.g. when the criteria is not met), you can use an empty string:

=(B1="ABC", "X", "")

This will return "X" whenever "B1" is filled with the text "ABC" (true statement). For any other value in cell "B1", it will return an empty string (false statement).

If you pick it up with the row or column example, it will return exactly what is in there.

Let's say "A1" is true, "B1" is not, and "C1" is again true. Using "ABC" for "A1", "DEF" for "B1" and "GHI" for "C1", it will return "ABCGHI".

  • I think this is what I am trying to do; however, I do not knwo the formula that would show the row if TRUE and leave it if it shows FALSE, and then, go on to the next row and not just adding the same row over and over. I have tried vlookup, offset, and if functions, and I a, doing something wrong. – Clayjoe Mar 9 '15 at 17:58
  • I updated my response to address your comment. – Phoenix Mar 10 '15 at 9:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.