+ Reply to Thread
Results 1 to 10 of 10

Copying and pasting INDIRECT formula

Hybrid View

  1. 08-02-2013, 03:00 PM #1
    ala4900
    ala4900 is offline
    Registered User
    Join Date
    06-07-2011
    Location
    Detroit, MI USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Copying and pasting INDIRECT formula

    I'm working in Excel 2010 on a sheet that will be compiling data from a different worksheet based on the current month. I'm using the formula:

    =IF(INDIRECT($K$7&"!C45")>"",INDIRECT($K$7&"!C45"),"")

    to complete the field using the other sheets value if it's not empty, where $K$7 references a worksheet name based on the previous month.

    It's working just fine, but I want to copy and paste it into other cells having the referenced cell, C45, change as I move it around the sheet (if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"").

    I haven't been able to find a way to make the C45 cell reference dynamic while copying and pasting. It would save me a ton of time if I could.

    Any help would be greatly appreciated!
    Reply With QuoteRegister To Reply
  2. 08-02-2013, 03:04 PM #2
    mellowmarshall
    mellowmarshall is offline
    Registered User
    Join Date
    02-18-2013
    Location
    Georgia
    MS-Off Ver
    Excel 2010
    Posts
    48

    Re: Copying and pasting INDIRECT formula

    You'd have to rework the logic, since that's being read as a string.

    /thinkingcap

    Maybe something like
    =IF(INDIRECT($K$7&"!C"&COLUMN())>"",INDIRECT($K$7&"!C"&COLUMN()),"")

    This, of course, is assuming the column containing this code is the same as the column you're pointing at in the other spreadsheet.
    Last edited by mellowmarshall; 08-02-2013 at 03:21 PM.
    Reply With QuoteRegister To Reply
  3. 08-05-2013, 11:55 AM #3
    ala4900
    ala4900 is offline
    Registered User
    Join Date
    06-07-2011
    Location
    Detroit, MI USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying and pasting INDIRECT formula

    Couldn't get this to work. Yes the column is the same, in fact it's the exact same cell on the other worksheet.
    Reply With QuoteRegister To Reply
  4. 08-05-2013, 12:07 PM #4
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Copying and pasting INDIRECT formula

    Hi,

    Edit: "if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"")"

    I presume this is an oversight on your part? Surely you mean it to become:

    =IF(INDIRECT($K$7&"!F45")>"",INDIRECT($K$7&"!F45"),"")

    if pasted 3 cells to the right (and what you have if pasted 3 cells down)?

    Try:

    =IF(INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7))>"",INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7)),"")

    Regards
    Last edited by XOR LX; 08-05-2013 at 12:12 PM.
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/
    Reply With QuoteRegister To Reply
  5. 08-05-2013, 02:50 PM #5
    ala4900
    ala4900 is offline
    Registered User
    Join Date
    06-07-2011
    Location
    Detroit, MI USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying and pasting INDIRECT formula

    Quote Originally Posted by XOR LX View Post
    Hi,

    Edit: "if I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!C48")>"",INDIRECT($K$7&"!C48"),"")"

    I presume this is an oversight on your part? Surely you mean it to become:

    =IF(INDIRECT($K$7&"!F45")>"",INDIRECT($K$7&"!F45"),"")

    if pasted 3 cells to the right (and what you have if pasted 3 cells down)?

    Try:

    =IF(INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7))>"",INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7)),"")

    Regards
    My apologies. Yes, that should have read "If I copy and paste it 3 cells to the right it would become =IF(INDIRECT($K$7&"!F45")>"",INDIRECT($K$7&"!F45"),"")"

    I tried =IF(INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7))>"",INDIRECT(ADDRESS(ROW(C45),COLUMN(C45),4,1,$K$7)),""), but that's returning #REF!
    Last edited by ala4900; 08-05-2013 at 03:19 PM.
    Reply With QuoteRegister To Reply
  6. 08-05-2013, 03:20 PM #6
    ala4900
    ala4900 is offline
    Registered User
    Join Date
    06-07-2011
    Location
    Detroit, MI USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying and pasting INDIRECT formula

    OK, this worked:

    =IF(INDIRECT($K$7&"!"&SUBSTITUTE(ADDRESS(1,(COLUMN()),4),"1","")&ROW())>"",INDIRECT($K$7&"!"&SUBSTITUTE(ADDRESS(1,(COLUMN()),4),"1","")&ROW()),"")

    I'm not sure why. If anyone wants to explain that to me, I'd appreciate it!

    Thanks all for your help!
    Reply With QuoteRegister To Reply
  7. 08-05-2013, 03:01 PM #7
    XOR LX
    XOR LX is offline
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Copying and pasting INDIRECT formula

    Works for me. See attached.

    Post a sample if that doesn't clarify things.

    Regards
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  8. 08-05-2013, 03:03 PM #8
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Copying and pasting INDIRECT formula

    Try using CELL function which can convert a reference to a text value, i.e.

    =IF(INDIRECT($K$7&"!"&CELL("address",C45))>"",INDIRECT($K$7&"!"&CELL("address",C45)),"")
    Audere est facere
    Reply With QuoteRegister To Reply
  9. 08-05-2013, 03:32 PM #9
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Copying and pasting INDIRECT formula

    Did you try my suggestion, it's a little shorter......
    Reply With QuoteRegister To Reply
  10. 08-06-2013, 10:36 AM #10
    ala4900
    ala4900 is offline
    Registered User
    Join Date
    06-07-2011
    Location
    Detroit, MI USA
    MS-Off Ver
    Excel 2007
    Posts
    5

    Re: Copying and pasting INDIRECT formula

    I did, I got a #REF! error.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Copying down an indirect formula
    By sable in forum Excel General
    Replies: 5
    Last Post: 08-20-2009, 10:26 AM
  2. Copying and Pasting of Formula
    By calli in forum Excel - New Users/Basics
    Replies: 3
    Last Post: 05-05-2008, 04:44 PM
  3. copying and pasting a formula
    By William in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-21-2005, 08:30 PM
  4. Problem copying and pasting formula
    By Arsenalguy in forum Excel General
    Replies: 2
    Last Post: 10-07-2005, 01:05 PM
  5. [SOLVED] Copying & Pasting Formula
    By Connie Martin in forum Excel General
    Replies: 2
    Last Post: 03-03-2005, 02:06 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1