Cell Reference as Sheet name

[aliaslamy2k]

Dear Experts,


I want to modify this formula to read cell reference as Sheet name. I tried entering as "'"&B14&"'! But it gives me an error.


L 0 =IF(ROWS(D$14:D14)>$D$12,"",INDEX("'"&B14&"'!$B$4:$B$37,SMALL(IF(("'"&B14&"'!$A$4:$A$37=$B$14),ROW("'"&B14&"'!$B$4:$B$37)-ROW("'"&B14&"'!$B$4)+1),ROWS(D$14:D14))))

D				R				L				J				H						Countries	Total						D
Assigned Countries		Total Assigned		Assigned Countries		Total Assigned		Assigned Countries		Total Assigned		Assigned Countries		Total Assigned		Assigned Countries		Total Assigned				Tunisia	361						R
Tunisia	361	659		India	95	232		Kyrgystan	50	140		Lebanon	266	286		Lebanon	192	219				India	198						L
India	103			Sri Lanka	102			Russia	90			Kyrgystan	20			Spain	27					Sri Lanka	194						J
Sri Lanka	92			Kenya	35																	Kenya	138						H
Kenya	103																					Lebanon	458
																						Kyrgystan	70
																						Spain	27
																						Russia	90
																						Total	1536
									L	0		=IF(ROWS(D$14:D14)>$D$12,"",INDEX("'"&B14&"'!$B$4:$B$37,SMALL(IF(("'"&B14&"'!$A$4:$A$37=$B$14),ROW("'"&B14&"'!$B$4:$B$37)-ROW("'"&B14&"'!$B$4)+1),ROWS(D$14:D14))))

 

[RoryA]

You need INDIRECT. So instead of this:

"'"&B14&"'!$A$4:$A$37

you'd use this:

INDIRECT("'"&B14&"'!$A$4:$A$37")

Note that this will make your formulas volatile so that they will recalculate whenever an open workbook does, even if their inputs haven't changed.

 

[aliaslamy2k]

Dear RoryA,

I am really sorry, i am not sure how to embed this in my formula. I would highly appreciate if you could add this in my formula.


I tried like this but its not working.

=IF(ROWS(D$14:D14)>$D$12,"",INDEX(INDIRECT("'"&B14&"'!$A$4:$A$37")$B$4:$B$37,SMALL(IF((INDIRECT("'"&B14&"'!$A$4:$A$37")$A$4:$A$37=$B$14),ROW(INDIRECT("'"&B14&"'!$A$4:$A$37")$B$4:$B$37)-ROW("'"&B14&"'!$B$4)+1),ROWS(D$14:D14))))

 

[RoryA]

Like this:

=IF(ROWS(D$14:D14)>$D$12,"",INDEX(INDIRECT("'"&B14&"'!$B$4:$B$37"),SMALL(IF((INDIRECT("'"&B14&"'!$A$4:$A$37")=$B$14),ROW(INDIRECT("'"&B14&"'!$A$4:$A$37"))-ROW(INDIRECT("'"&B14&"'!$B$4"))+1),ROWS(D$14:D14))))

given that this is an array formula, if you have a lot of these, I would really advise against using INDIRECT but you can test it and see what performance is like for you.

 

[aliaslamy2k]

Like this:

=IF(ROWS(D$14:D14)>$D$12,"",INDEX(INDIRECT("'"&B14&"'!$B$4:$B$37"),SMALL(IF((INDIRECT("'"&B14&"'!$A$4:$A$37")=$B$14),ROW(INDIRECT("'"&B14&"'!$A$4:$A$37"))-ROW(INDIRECT("'"&B14&"'!$B$4"))+1),ROWS(D$14:D14))))

given that this is an array formula, if you have a lot of these, I would really advise against using INDIRECT but you can test it and see what performance is like for you.

Dear RoryA,

Thank You so much. The formula is working very well. Thanks a million once again.