Need help with an Excel sheet

Someone here has to be some kind of Excel champion. I think what I'm trying to do is fairly simple.

What I'm looking for is 3 columns wide. A=question b=checkbox c=answer.
C is only displaying it's text if B is checked.
I know that I can put a checkbox up, and if checked, it makes B=True. C=answer if B=True.
This works, but I have to hand type each one, and I have 300 pages worth to do. There's gotta be an easier way.

What I'm ultimately trying to accomplish is a question and answer sheet. You input all the questions and answers and use it as a study guide/flash card alternative.

Its been a while since I've used check boxes, but if you know how to quickly do that for all the columns then hiding/displaying the answer is easy.

If you already have all your answers in column C then what you need is conditional formatting. It varies by excel version how you set it up, but a search on "conditional formatting" should get you all you need. Set up the condition so that if column B is false then the text color of column C is white (so its hidden) and a second condition of if B is true then column C text color is black. Alternatively you can just set the text color of column C to white and then just have a single condition to set it black when B is true.

You should be able to "drag and drop" the conditional formatting, making it easy to cover hundreds of lines quickly.

This should get you started, let me know if you have more questions.

I tried putting questions in A, answers in B, checkbox was linked to C. I had conditional formatting that would change B font color to white if C=True. For some reason, this would not change the color. Anything I should look for that would cause this?

I just tried this as a quick example and it worked for me in Office 2007:

A1=question?
B1=answer
C1=TRUE

Click on B1, click conditional formatting. Click new rule. Click Use a formula to determine which cells to format. For the formula enter "=C1" without the quotes. Click on formatting, click on color, choose white. Hit Ok, hit OK.

The answer in column B should have disappeared. Select A1-C1 and drag the corner down to fill in a few more rows. Type FALSE in a row to test that the answer in that row shows up.

If you prefer the formula could have been "=not(C1)" instead to switch false and true.

Is it possible to just use basic code? Example:

If c1.text=true then
b1.hide=true

I haven't used VB/VS in a long time, so my language skills might be rusty, but you get the idea.

You could do it with VB, but it probably won't be as strait forward as you are thinking. Granted I'm also a bit rusty with VB myself. The nice thing about the conditional formating is that when you click and drag it to other cells or copy/paste etc. it will automatically update the formula.

So when you paste the formating on B2 the formula automatically becomes "=C2" instead of "=C1". So all you have to do is get the first row to work and then paste it on to the rest.

Actually now that I think about it the VB might not be bad... make the "on click" action of your check boxes do something like:

B1.visable = this.value

Pretty sure "this" isn't the correct VB keyword, it might be "me", but you get the idea. I don't know a way of easily duplicating this to hundreds of check boxes though, like you can do with conditional formating.

It seems that doing it this way, I need to edit each checkbox to be linked to the corresponding C cell, so that the B cell next to it will use the conditonal formatting.

Yeah thats a tough one. I don't even know how to make a check box in Office 2007, I haven't used them in several years.

For a less elegant work around you could do something like change the conditional format formula to "=isblank(c1)". Then while studying you could just go down list by mash keyboard, press enter, mash keyboard, press enter, repeat. To clear all the answers just select column C and hit delete.

Another less than ideal solution is to have a single checkbox show/hide all answers with a formula like "=c$1", but if you sized your rows correctly (or left blank ones) you could just have one question on the screen at a time so you wouldn't see the other answers. You could then have a macro button "jump down 10 rows" or similar to get to each new question.

I guess I'll have to manually edit each checkbox. Not a huge deal, I was just trying to avoid it.