+ Reply to Thread
Results 1 to 4 of 4

Copying reference to same cell on multiple sheets into multiple cells on 1 sheet

  1. #1
    Registered User
    Join Date
    06-19-2014
    Location
    Germany
    MS-Off Ver
    Home and Student 2010
    Posts
    1

    Copying reference to same cell on multiple sheets into multiple cells on 1 sheet

    Hi everyone, sorry if the subject is confusing.

    I have a workbook with 100 sheets that feed data to a ledger sheet. The ledger is by necessity in a column format, and I want to reference B6 on the other 100 sheets. When I put in ='2'!B$6 and copy/paste I wind up with the entire column referencing sheet 2. How do I get it to reference 3, 4, etc?

  2. #2
    Valued Forum Contributor Hawkeye16's Avatar
    Join Date
    02-27-2013
    Location
    Holland
    MS-Off Ver
    ├┤ Pew Pew
    Posts
    441

    Re: Copying reference to same cell on multiple sheets into multiple cells on 1 sheet

    You may need to attach a small sample workbook with no sensitive data for an absolute answer.

    When you want to copy over and have the tab names change you usually use an Indirect statement with the tab name being an input into the formula.

    i.e. if your tab names are in row 1 you would use something similar to
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Despite the high cost of living, it remains very popular.

    Don't forget to mark threads SOLVED when you get an answer and rep all the geniouses that helped you today!

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,419

    Re: Copying reference to same cell on multiple sheets into multiple cells on 1 sheet

    Hi, and welcome to the forum.

    You haven't said to where you are copying this formula.

    Assuming it's down a series of rows, and let's say you start in row 10 then an Indirect will do it. i.e

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and copy this down.

    Note that if you start on another row the -8 number in the formula will need to change so that the row you're on minus a new number = 2.
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,419

    Re: Copying reference to same cell on multiple sheets into multiple cells on 1 sheet

    Thanks for the rep. Glad to have helped.

+ 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. Replies: 5
    Last Post: 01-25-2013, 08:04 AM
  2. Copying multiple cells from multiple worksheets to one summary sheet
    By Dragothemensch in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 01-18-2013, 08:39 PM
  3. Replies: 0
    Last Post: 03-27-2012, 04:54 PM
  4. Printing/copying multiple cells from Multiple sheets
    By browner2003 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-12-2011, 12:53 PM
  5. Copying cells from 1 sheet to multiple sheets
    By stevenwhite1968 in forum Excel General
    Replies: 3
    Last Post: 05-10-2011, 09:49 AM

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