# Thread: FORMULA USING WORKSHEET NAME

1. I have a workbook with approx 48 sheets - each with a different 3 character name (each is a different city).
The layout on all will be the same. for example the cell A28 on the AUB worksheet will hold the same type of data as cell A28 on the ACH worksheet and all the others. Each cell will hold a date.
I want to copy the formula for all the cities into a separate worksheet in the same workbook(D-S-H-POSTED). The formula will extract three dates per month for each worksheets. I have attached a sample
The three columns with dates to copy on each worksheet are"Date D Posted" (A28), "Date S Posted" (B28) and "Date H Posted" (B44). Dates start in cell A28 of each sheet
I want to be able to copy the formula for all 12 months and all 48 sheets.
Hope you can help.
thanks

2. In F2: =INDIRECT(\$A2&"!A"&(26+INT((COLUMN()+2)/4)))
In G2: =INDIRECT(\$A2&"!B"&(26+INT((COLUMN()+2)/4)))
In H2: =INDIRECT(\$A2&"!B"&(42+INT((COLUMN()+2)/4)))

You can fill down F2:H2 down to row 50.

Then copy F2:H50 and paste to J2, to N2 etc.

3. Hans answered the question, but just a comment on the structure. I am not a proponent of this type of setup. I would suggest that you create just one sheet with that layout and include an additional column with the 3 digit city designation that you have as the sheetname.

Using autofilter on that city column will essentially give you the information you would have on any of those sheets, but you have the added advantage of using the autofilter with subtotals to get statistics or even pivot tables and pivot charts to summarize across the entire data set.

Steve

4. Thank you both. One more question.
I copied the formulas down but in the ones with no data yet I get an "#REF!" error.
Is there a way to leave those cells blank instead of displaying the error message.

thanks

5. You could use the following formula in F2:

=IF(ISERROR(INDIRECT(\$A2&"!A"&(26+INT((COLUMN()+2)/4)))),"",INDIRECT(\$A2&"!A"&(26+INT((COLUMN()+2)/4))))

and similar for G2 and H2. These formulas will return a blank instead of an error value.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•