so not sure start i'll kind of dump out there , ask direction.
i have work book no less 40 work sheets. in original (name) sheet have 3 columns city, date, "to filled in"
in next ~30 sheets have 2 columns city, value (each of these sheets named day of month... (1,2,3,4,etc...)
i need put "value" sheets 1 - 30 column "to filled in" based on city matching , date column day matching worksheet day.
so example: original sheet contains:
city date "to filled in" c1 01012000 (missing) c1 01032000 (missing) c2 01012000 (missing) c2 01022000 (missing) c3 01012000 (missing) sheets 1-30 so: sheet 1 has: city value c1 5 c2 3 c3 1 sheet 2 has: city value c1 3 c2 10 c3 9 sheet 3 has: city value c1 1 c2 2 c3 2 so after run vba code original work sheet should this: original sheet contains:
city date "to filled in" c1 01012000 5 c1 01032000 1 c2 01012000 3 c2 01022000 10 c3 01012000 1 so need search worksheets based on day in date column, looking city in original sheet , once find city on correct tab, need pull value in sheet/row , populate missing data on original sheet.
i'd using vba. it's kind of vlookup, not, i'm bit stumped.
thanks suggestions.
you don't need vba this. excel's magical formulas work.
formula first extract date/sheet number using formula:
if(left(mid(b2,if(len(b2)=8,3,2),2),1)="0",right(mid(b2,if(len(b2)=8,3,2),2),1),mid(b2,if(len(b2)=8,3,2),2)) then concatenate "sheet" create sheet name.
concatenate("sheet",if(left(mid(b2,if(len(b2)=8,3,2),2),1)="0",right(mid(b2,if(len(b2)=8,3,2),2),1),mid(b2,if(len(b2)=8,3,2),2)), "!a:b") and use vlookup find value.
=vlookup(a2, indirect(concatenate("sheet",if(left(mid(b2,if(len(b2)=8,3,2),2),1)="0", right(mid(b2,if(len(b2)=8,3,2),2),1),mid(b2,if(len(b2)=8,3,2),2)), "!a:b"),true ),2,false)
if have dates in 1/1/2000 format instead of 01012000, use formula instead.
=vlookup(a2, indirect(concatenate("sheet",if(left(text(b2,"dd"),1)="0", right(text(b2,"dd"),1),text(b2,"dd")), "!a:b"),true ),2,false)
just place formula in cell c2 , drag down.
and result this.
a b c d ----------------------------------------------------------------- 1 city date "to filled in" formula used ----------------------------------------------------------------- 2 c1 01012000 5 =vlookup(a2, indirect( concatenate("sheet",if(left(mid(b2,if(len(b2)=8,3,2),2),1)="0", right(mid(b2,if(len(b2)=8,3,2),2),1),mid(b2,if(len(b2)=8,3,2),2)), "!a:b"),true ),2,false) 3 c1 01032000 1 =vlookup(a3, indirect( concatenate("sheet",if(left(mid(b3,if(len(b3)=8,3,2),2),1)="0", right(mid(b3,if(len(b3)=8,3,2),2),1),mid(b3,if(len(b3)=8,3,2),2)), "!a:b"),true ),2,false) 4 c2 01012000 3 =vlookup(a4, indirect( concatenate("sheet",if(left(mid(b4,if(len(b4)=8,3,2),2),1)="0", right(mid(b4,if(len(b4)=8,3,2),2),1),mid(b4,if(len(b4)=8,3,2),2)), "!a:b"),true ),2,false) 5 c2 01022000 10 =vlookup(a5, indirect( concatenate("sheet",if(left(mid(b5,if(len(b5)=8,3,2),2),1)="0", right(mid(b5,if(len(b5)=8,3,2),2),1),mid(b5,if(len(b5)=8,3,2),2)), "!a:b"),true ),2,false) 6 c3 01012000 1 =vlookup(a6, indirect( concatenate("sheet",if(left(mid(b6,if(len(b6)=8,3,2),2),1)="0", right(mid(b6,if(len(b6)=8,3,2),2),1),mid(b6,if(len(b6)=8,3,2),2)), "!a:b"),true ),2,false) 
Comments
Post a Comment