+ Reply to Thread
Results 1 to 10 of 10

Indirect and copying formulas

  1. 04-03-2015, 04:53 AM #1
    sknalodz
    sknalodz is offline
    Registered User
    Join Date
    01-22-2013
    Location
    Poland
    MS-Off Ver
    Excel 365 64bits
    Posts
    46

    Indirect and copying formulas

    Dear All,

    i need a help with an Indirect function.

    i have a formula
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    and i am willing to copy with a change of reference cell, to get
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    it should work also into column direction. i have quite huge table and i must copy based on the cell value (worksheet name).

    can you please help me?

    i was looking for the solution over internet and also on the forum but maybe my query was wrong...
    thanks in advance...
    Reply With QuoteRegister To Reply
  2. 04-03-2015, 04:57 AM #2
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,000

    Re: Indirect and copying formulas

    Which bit do you want to change? A2 to drag right and go to B2, C2, etc; or A2 to drag down and become A3, A4, etc, or both combined, or something else?
    Glenn



    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
    Reply With QuoteRegister To Reply
  3. 04-03-2015, 05:00 AM #3
    samba_ravi
    samba_ravi is offline
    Forum Guru samba_ravi's Avatar
    Join Date
    07-26-2011
    Location
    Hyderabad, India
    MS-Off Ver
    Excel 2021
    Posts
    8,914

    Re: Indirect and copying formulas

    =INDIRECT("'"&$B$3218&"'!"&A2) try this if you want to copy towards down
    Samba

    Say thanks to those who have helped you by clicking Add Reputation star.
    Reply With QuoteRegister To Reply
  4. 04-03-2015, 05:11 AM #4
    sknalodz
    sknalodz is offline
    Registered User
    Join Date
    01-22-2013
    Location
    Poland
    MS-Off Ver
    Excel 365 64bits
    Posts
    46

    Re: Indirect and copying formulas

    Quote Originally Posted by Glenn Kennedy View Post
    Which bit do you want to change? A2 to drag right and go to B2, C2, etc; or A2 to drag down and become A3, A4, etc, or both combined, or something else?
    both combined...


    Quote Originally Posted by nflsales View Post
    =INDIRECT("'"&$B$3218&"'!"&A2) try this if you want to copy towards down
    i get error #VALUE!

    when i use
    =INDIRECT("'"&$B$3218&"'!"& "A2") value is returned correctly...
    Reply With QuoteRegister To Reply
  5. 04-03-2015, 05:17 AM #5
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,000

    Re: Indirect and copying formulas

    Try this. It works for me!!

    INDIRECT("'"&$B$3218&"'!"&ADDRESS(ROWS($1:1)+1,COLUMNS($A:A),4))
    Reply With QuoteRegister To Reply
  6. 04-03-2015, 05:23 AM #6
    sknalodz
    sknalodz is offline
    Registered User
    Join Date
    01-22-2013
    Location
    Poland
    MS-Off Ver
    Excel 365 64bits
    Posts
    46

    Re: Indirect and copying formulas

    and it works for me !
    many thanks for quick reply!!!
    wishing you a very happy Easter
    Reply With QuoteRegister To Reply
  7. 04-03-2015, 05:34 AM #7
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,000

    Re: Indirect and copying formulas

    Glad to have helped! Happy Easter to you, too. I just hope the rain here stops!!

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.
    Reply With QuoteRegister To Reply
  8. 04-03-2015, 06:50 AM #8
    sknalodz
    sknalodz is offline
    Registered User
    Join Date
    01-22-2013
    Location
    Poland
    MS-Off Ver
    Excel 365 64bits
    Posts
    46

    Re: Indirect and copying formulas

    done. thanks.

    we have got also raining, snowing and quite strong wind...
    Reply With QuoteRegister To Reply
  9. 04-03-2015, 06:52 AM #9
    Glenn Kennedy
    Glenn Kennedy is offline
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,000

    Re: Indirect and copying formulas

    Thanks... We had a little blue sky, just for a few seconds, but now: it's gone again.
    Reply With QuoteRegister To Reply
  10. 04-03-2015, 08:07 AM #10
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Indirect and copying formulas

    You can also make an INDIRECT formula "draggable" by using CELL function, e.g.

    =INDIRECT("'"&$B$3218&"'!"&CELL("address",A2))
    Audere est facere
    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. [SOLVED] Automatic copying to another sheet using indirect
    By pauldaddyadams in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-05-2014, 11:51 AM
  2. Copying and pasting INDIRECT formula
    By ala4900 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-06-2013, 10:36 AM
  3. Indirect (with copying down rows)
    By singla in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2009, 06:15 PM
  4. Copying down an indirect formula
    By sable in forum Excel General
    Replies: 5
    Last Post: 08-20-2009, 10:26 AM
  5. copying cells with INDIRECT
    By inky in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-30-2008, 08:55 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1