• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data Validation drop box using INDEX formula

Gaurang Mhatre

Gaurang Mhatre

Member
Hi Friends,

I have this excel, in which there are two columns I & J, i want to put drop down in column J , whenever i select "Client Query/Activity" in column I.

Is there any Index/Match formula for it.

Thanks for your help in advance.

Regards,
Gaurang
 

Attachments

  • Daily MIS.xlsx
    14.2 KB · Views: 9
Gaurang
You mean there is only subcategory for client....for others there is no SubCat
am i right?
 
please see attached sheet, there is only drop down texts appear for Client...
for others its blank drop down
PS: to declare in Name manager, i changed the name for
Client Query/Activity
 

Attachments

  • Copy of Daily MIS.xlsx
    14 KB · Views: 6
Hi Aashu,

its not working, if i select client query/activity from column I then in column J drop down should come. If i select category other than client/query activity, column J should be blank i.e. no drop box. whereas in your given sheet Column K row 25 contain some formula which is not working. Is it possible to put formula in Column J ???

Thanks for the help buddy...
 
Gaurang
Ignore that K cell formula, it was me who was trying to get logic.

I cannot help you with regards to the query, sorry...As far as i know if we put data validation for a cell in dropbox form & it will stay irrespective of any, we can always change what it should show based on other cell value.
Lets see if any one can come up with better solution.
Good luck.
 
.xlsb-format enables Macros.
There are no formula in sheet for this.
There is one Macro which checks active cell's (I-column) position and value.
It deletes current row's J-column data validation and
if value is "Client Query/Activity" then it will make data validation to J-column.
 
@Ashhu
xlsb is binary format and has following advantages over .xlsx/.xlsm

1. Smaller file size. Especially noticeable when file is large.
2. Loading/Saving is much faster since it does not need to parse XML files.
3. Last but not least supports formulas above the 8192 character limit (not an issue in most instances)

Then there are disadvantages...
1. Excel 2003 and older does not support it
2. Many third-party software will not recognize xlsb format. You'll need to convert it to .xls, .xlsx or .csv format.
3. You can't edit Ribbon. You will need to convert it to xlsx or xlsm format first, make changes and then save as xlsb.
4. No way of telling before hand, whether the file contains macro or not.
 
Ah, good to know. My info is somewhat outdated ;) When I was using 2003 & 2007 back 5 or so years back I had issues.
 
I can open .xls? workbooks under 2003 but with some workbooks
there could be issues like

• error in formulas with functions created on version upper than 2003
(for example all the IFS functions like SUMIFS, IFERROR, AGGREGATE, …)

• VBA code blocked 'cause of ActiveX objects on sheet (ex : control button) …

Sometimes (quite rare) a workbook can not be opened.

File format converters link to update Office 2000, XP and 2003 version :
 
You must log in or register to reply here.
Back
Top