excel - How can I use structured references to identify column and use row values in the identified column as criteria? -

-

i have excel 2013 table, called 'student_courses' columns: stu_id, stu_major, stu_course, , calculated column named validated_crs.

i trying create formula calculated column named validated_crs evaluates every record.

in calculated column, need reference value in stu_major column find column in second table, 'qualified_courses'. headers in 'qualified_courses' table correlate stu_major values.

i need check see if of values in correlated column in 'qualified_courses' match 'stu_course' value in record being evaluated in 'student_courses' table.

if of values in correlated column match stu_course value given record in first table, should return value of "validated" in calculated column.

so far, have found out how return second table column values using =indirect("qualified_courses"&"["&[@[stu_major]]&"]"), i'm not sure how use returned set of values check against stu_course value see if there match.

many thanks,

lindsay

student_courses table:

stu_id     stu_major     stu_course     validated_crs 5432       multe         biol1102       validated 5432       multe         musc1303       null 5432       multe         engl2303       validated 6737       multm         hist1104       validated 6737       multm         biol1222       null 6737       multm         educ2303       validated 6737       multm         educ1302       validated

qualified_courses table:

multa      multb         multe          multm art1301    hist1301      biol1102       hist1104 biol1322   pols2210      engl2303       educ2303 engl1440   is3340        art3303        educ1302            biol2302      biol1222       musc1303

this formula builds on yours:

=if(isnumber(match([@[stu_course]],indirect("qualified_courses"&"["&[@[stu_major]]&"]"),0)),"validated","")

we use match function search returned column course. if there 1 returns number, otherwise error.

the isnumber returns true/false if uses determine outcome.

but not indirect() formula volatile formula. prefer index formula:

=if(isnumber(match([@[stu_course]],index(qualified_courses,0,match([@[stu_major]],qualified_courses[#headers],0)),0)),"validated","")

which return same.

instead of indirect find correct column use index/match. once again match returns number, time of column in second table.

the index() uses nubmer return full column @ position. 0 @ second criterion states want full column.

then first.

volatile formulas recalculate every time excel recalculates whether data tied changed or not.

non volatile formulas recalculate if data refer changes.

the validated_cs column in picture top formula , next column bottom:

enter image description here


Comments

Post a Comment

Popular posts from this blog

wordpress - (T_ENDFOREACH) php error -

-
creating wordpress website , i'm getting following php error: parse error: syntax error, unexpected 'endforeach' (t_endforeach) in /home/soralmedia/public_html/wp-content/themes/soral media/woocommerce/myaccount/my-orders.php on line 98 here's code file: <?php /** * orders * * shows recent orders on account page. * * template can overridden copying yourtheme/woocommerce/myaccount/my-orders.php. * * however, on occasion woocommerce need update template files , (the theme developer). * need copy new files theme maintain compatibility. try this. * little possible, happen. when occurs version of template file will. * bumped , readme list important changes. * * @see http://docs.woothemes.com/document/template-structure/ * @author woothemes * @package woocommerce/templates * @version 2.5.0 */ if ( ! defined( 'abspath' ) ) { exit; } $my_orders_columns = apply_filters( 'woocommerce_my_account_my_orders_columns', array( ...
Read more

Export Excel workseet into txt file using vba - (text and numbers with formulas) -

-
i new vba have developed code use in excel move text/numbers txt file. issue have 3 functions need go same txt file. when run first function has set fs = createobject("scripting.filesystemobject") set = fs.createtextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the other 2 functions had save. set fs = createobject("scripting.filesystemobject") set = fs.savetextfile(module1.nys45uploadfilename, true) a.writeline (str) a.close. the first function go txt file, other 2 error object doesn't support property or method. when changed 3 create last function goes txt file. can not figure out word use in order make other 2 functions follow txt file in order keyed. the entire code follows private sub addrecord_click() on error goto errhandler module1.nys45uploadfilename = application.getsaveasfilename(filefilter:="textfiles (*.txt), *.txt") if module1.nys45uploadfilename = "false" exit sub ...
Read more

Using django-mptt to get only the categories that have items -

-
sample structure: all uppercase: category mixed case: item root ├── books │   ├── fiction │   │   └── classics │   └── non-fiction ├── clothing └── electronics ├── laptops ├── phones │   ├── apple │   │   ├── iphone 6 │   │   ├── iphone 6 plus │   │   ├── iphone 6s │   │   └── iphone 6s plus │   ├── motorola │   │   ├── moto g4 │   │   ├── moto g4 play │   │   ├── moto g4 plus │   │   └── moto x │   └── samsung └── tablets └── apple i'm trying have index page show "electronics" category. "clothing" category shouldn't show since it's empty , "books" category shouldn't show either since, although has child categories, child categories don't have items. similarly, "electronics" page should show "phones" category. "laptops" category shouldn't show since it's empty , "tablets" category shouldn't show either since...
Read more