Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cell References (2003)

    Hey there ... I'm trying to do something rather tricky here. I have received a couple of hundred forms from different people that were completed in Excel. I would like to extract information from each of them in a separate report sheet. I've named each of the spreadsheets with a numeric - i.e. 2005 Survey001, 2005 Survey002, etc. I created a 3D formula for the first survey in my report sheet -- but I would like to be able to automate bringing this down to the subsequent cells to extract from other forms. Obviously using AutoFill isn't going to work because it's not the cell that I want to change, but rather the form name. Does anyone have another suggestion?

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

    Re: Cell References (2003)

    Since you did not show your whole formula, I can't be exact, but you can do something like this:

    =INDIRECT("'2005 Survey"&TEXT(ROW(),"000")&"'!A1")

    That formula can be copied down. If you don't start in row 1, you will need to subtract something from the ROW() to get the correct number.
    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell References (2003)

    Here's the original formula
    =[Diary05.001.xls]Sheet1!$C$15
    How would I apply that the Indirect formula?

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

    Re: Cell References (2003)

    What row does that formula go in?
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Mar 2003
    Location
    Toronto, Ontario, Canada
    Posts
    94
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cell References (2003)

    A1

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

    Re: Cell References (2003)

    I also just noticed that this formula looks like it is referring to different workbooks, not different worksheets as your first post indicated. Is this correct? If so, the formula would look like this:

    <pre>=INDIRECT("[Diary05."&TEXT(ROW(),"000")&".xls]Sheet1!$C$15")
    </pre>


    Warning!!! Alll of the workbooks must be open for this to work.
    Legare Coleman

Posting Permissions

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