Re: Bls: [belajar-excel] Ambil data berdasarkan data validasi

Toby kenzo [email protected] [belajar-excel] Thu, 28 Jan 2016 22:42:12 -0800

Hai Jack,

Coba di buat formula di sheet3 pada cell C2 :
=IFERROR(INDEX(Sheet2!$B$2:$B$9,MATCH(Sheet3!B2,Sheet2!$A$2:$A$9,0)),"")
jangan lupa di tekan bersamaan shiftCtrlEnter karena array formula.


TerimaKasih

Pada Jum, 29/1/16, Jack Carmelo [email protected] [belajar-excel] 
<[email protected]> menulis:

 Judul: Re: Bls: [belajar-excel] Ambil data berdasarkan data validasi
 Kepada: [email protected]
 Tanggal: Jumat, 29 Januari, 2016, 12:48 PM
 
 
  
 
 
 
   
 
 
     
       
       
       
 Makasih bang toby atas bantuannya. Saya
 sudah coba kombinasikan tapi belum ketemu. Mohon
 bantuannya.
 --
 
 Salam
 
 Jack Carmelo
 
 Jumat, 29 Januari 2016, 01:49PM +0900 dari
 "Toby kenzo [email protected] [belajar-excel]"
 <[email protected]>:
 Hai Jack,
 
 Coba pake formula array  IFERROR,INDEX,MATCH.
 
 Di forum ini sudah sering di jelaskan penggunaan array
 fermula untuk kombinasi INDEX, MATCH.
 
 TerimaKasih.
 
 Pada Jum, 29/1/16, Jack Carmelo  [email protected]
 [belajar-excel] < [email protected] >
 menulis:
 
 Judul: [belajar-excel] Ambil data berdasarkan data validasi
 [1 Attachment]
 
 Kepada:  [email protected]
 
 Tanggal: Jumat, 29 Januari, 2016, 9:00 AM
 
 Selamat pagi para master.
 
 Saya punya data
 
 yang harus di ambil. Tapi
 
 karena datanya diambil berdasarkan data validasi list jdi
 
 saya bingung
 
 cara membuat formulanya. Mohon bantuannya.
 
 Contoh kasus terlampir. Yang ditanyakan di sheet3
 
 datanya harus sesuai dengan  sheet2 cell D2. Sesuai
 dengan
 
 validasi data di cell A2.
 
 Terima kasih atas bantuannya..
 
 Salam
 
 Jack Carmelo
 
 #yiv1688961367 #yiv1688961367 --
 
 #yiv1688961367ygrp-mkp {
 
 border:1px solid #d8d8d8;font-family:Arial;margin:10px
 
 0;padding:0 10px;}
 
 #yiv1688961367 #yiv1688961367ygrp-mkp hr {
 
 border:1px solid #d8d8d8;}
 
 #yiv1688961367 #yiv1688961367ygrp-mkp #yiv1688961367hd {
 
 color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
 
 0;}
 
 #yiv1688961367 #yiv1688961367ygrp-mkp #yiv1688961367ads {
 
 margin-bottom:10px;}
 
 #yiv1688961367 #yiv1688961367ygrp-mkp .yiv1688961367ad {
 
 padding:0 0;}
 
 #yiv1688961367 #yiv1688961367ygrp-mkp .yiv1688961367ad p
 {
 
 margin:0;}
 
 #yiv1688961367 #yiv1688961367ygrp-mkp .yiv1688961367ad a
 {
 
 color:#0000ff;text-decoration:none;}
 
 #yiv1688961367 #yiv1688961367ygrp-sponsor
 
 #yiv1688961367ygrp-lc {
 
 font-family:Arial;}
 
 #yiv1688961367 #yiv1688961367ygrp-sponsor
 
 #yiv1688961367ygrp-lc #yiv1688961367hd {
 
 margin:10px
 
 0px;font-weight:700;font-size:78%;line-height:122%;}
 
 #yiv1688961367 #yiv1688961367ygrp-sponsor
 
 #yiv1688961367ygrp-lc .yiv1688961367ad {
 
 margin-bottom:10px;padding:0 0;}
 
 #yiv1688961367 #yiv1688961367actions {
 
 font-family:Verdana;font-size:11px;padding:10px 0;}
 
 #yiv1688961367 #yiv1688961367activity {
 
 
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}
 
 #yiv1688961367 #yiv1688961367activity span {
 
 font-weight:700;}
 
 #yiv1688961367 #yiv1688961367activity span:first-child {
 
 text-transform:uppercase;}
 
 #yiv1688961367 #yiv1688961367activity span a {
 
 color:#5085b6;text-decoration:none;}
 
 #yiv1688961367 #yiv1688961367activity span span {
 
 color:#ff7900;}
 
 #yiv1688961367 #yiv1688961367activity span
 
 .yiv1688961367underline {
 
 text-decoration:underline;}
 
 #yiv1688961367 .yiv1688961367attach {
 
 clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
 
 0;width:400px;}
 
 #yiv1688961367 .yiv1688961367attach div a {
 
 text-decoration:none;}
 
 #yiv1688961367 .yiv1688961367attach img {
 
 border:none;padding-right:5px;}
 
 #yiv1688961367 .yiv1688961367attach label {
 
 display:block;margin-bottom:5px;}
 
 #yiv1688961367 .yiv1688961367attach label a {
 
 text-decoration:none;}
 
 #yiv1688961367 blockquote {
 
 margin:0 0 0 4px;}
 
 #yiv1688961367 .yiv1688961367bold {
 
 font-family:Arial;font-size:13px;font-weight:700;}
 
 #yiv1688961367 .yiv1688961367bold a {
 
 text-decoration:none;}
 
 #yiv1688961367 dd.yiv1688961367last p a {
 
 font-family:Verdana;font-weight:700;}
 
 #yiv1688961367 dd.yiv1688961367last p span {
 
 margin-right:10px;font-family:Verdana;font-weight:700;}
 
 #yiv1688961367 dd.yiv1688961367last p
 
 span.yiv1688961367yshortcuts {
 
 margin-right:0;}
 
 #yiv1688961367 div.yiv1688961367attach-table div div a {
 
 text-decoration:none;}
 
 #yiv1688961367 div.yiv1688961367attach-table {
 
 width:400px;}
 
 #yiv1688961367 div.yiv1688961367file-title a,
 #yiv1688961367
 
 div.yiv1688961367file-title a:active, #yiv1688961367
 
 div.yiv1688961367file-title a:hover, #yiv1688961367
 
 div.yiv1688961367file-title a:visited {
 
 text-decoration:none;}
 
 #yiv1688961367 div.yiv1688961367photo-title a,
 
 #yiv1688961367 div.yiv1688961367photo-title a:active,
 
 #yiv1688961367 div.yiv1688961367photo-title a:hover,
 
 #yiv1688961367 div.yiv1688961367photo-title a:visited {
 
 text-decoration:none;}
 
 #yiv1688961367 div#yiv1688961367ygrp-mlmsg
 
 #yiv1688961367ygrp-msg p a span.yiv1688961367yshortcuts {
 
 font-family:Verdana;font-size:10px;font-weight:normal;}
 
 #yiv1688961367 .yiv1688961367green {
 
 color:#628c2a;}
 
 #yiv1688961367 .yiv1688961367MsoNormal {
 
 margin:0 0 0 0;}
 
 #yiv1688961367 o {
 
 font-size:0;}
 
 #yiv1688961367 #yiv1688961367photos div {
 
 float:left;width:72px;}
 
 #yiv1688961367 #yiv1688961367photos div div {
 
 border:1px solid
 
 #666666;height:62px;overflow:hidden;width:62px;}
 
 #yiv1688961367 #yiv1688961367photos div label {
 
 
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}
 
 #yiv1688961367 #yiv1688961367reco-category {
 
 font-size:77%;}
 
 #yiv1688961367 #yiv1688961367reco-desc {
 
 font-size:77%;}
 
 #yiv1688961367 .yiv1688961367replbq {
 
 margin:4px;}
 
 #yiv1688961367 #yiv1688961367ygrp-actbar div a:first-child
 {
 
 margin-right:2px;padding-right:5px;}
 
 #yiv1688961367 #yiv1688961367ygrp-mlmsg {
 
 font-size:13px;font-family:Arial, helvetica, clean,
 
 sans-serif;}
 
 #yiv1688961367 #yiv1688961367ygrp-mlmsg table {
 
 font-size:inherit;font:100%;}
 
 #yiv1688961367 #yiv1688961367ygrp-mlmsg select,
 
 #yiv1688961367 input, #yiv1688961367 textarea {
 
 font:99% Arial, Helvetica, clean, sans-serif;}
 
 #yiv1688961367 #yiv1688961367ygrp-mlmsg pre,
 #yiv1688961367
 
 code {
 
 font:115% monospace;}
 
 #yiv1688961367 #yiv1688961367ygrp-mlmsg * {
 
 line-height:1.22em;}
 
 #yiv1688961367 #yiv1688961367ygrp-mlmsg #yiv1688961367logo
 {
 
 padding-bottom:10px;}
 
 #yiv1688961367 #yiv1688961367ygrp-msg p a {
 
 font-family:Verdana;}
 
 #yiv1688961367 #yiv1688961367ygrp-msg
 
 p#yiv1688961367attach-count span {
 
 color:#1E66AE;font-weight:700;}
 
 #yiv1688961367 #yiv1688961367ygrp-reco
 
 #yiv1688961367reco-head {
 
 color:#ff7900;font-weight:700;}
 
 #yiv1688961367 #yiv1688961367ygrp-reco {
 
 margin-bottom:20px;padding:0px;}
 
 #yiv1688961367 #yiv1688961367ygrp-sponsor
 #yiv1688961367ov
 
 li a {
 
 font-size:130%;text-decoration:none;}
 
 #yiv1688961367 #yiv1688961367ygrp-sponsor
 #yiv1688961367ov
 
 li {
 
 font-size:77%;list-style-type:square;padding:6px 0;}
 
 #yiv1688961367 #yiv1688961367ygrp-sponsor
 #yiv1688961367ov
 
 ul {
 
 margin:0;padding:0 0 0 8px;}
 
 #yiv1688961367 #yiv1688961367ygrp-text {
 
 font-family:Georgia;}
 
 #yiv1688961367 #yiv1688961367ygrp-text p {
 
 margin:0 0 1em 0;}
 
 #yiv1688961367 #yiv1688961367ygrp-text tt {
 
 font-size:120%;}
 
 #yiv1688961367 #yiv1688961367ygrp-vital ul li:last-child
 {
 
 border-right:none !important;
 
 }
 
 #yiv1688961367
 
 
 
 
 
     
      
 
     
     
 
 
 
 #yiv0509230174 #yiv0509230174 --
   #yiv0509230174ygrp-mkp {
 border:1px solid #d8d8d8;font-family:Arial;margin:10px
 0;padding:0 10px;}
 
 #yiv0509230174 #yiv0509230174ygrp-mkp hr {
 border:1px solid #d8d8d8;}
 
 #yiv0509230174 #yiv0509230174ygrp-mkp #yiv0509230174hd {
 color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
 0;}
 
 #yiv0509230174 #yiv0509230174ygrp-mkp #yiv0509230174ads {
 margin-bottom:10px;}
 
 #yiv0509230174 #yiv0509230174ygrp-mkp .yiv0509230174ad {
 padding:0 0;}
 
 #yiv0509230174 #yiv0509230174ygrp-mkp .yiv0509230174ad p {
 margin:0;}
 
 #yiv0509230174 #yiv0509230174ygrp-mkp .yiv0509230174ad a {
 color:#0000ff;text-decoration:none;}
 #yiv0509230174 #yiv0509230174ygrp-sponsor
 #yiv0509230174ygrp-lc {
 font-family:Arial;}
 
 #yiv0509230174 #yiv0509230174ygrp-sponsor
 #yiv0509230174ygrp-lc #yiv0509230174hd {
 margin:10px
 0px;font-weight:700;font-size:78%;line-height:122%;}
 
 #yiv0509230174 #yiv0509230174ygrp-sponsor
 #yiv0509230174ygrp-lc .yiv0509230174ad {
 margin-bottom:10px;padding:0 0;}
 
 #yiv0509230174 #yiv0509230174actions {
 font-family:Verdana;font-size:11px;padding:10px 0;}
 
 #yiv0509230174 #yiv0509230174activity {
 
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}
 
 #yiv0509230174 #yiv0509230174activity span {
 font-weight:700;}
 
 #yiv0509230174 #yiv0509230174activity span:first-child {
 text-transform:uppercase;}
 
 #yiv0509230174 #yiv0509230174activity span a {
 color:#5085b6;text-decoration:none;}
 
 #yiv0509230174 #yiv0509230174activity span span {
 color:#ff7900;}
 
 #yiv0509230174 #yiv0509230174activity span
 .yiv0509230174underline {
 text-decoration:underline;}
 
 #yiv0509230174 .yiv0509230174attach {
 clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
 0;width:400px;}
 
 #yiv0509230174 .yiv0509230174attach div a {
 text-decoration:none;}
 
 #yiv0509230174 .yiv0509230174attach img {
 border:none;padding-right:5px;}
 
 #yiv0509230174 .yiv0509230174attach label {
 display:block;margin-bottom:5px;}
 
 #yiv0509230174 .yiv0509230174attach label a {
 text-decoration:none;}
 
 #yiv0509230174 blockquote {
 margin:0 0 0 4px;}
 
 #yiv0509230174 .yiv0509230174bold {
 font-family:Arial;font-size:13px;font-weight:700;}
 
 #yiv0509230174 .yiv0509230174bold a {
 text-decoration:none;}
 
 #yiv0509230174 dd.yiv0509230174last p a {
 font-family:Verdana;font-weight:700;}
 
 #yiv0509230174 dd.yiv0509230174last p span {
 margin-right:10px;font-family:Verdana;font-weight:700;}
 
 #yiv0509230174 dd.yiv0509230174last p
 span.yiv0509230174yshortcuts {
 margin-right:0;}
 
 #yiv0509230174 div.yiv0509230174attach-table div div a {
 text-decoration:none;}
 
 #yiv0509230174 div.yiv0509230174attach-table {
 width:400px;}
 
 #yiv0509230174 div.yiv0509230174file-title a, #yiv0509230174
 div.yiv0509230174file-title a:active, #yiv0509230174
 div.yiv0509230174file-title a:hover, #yiv0509230174
 div.yiv0509230174file-title a:visited {
 text-decoration:none;}
 
 #yiv0509230174 div.yiv0509230174photo-title a,
 #yiv0509230174 div.yiv0509230174photo-title a:active,
 #yiv0509230174 div.yiv0509230174photo-title a:hover,
 #yiv0509230174 div.yiv0509230174photo-title a:visited {
 text-decoration:none;}
 
 #yiv0509230174 div#yiv0509230174ygrp-mlmsg
 #yiv0509230174ygrp-msg p a span.yiv0509230174yshortcuts {
 font-family:Verdana;font-size:10px;font-weight:normal;}
 
 #yiv0509230174 .yiv0509230174green {
 color:#628c2a;}
 
 #yiv0509230174 .yiv0509230174MsoNormal {
 margin:0 0 0 0;}
 
 #yiv0509230174 o {
 font-size:0;}
 
 #yiv0509230174 #yiv0509230174photos div {
 float:left;width:72px;}
 
 #yiv0509230174 #yiv0509230174photos div div {
 border:1px solid
 #666666;height:62px;overflow:hidden;width:62px;}
 
 #yiv0509230174 #yiv0509230174photos div label {
 
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}
 
 #yiv0509230174 #yiv0509230174reco-category {
 font-size:77%;}
 
 #yiv0509230174 #yiv0509230174reco-desc {
 font-size:77%;}
 
 #yiv0509230174 .yiv0509230174replbq {
 margin:4px;}
 
 #yiv0509230174 #yiv0509230174ygrp-actbar div a:first-child {
 margin-right:2px;padding-right:5px;}
 
 #yiv0509230174 #yiv0509230174ygrp-mlmsg {
 font-size:13px;font-family:Arial, helvetica, clean,
 sans-serif;}
 
 #yiv0509230174 #yiv0509230174ygrp-mlmsg table {
 font-size:inherit;font:100%;}
 
 #yiv0509230174 #yiv0509230174ygrp-mlmsg select,
 #yiv0509230174 input, #yiv0509230174 textarea {
 font:99% Arial, Helvetica, clean, sans-serif;}
 
 #yiv0509230174 #yiv0509230174ygrp-mlmsg pre, #yiv0509230174
 code {
 font:115% monospace;}
 
 #yiv0509230174 #yiv0509230174ygrp-mlmsg * {
 line-height:1.22em;}
 
 #yiv0509230174 #yiv0509230174ygrp-mlmsg #yiv0509230174logo {
 padding-bottom:10px;}
 
 
 #yiv0509230174 #yiv0509230174ygrp-msg p a {
 font-family:Verdana;}
 
 #yiv0509230174 #yiv0509230174ygrp-msg
 p#yiv0509230174attach-count span {
 color:#1E66AE;font-weight:700;}
 
 #yiv0509230174 #yiv0509230174ygrp-reco
 #yiv0509230174reco-head {
 color:#ff7900;font-weight:700;}
 
 #yiv0509230174 #yiv0509230174ygrp-reco {
 margin-bottom:20px;padding:0px;}
 
 #yiv0509230174 #yiv0509230174ygrp-sponsor #yiv0509230174ov
 li a {
 font-size:130%;text-decoration:none;}
 
 #yiv0509230174 #yiv0509230174ygrp-sponsor #yiv0509230174ov
 li {
 font-size:77%;list-style-type:square;padding:6px 0;}
 
 #yiv0509230174 #yiv0509230174ygrp-sponsor #yiv0509230174ov
 ul {
 margin:0;padding:0 0 0 8px;}
 
 #yiv0509230174 #yiv0509230174ygrp-text {
 font-family:Georgia;}
 
 #yiv0509230174 #yiv0509230174ygrp-text p {
 margin:0 0 1em 0;}
 
 #yiv0509230174 #yiv0509230174ygrp-text tt {
 font-size:120%;}
 
 #yiv0509230174 #yiv0509230174ygrp-vital ul li:last-child {
 border-right:none !important;
 }
 #yiv0509230174

Re: Bls: [belajar-excel] Ambil data berdasarkan data validasi

Kirim email ke