Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    How to fill in a different direction (2003 sp2)

    I looked and perhaps am not searching on the right topic title. I am revising a workbook that feeds data from one sheet to another. There is a sheet in my workbook named Main that pulls data from a couple of different sheets in the book. The data in Main is then pulled into a large number of other sheets that are used as reports for the various locations that these sheets represent.

    The problem I am up against is that the data is for a 12 month period of time that had to be changed to match the fiscal year. It was originally set up to be from August to July when the actual fiscal year is July to June. Changing the calendars in each category on the Main page required making similar changes to the other pages that pull data from all the links on the other sheets that represent the locations. Where it gets tricky is the data on the Main page is arranged in rows with the months listed down the column on the left and the theater names listed across the top. But when you go to the individual sheets for each location the arrangement is the opposite. The data for each category is listed in rows with the months listed at the top of each column and the data titles listed at the beginning of each row, (I will try to strip out as much of the data as I can and post it here so you can see what I am talking about.)

    The problem I am having is getting the data on the location sheets to realign with the main sheet. When I enter the location for the data point on the location sheet (=Main!H24 for example in the July column) to be pulled from the Main sheet I am unable to then fill across the row with the follow on data points for each month so I am having to manually enter the link for each cell on the location sheet to pull from the Main sheet. Is there a way to drag/fil a series of links so that I don't have to manually set each link for each cell on the location sheets?

  2. #2
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to fill in a different direction (2003 sp2)

    I can't get the file below one mb without it really getting unusable. Is there a way to email the file to someone if they need it?

    Thom

  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

    Re: How to fill in a different direction (2003 sp2)

    You could make a small example file to show us the details.

    I think you can use an Index or an Offset based on the current row and column and it can be copied across and down to "transpose" the results.

    The exact formula will depend on your setup.

    Steve

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to fill in a different direction (2003 sp2)

    Have you tried compressing the file with PKZIP?
    Legare Coleman

  5. #5
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: How to fill in a different direction (2003 sp2

    Thom

    As an idea. I created a fictious table in sheet2 of a workbook in A1:C12.

    In sheet1!A1 I placed =sheet2!A1

    dragged down the values to A3 then highlighted the fields A1:C12 in sheet 1 and pressed ctrl+R all data was filled in.

    Have I missed your point or should I wait for your stripped down spreadsheet?
    Jerry

  6. #6
    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

    Re: How to fill in a different direction (2003 sp2

    I think he wants the links transposed.

    Steve

  7. #7
    Star Lounger
    Join Date
    Mar 2002
    Location
    California, USA
    Posts
    89
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: How to fill in a different direction (2003 sp2

    OK, Here is a sample worksheet that I think illustrates what I am trying to do. The cells C3:E14 and C16:E27 in the Master worksheet are pulled in from the Data Category Worksheets. The info on the Data Category sheets are either entered by hand or pulled from a different workbook. The data on the Master sheet then needs to be pulled into the individual locations. For example on the Location 1 worksheet cells C3:N3 should include the data from the Master worksheet cells C3:C14. I would like to be able to link Cell C3 in Location 1 to cell C3 in Master and then be able to drag fill C3:N3 in Location 1 with the data matching monthly data from the Master worksheet. I need to be able to do this for about 35 rows on 50 location worksheets in my actual workbook. So you can see I don't want to have to link each cell on the Location pages back to their matching cell on the Master page.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: How to fill in a different direction (2003 sp2

    The attached version shows two different approaches: the one in Location 1 uses TRANSPOSE in an array formula (confirmed with Ctrl+Shift+Enter), the other in Location 2 uses a combination of INDEX and MATCH.

Posting Permissions

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