sleek12
Board Regular
- Joined
- May 3, 2014
- Messages
- 71
- Office Version
- 365
- Platform
- Windows
I am unable to discern where i am going wrong !
Any help given is appreciated,I started with Name manager
Sourcedatasheet==INDEX(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),""),N(IF(1,ROW(INDIRECT("4:"&COUNTA(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")))))))&T(NOW())
There is a dropdown list of names here--->(List worksheet)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Sheet is called Query[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S/no[/TD]
[TD]Sheet name month[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD]REF BRANCH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD]Trans Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(B4="","",MAX(A$3:A3)+1)[/TD]
[TD]=IFERROR(INDEX(List!C:C,MATCH(1,INDEX(--(ROW(A1)<=SUMIF(OFFSET(List!D$1,,,ROW(List!$1:$12)),"<>")),))),"")[/TD]
[TD]=IF(OR($B4="",C$3=""),"",IFERROR(INDEX(INDIRECT($B4&"!A3:S100"),AGGREGATE(15,6,ROW($A3:$A102)-ROW($A2)/(INDIRECT($B4&"!C3:C100")=$B$2),COUNTIF($B$4:$B4,$B4)),COLUMNS($A:A)),""))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
List worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Details[/TD]
[TD]To drag down below formula[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] DOUGLAS OMBATI AND CO ADVOCATE2014/07/070/001016 KAN 615W MAKINI AUCTIONEERS AGENCIES 2015/20/070/00050 KBR 066JA.G RIUNGU AND CO ADVOCATES 2015/01/080/2322 KBB 872GA.M MBINDYO AND CO ADVOCATES-2014/01/070/000305 KAQ 417UA.M TAILOR LTD-KISII ELECTRICITYA.M TAILOR LTD-KISII RENT APRIL - JUNE 2018ABISAI AND CO ADVOCATES-2016/75/080/001325 KAL 487QABRAHAM LINCOLN ONYANGO-2017/04/070/000297 KBL 780RACUMEN EQUITIES LTD-[/TD]
[TD]=IFERROR(INDEX(SourceDataSheet,ROWS($1:1)),"")
[TABLE="width: 646"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=IF(C2="","",INDEX(COUNTIF(INDIRECT("'"&SourceDataSheet&"'!C3:C50"),Query!$B$2),ROWS($1:1)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Any help given is appreciated,I started with Name manager
Sourcedatasheet==INDEX(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),""),N(IF(1,ROW(INDIRECT("4:"&COUNTA(REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1)),"")))))))&T(NOW())
There is a dropdown list of names here--->(List worksheet)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Sheet is called Query[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]S/no[/TD]
[TD]Sheet name month[/TD]
[TD][TABLE="width: 74"]
<tbody>[TR]
[TD]REF BRANCH[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 90"]
<tbody>[TR]
[TD]Trans Date[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]=IF(B4="","",MAX(A$3:A3)+1)[/TD]
[TD]=IFERROR(INDEX(List!C:C,MATCH(1,INDEX(--(ROW(A1)<=SUMIF(OFFSET(List!D$1,,,ROW(List!$1:$12)),"<>")),))),"")[/TD]
[TD]=IF(OR($B4="",C$3=""),"",IFERROR(INDEX(INDIRECT($B4&"!A3:S100"),AGGREGATE(15,6,ROW($A3:$A102)-ROW($A2)/(INDIRECT($B4&"!C3:C100")=$B$2),COUNTIF($B$4:$B4,$B4)),COLUMNS($A:A)),""))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
List worksheet
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Details[/TD]
[TD]To drag down below formula[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD] DOUGLAS OMBATI AND CO ADVOCATE2014/07/070/001016 KAN 615W MAKINI AUCTIONEERS AGENCIES 2015/20/070/00050 KBR 066JA.G RIUNGU AND CO ADVOCATES 2015/01/080/2322 KBB 872GA.M MBINDYO AND CO ADVOCATES-2014/01/070/000305 KAQ 417UA.M TAILOR LTD-KISII ELECTRICITYA.M TAILOR LTD-KISII RENT APRIL - JUNE 2018ABISAI AND CO ADVOCATES-2016/75/080/001325 KAL 487QABRAHAM LINCOLN ONYANGO-2017/04/070/000297 KBL 780RACUMEN EQUITIES LTD-[/TD]
[TD]=IFERROR(INDEX(SourceDataSheet,ROWS($1:1)),"")
[TABLE="width: 646"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=IF(C2="","",INDEX(COUNTIF(INDIRECT("'"&SourceDataSheet&"'!C3:C50"),Query!$B$2),ROWS($1:1)))[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Last edited: