Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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. #4
    Lounger
    Join Date
    Mar 2009
    Posts
    29
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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
  •