Dynamic Sheet Name (Excel XP)

Topic

New Reply

Assume the following formula in cells a1=Jan!$d$4, b1=Feb!$d$4,c1=Mar!$d$4,d1=Apr!$d$4. This continues across row 1 for a total of twelve columns with the last formula =Dec!$d$4. I have hundreds of formulas with this pattern to create and copy across rows on a sheet and then must create the same type of formulas on different sheets. Is there any way that I can dynamically name the sheet name so that when I copy from a1 across the other 11 columns, that the sheet name is correct (i.e., formula in column b is for sheet feb; formula in column c is for sheet mar)? Right now, I am copying the a1 formula across the columns and then manually changing each of the other 11 cells to the proper sheet name since the cell location is absolute. I have thousands of these to do and was thinking there has to be a better way. THANKS.

Reply | Quote

Replies

– Type Jan into cell A1
– Drag the fill handle to the right untill you get to Dec
– in A2 type this formula:

=”A=” & A1 & “!A1”

The result will be (in A2): A=Jan!A1

Now select the 12 cells on row 2 and drag its border whilst holding the right mouse key on top of A1:L1 and release.
From the menu that appears select “Copy Here as values only”.

Finally, hit control-H and find A= and replace with =

Reply | Quote

Steve,
How can I adjust your formula to start in B1 and not A1. Column A is used for descriptions and the twelve months of monthly data starts in column B and moves by month to the right. I have tried moving the “1” in your formula to zero but that does not work. I read through the on-line help, but to be honest, I still don’t understand the indirect formula and its options. Thanks for your patience. Thanks also to Legare..he was right. Since I was so unfamiliar with the indirect function, I wanted to “play with it” in an empty workbook and not the production workbook that contained the Jan, Feb, etc, worksheets. Take care.

Reply | Quote

I assume that you meant to reply to post 474907 by Steve. The DATE function has syntax DATE(year, month, day). Steve’s formula uses DATE(2005,COLUMN(),1), i.e. year=2005, month=COLUMN() and day=1. The COLUMN() function returns the column number of the cell containing the formula, i.e. 1 for column A, 2 for column B etc. To make column B correspond to the 1st month, you must subtract 1 from the column number: DATE(2005,COLUMN()-1,1), so the formula becomes

=INDIRECT(TEXT(DATE(2005,COLUMN()-1,1),"mmm")&"!D4")

Reply | Quote

Hans,
Yes, I replied to the wrong post…sorry. Thanks for the explanation. Take care.

Reply | Quote

How about this in A1 and then copy it?

=INDIRECT(TEXT(DATE(2005,COLUMN(),1),"mmm")&"!D4")

Steve

Reply | Quote

Steve,
This formula gives me a #REF! error. Any ideas? THANKS.

Reply | Quote

That says you don’t have a sheet in the workbook named Jan, or Feb, or whatever matches the column you put that formula in.

Reply | Quote