ANmarS - Search results

Excel/VBA expert since Sep 1997 (21 years 2 months)

We are moving ...

We are moving to another location, better, faster and more resources .... check it out ANmar.Systems

3 Q & A posts tagged with Validation

Mon
Apr 26
2010

How to add check boxes to Excel 2007 to a column if a the row has data?

I want to have check boxes in column D, but I dont want the box boxes to appear unless data is intered into column A, that way I can keep the spread sheet clean because I will be adding data to this spread sheet daily. I have no idea how to even start this process. Any help would be great! Thanks!


Answer

The only ay to add checkbox automatically is using VBA or macros

However, there is another way, but using Validation instead
You can use Validation based on Validation, or Validation based on your value in column A
Make the validation list of Yes, No and you will get something similar to check box

Check out Multiple validations here
http://www.file1.net/lesson.asp?id=6y0x6…

VBAXLMan

Monday 4/26/2010 7:24:31 PM
Mon
Apr 12
2010

How do I remove blank (but not necessarily empty) cells from a drop down menu in excel?

I want to have a row of drop down lists. In each drop down I want all the options to exist EXCEPT any that have already been selected. This is my primary problem. The closest I can get is to remove value from the drop down range by an IF function in the source data, so that the list updates based on what has already been selected. This requires extensive functions and also leaves blanks in my drop down menus. I'm keen to have the drop downs only contain the remaining options and no blank gaps. Are there any solutions to either the primary problem, or, failing that, a solution to remove the blank (but not empty) cells from my drop down menus?

Cheers :)


Answer

You have very interesting request

Check out this file
http://www.File1.net/Links/NewList.xls

It has new list populated every time user selects an item, the new list will have the same as old list without the newly selected one, it was done for tutoring purposes

It should do what you are looking for, you might need some tweaks though
That was done using the following Excel features:
Data Validation
OFFSET function
IF function
Name

Let me know if it helped or not

VBAXLMan

Monday 4/12/2010 2:35:47 AM
Thu
Apr 9
2009

Can I count number of rows based on 2 critera that are inputted by the user?

I have many sheets of raw data that I am trying to Roll up in a simple Dashboard using excel. So far I have managed fine but have come stuck on this part.
I am trying to count a number of rows based on 2 criteria. The problem is these criteria are inputted via validation list from the user!

The user selects a name of a program and what week number they would want to view a button is selected to run a macro that calculates this information.

The macro is no problem just the input of the search criteria via validation list.
I have tried everything I know but cant figure it out.
Any help is much appreciated


Answer

I would rather you do DSUM
Here is a video lesson on how to do it
http://file1.net/lesson.asp?id=6d2CvCTrt…

You can make the criteria range as the same as the validation cell (Excel will not mind)

Let me know if everything goes well or not

VBAXLMan

Thursday 4/9/2009 12:12:14 PM

Tags

History