Excel formula help

Discussion in 'Mac Apps and Mac App Store' started by GimmeSlack12, Oct 22, 2010.

  1. GimmeSlack12 macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #1
    I really am at my rope's end on this. I downloaded my vibration data from tons of measurements and it puts everything in its own Worksheet. So for one file I have about 30 worksheets each with their own data.

    I'm trying to get everything on to one worksheet and am doing this by simple cell references example: ='Address 1'!A1

    Now to make my life easier I want to be able to have the Worksheet number a variable so I can do a 'fill right' and all the data is pulled from each sheet. I have a summary worksheet that has the measurement numbering that I want to reference, this row is just numbers (1, 2, 3...).

    What I want is: ='Address <row number>'!A1

    This will allow me to do a fill right and the worksheet names will fill in and pull the correct data. Help!
    (please ask if this doesn't make sense)
     
  2. Tomorrow macrumors 604

    Tomorrow

    Joined:
    Mar 2, 2008
    Location:
    Always a day away
    #2
    You can't do a simple copy and paste? :confused:

    Maybe I don't understand the problem.
     
  3. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #3
    The problem when I c&p the 'Address 1' remains 'Address 1' and doesn't update to 'Address 2' 'Address 3' and so on. Thats where the problem lay.
     
  4. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #4
    If I understand correctly, you want to create formulas, where each formula refers to a different worksheet, which are numbered? Like this?
    Row:.......Column A
    1........='Worksheet 1'!A1
    2........='Worksheet 2'!A1
    3........='Worksheet 3'!A1
    4........='Worksheet 4'!A1
    5........='Worksheet 5'!A1​
     
  5. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #5
    Correct. That's what I want. I looked at the INDIRECT function but got confused, and I don't have the time for VBA at the moment.
     
  6. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #6
    Start with a column of the numbers you want, then use the CONCATENATE function to create the formulas:
    ScreenCap 7.PNG
    Create the first formula, then drag to copy to the other rows.
    When you're done creating them, copy them and Paste Special > Values.
    You may have to click in the formula bar for each one and press enter to make the created formula active.
     
  7. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #7
    That works great, but I actually want the value from the specified worksheet. I was hoping to get "865.55" instead of ='Address 1'!K5
     
  8. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #8
    See the last line of my post that I just added.
     
  9. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #9
    Duh. I knew I was missing something right in front of me. Thanks for helping out Studios!
     
  10. GGJstudios macrumors Westmere

    GGJstudios

    Joined:
    May 16, 2008
    #10
    Happy to help! I added it to my post late, which is why you didn't see it before. Concatenate and VLOOKUP are two of my favorite Excel formulas.
     
  11. GimmeSlack12 thread starter macrumors 603

    GimmeSlack12

    Joined:
    Apr 29, 2005
    Location:
    San Francisco
    #11
    Ha, VLOOKUP and HLOOKUP are infinitely useful!
     

Share This Page