update master list automatically from other worksheet

[vicmar]

hi,

I have three worksheet(CV, AR and Master List) one is master list what Im trying to do is when i update the CV or AR sheet it should be reflect in the master list automatically.

This is based on reference and revision. I try using vlookup, index and match but the problem is when the second submission. I attached the sample of my excel and enter manually the data that will appear in master list.

Thank you

[NBVC]

Can/will you prefill the Reference column (A) in the Master list, so that formulas can match that field in the other sheets?

[vicmar]

yes it will fill the same reference

[NoSparks]

Do you have any interest in a VBA macro solution ?

[vicmar]

i prefer formula because this file will be shared in the network but if that's the better solution its ok for me.

[NBVC]

Add a helper column in the AV and CV sheets.. (you can hide these columns afterwards)

in F3 of each sheet enter formula:

=IF(A3="","",A3&"_"&B3)

copied down. You can go as far as you want so that future entries will be covered... or you can turn your data into tables so that the formula will copy down automatically as you enter new data.

In the Master sheet in B5 enter formula:

=IF($A5="","",IFERROR(IF(COUNTIF(CV!$F:$F,$A5&"_"& 0),INDEX(CV!C:C,MATCH($A5&"_"&0,CV!$F:$F,0)),INDEX (AR!C:C,MATCH($A5&"_"&0,AR!$F:$F,0))),0))

copied across to column D and down as far as you want to capture future data.


The enter same formula in E5 and change all the &0 in the formula to &1 to correspond to Rev number

e.g.

=IF($A5="","",IFERROR(IF(COUNTIF(CV!$F:$F,$A5&"_"& 1),INDEX(CV!C:C,MATCH($A5&"_"&1,CV!$F:$F,0)),INDEX (AR!C:C,MATCH($A5&"_"&1,AR!$F:$F,0))),0))

.... then copy to column G and down.

Repeat for each revision.

[vicmar]

nice job @NBVC

thank you very much..

the last 0 I change it to "" so that if empty it will be blank.

[NBVC]

Ok.

You're welcome.

[vicmar]

hi @NBVC

I add more sheets and I adjust the formula as per below:

=IF($A5="","",IFERROR(IF(COUNTIF(CV!$F:$F,$A5&"_"& 0),INDEX(CV!C:C,MATCH($A5&"_"&0,CV!$F:$F,0)),INDEX (CV-SKT!C:C,MATCH($A5&"_"&0,CV-SKT!$F:$F,0)),INDEX(AR!C:C,MATCH($A5&"_"&0,AR!$F:$ F,0)),INDEX(LS!C:C,MATCH($A5&"_"&0,LS!$F:$F,0)),IN DEX(EL!C:C,MATCH($A5&"_"&0,EL!$F:$F,0)),INDEX(ME!C :C,MATCH($A5&"_"&0,ME!$F:$F,0)),INDEX(GE!C:C,MATCH ($A5&"_"&0,GE!$F:$F,0))),""))

But I encounter an error saying "You've entered too many arguments for this function"

[NBVC]

I apologize for the late response...been very busy at work....

Try:

=IF($A5="","",IFERROR(IF(COUNTIF(CV!$F:$F,$A5&"_"&0),INDEX(CV!C:C,MATCH($A5&"_"&0,CV!$F:$F,0)),IF(COUNTIF('CV-SKT'!$F:$F,$A5&"_"&0),INDEX('CV-SKT'!C:C,MATCH($A5&"_"&0,'CV-SKT'!$F:$F,0)),IF(COUNTIF(AR!$F:$F,$A5&"_"&0),INDEX(AR!C:C,MATCH($A5&"_"&0,AR!$F:$F,0)),IF(COUNTIF(LS!$F:$F,$A5&"_"&0),INDEX(LS!C:C,MATCH($A5&"_"&0,LS!$F:$F,0)),IF(COUNTIF(EL!$F:$F,$A5&"_"&0),INDEX(EL!C:C,MATCH($A5&"_"&0,EL!$F:$F,0)),IF(COUNTIF(ME!$F:$F,$A5&"_"&0),INDEX(ME!C:C,MATCH($A5&"_"&0,ME!$F:$F,0)),INDEX(GE!C:C,MATCH($A5&"_"&0,GE!$F:$F,0)))))))),""))

[vicmar]

no worries..

Thank you very much... its working but there is some conflict in my other formula.

Attached is the sample of my data:

[NBVC]

Try updating formula:

=IFERROR(IF(SUBSTITUTE(LOOKUP(2,1/((ISTEXT($Z17:$AO17)*($Z17:$AO17<>""))),$Z17:$AO17 ),"","")=B$15,1,""),"")

[vicmar]

it works the way I want it.. thank you very much!

[vicmar]

I notice that after I put all the formula in my actual excel file it becomes slow.

now its ok I change the range to a certain number of rows instead of the whole column.

Thank you

[NoSparks]

Not your stated preference, but you can check out this VBA macro solution.
No formulas required.