+ Reply to Thread
Results 1 to 3 of 3

Updating cell references when copying and pasting down rows

  1. #1
    Registered User
    Join Date
    10-19-2007
    Posts
    4

    Updating cell references when copying and pasting down rows

    I've tried multiple searches and didn't see this question already posted, so hopefully somebody can help me.

    I have data on a data table that is horizontally-oriented, in other words the data goes across columns (C4, G4, K4, O4, etc). There is always a three column gap.

    The data presentation tab was originally horizontally-oriented so the design worked well, but now they want it to be vertically-oriented. Instead of recreating the entire datasheet, which would take many hours, I want to just change the way the presentation tab references the data sheet. Specifically, I need DataSheet!C4 to go in B2, DataSheet!G4 to go in B3, DataSheet!K4 in B4, DataSheet!O4 in B5, etc.

    The only way I've been able to do this is to manually update each row but there are a lot of rows that need to be updated. I'm hoping somebody out there knows a way that I can paste my cell references down Column B but have the column reference update instead of the row reference. Obiously I can fix my row reference (DataSheet!C$4) but how do I get the C$4 reference to update to G$4 when I copy and paste it from B2 to B3?

  2. #2
    Forum Expert Paul's Avatar
    Join Date
    02-05-2007
    Location
    Wisconsin
    MS-Off Ver
    2016/365
    Posts
    6,885
    Hello bradams,

    Try this formula in B2 and fill downward:

    =OFFSET($C$4,0,ROW(B1)*4-4)

    This should show the value of C4 in B2, G4 in B3, K4 in B4, O4 in B5, etc..

  3. #3
    Registered User
    Join Date
    10-19-2007
    Posts
    4

    Thank you, Paul!

    That works perfectly. Thank you for saving me a lot of manual updating.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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