Offset index match

[kumar lama]

Hello,
My master workbook(master data sheet) Column A has S.No. But this S.No Has break In middle, like A3= "AAAA" then from A4 Start S.No
like A4 = 1, A5=2 ,A6=3, A7=4, A8=5 ,A9=6 ......And again A10= "BBBB" and start S. No. Like A11=1,A12=2,A13=3,A14=4, A15=5........
Basicly AAAA & BBBB is a group, when person join for AAAA group i have to insert Row in last of AAAA. same in BBBB & CCCC also
i have another workbook =Group and every sheet by group AAAA, BBBB, & CCCC and i want update data from master to every sheet. I got S.No. from master sheet but i am unable to get other data, Match is getting Error
I use this formula to get S.No From Master Data
I got S. No like this
=IFERROR(IF(OFFSET('Master Data'!$A$3,1+C2,0)>0,OFFSET('Master Data'!$A$3,1+C2,0),""),"")
=IFERROR(IF(OFFSET('Master Data'!$A$65,1+C1,0)>0,OFFSET('Master Data'!$A$65,1+C1,0),""),"")

$A$3= AAAA
$A$65=BBBB
But i can't get another Date like column B Has ID NO. which i cant use index match for all over worksheet

is there someway to do it?

thank you in advance,

 

[Aladin Akyurek]

Mr. Aladin Akyurek Thank You for your help and hard work. In here sheet AAAA is working fine but sheet BBBB is not working, because in sheet AAAA formula "=MATCH($A$1,master!$A$2:$A$10,0)" $A$10 is changing as i insert New Row, But sheet BBBB i dont have to insert row, just i will add S. NO so the formula is not worked

and the array formula is really Long, very hard to ulderstand :P, i cant figure out what Choose() & Istext is doing there?

I have no idea what you are trying to say. If you add a record to the data at the end, the formulas for BBBB won't see the addition. Is that what you mean?

By the way, it's your lay-out that forces invoking complex formulas.

 

[kumar lama]

yes, what i mean is when i add data in last row of BBBB the formula is not working in sheet BBBB if i drag the formula..and also if i change the sheet name formula is won't work?

 

[Aladin Akyurek]

yes, what i mean is when i add data in last row of BBBB the formula is not working in sheet BBBB if i drag the formula..and also if i change the sheet name formula is won't work?

If you add records to the master sheet, you need to adjust the references in the formula either (1) manually or (2) using dynamic named ranges that adjust automatically when records are added or removed.

 

[kumar lama]

Thank you Very much

 

[Aladin Akyurek]

Thank you Very much

You are welcome.

Here is a dynamic version.

Define BigNum as:

=9.99999999999999E+307
[/code

Lrow as:

	
	
	
	
	
	



	
	

		
=MATCH(BigNum,master!$A:$A)

	

S.No as:

	
	
	
	
	
	



	
	

		
=master!$A$2:INDEX(master!$A:$A,Lrow)

	

ID as:

	
	
	
	
	
	



	
	

		
=master!$B$2:INDEX(master!$B:$B,Lrow)

	

Name as:

	
	
	
	
	
	



	
	

		
=master!$C$3:INDEX(master!$C:$C,Lrow)

	

Formulas for AAAA (and BBBB) become:

A1 is unmodified (see the post done earlier)

A2, just enter:

	
	
	
	
	
	



	
	

		
=MATCH($A$1,S.No,0)

	

A3, control+shif+enter:

	
	
	
	
	
	



	
	

		
=LOOKUP(BigNum,CHOOSE({1,2},MATCH(BigNum,S.No),
  LOOKUP(BigNum,IF(ROW(S.No)-ROW(INDEX(S.No,1,1))+1>MATCH($A$1,S.No,0),
  IF(ISTEXT(S.No),ROW(S.No)-ROW(INDEX(S.No,1,1)))))))

	

A5, just enter and copy down, unmodified:

	
	
	
	
	
	



	
	

		
=IF(ROWS(A$5:A5)<=$A$3-$A$2,ROWS($A$5:A5),"")

	

B5, just enter and copy down:

	
	
	
	
	
	



	
	

		
=IF($A5="","",OFFSET(INDEX(S.No,$A$2),$A5,1))

	

C5, just enter and copy down:

	
	
	
	
	
	



	
	

		
=IF($A5="","",OFFSET(INDEX(S.No,$A$2),$A5,2))

	

See:
https://dl.dropboxusercontent.com/u/65698317/kumar%20lama%20distribute%20to%20sheets%20dynamic%20version.xlsx