+ Reply to Thread
Results 1 to 6 of 6

Referencing cells in a workbook

  1. #1
    Registered User
    Join Date
    05-06-2015
    Location
    austin
    MS-Off Ver
    2007
    Posts
    7

    Referencing cells in a workbook

    Hey Everyone,

    I have a workbook that has 12 different worksheets. On my last worksheet I have a table that references the same exact cells on each of the different worksheets.

    Example in the formula bar:
    =week3!$j$22
    =week3!$j$36
    =week3!$j$68

    It displays just fine, but when I highlight and drag the cells over to fill the rest of table it continues to pull the data from the same worksheet. Is there a way to correct this so that when I click and drag it will fill correctly from the correct worksheets; or do I have to manually reference the correct worksheet?
    Any help would be appreciated.

  2. #2
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Referencing cells in a workbook

    When you drag to the left Excel will stay on the same referenced worksheet. Because your reference is locked with the $ when you drag it will still reference J22 no matter what.

    If you want to reference a different worksheet you can use indirect. You would need another row which has the correct worksheet names on them and then you could drag to the left. In the code below, the sheet number is the only thing that changes. I have the number 1 in cell A1 and the number 2 in cell A2. When I drag this to the left it will change the reference from sheet1 to sheet2.

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    05-06-2015
    Location
    austin
    MS-Off Ver
    2007
    Posts
    7

    Re: Referencing cells in a workbook

    Here is an example of what I have. The other sheets are hidden and it starts from week 7 since I've manually done the others.
    Attached Files Attached Files

  4. #4
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Referencing cells in a workbook

    Put this formula in H3 and copy to the left. You can edit it for the other rows. The substitute part is just in there to get rid of the space you have in the name in the cell reference.

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-06-2015
    Location
    austin
    MS-Off Ver
    2007
    Posts
    7

    Re: Referencing cells in a workbook

    Please Login or Register  to view this content.
    [/QUOTE]

    This worked on the orginal workbook, but when I tried to use the same formula for a new workbook set up exactly the same way, referencing all the same info I get a #REF! error.

    The previous example I have attached above is the spreadsheet I am using now. If you unhide the cells I started at week 1 this time.

  6. #6
    Valued Forum Contributor nigelbloomy's Avatar
    Join Date
    11-06-2013
    Location
    USA
    MS-Off Ver
    Excel 2010
    Posts
    1,011

    Re: Referencing cells in a workbook

    Your week 1 and Week 2 sheets have a space inbetween the week and the number. The rest of your sheets have no space. If you go in and remove the space on those two sheets everything should work fine.
    Some people volunteer in soup kitchens or hospitals. I choose to make the world better by trying to help you with Excel. We're all learning.

    <---Click * Add Reputation for all helpful comments. It's like giving a smile.
    Forum Rules: How to mark your post [Solved] and have a happier, Excel enriched life.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Referencing cells from another workbook
    By saq7792 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-04-2015, 08:42 AM
  2. [SOLVED] Index Match Function Within One Workbook Referencing Cells in Another workbook error
    By Hackboss007 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-18-2014, 12:06 PM
  3. Help Referencing other Workbook cells
    By FRIZZO in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-02-2012, 03:37 AM
  4. Referencing Inactive Workbook Cells
    By PvanS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-22-2009, 07:35 AM
  5. [SOLVED] Referencing Cells of Different Workbook
    By Mike in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-14-2006, 01:20 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1