autofill a formula that references worksheet tabs

**menziesthefish** · 03-29-2010, 07:25 AM

Hi there,

I currently have a very simple formula that brings in data from another tab within the same worksheet:

='JN0001'!C$3$

[where JN0001 is the tab name, and C3 the cell being referenced.]

Is there a way to amend this formula, or a function in Excel, so that when I drag down/autofill down it only changes the tab name (i.e. becomes ='JN0002'!C$3$, then ='JN0003'!C$3$, etc...)?

Many thanks
M

**NBVC** · 03-29-2010, 08:36 AM

Try:

=INDIRECT("'JN"&TEXT(ROWS($A$1:$A1),"0000")&"'!C3")

**menziesthefish** · 03-29-2010, 09:45 AM

Hi NBVC,

Thanks for your reply. I've tried the formula though and just get a blank cell. Do I need to adjust the formula, or the cells it references, in any way to fit my purpose?

Thanks
M

**NBVC** · 03-29-2010, 09:54 AM

I just tried it again to make sure, and it works.

Check that you actually have something in cell C3 of JN0001, JN0002, etc...

and make sure that the tab names are actually those, i.e. JN followed by 3 0's and then 1, etc..