INDIRECT formula cell reference
Page 1 of 2 12 LastLast
Results 1 to 10 of 17

Thread: INDIRECT formula cell reference
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default INDIRECT formula cell reference

    Hi I am having trouble referencing the cell in below formula vs. text.



    =SUMPRODUCT(SUMIF(INDIRECT(""&data&"!"&"A6:A6"),$B12,INDIRECT(""&data&""!"&"D6:D6")))

    data represents the line for sum values and name range of sheets (D6)..A6 is lookup on sheets and B12 is criteria.

    The formula works but I have to go through ad manually change text vs dragging
    Last edited by rlexcel101; Jul 17th, 2019 at 01:33 PM.

  2. #2
    New Member
    Join Date
    Apr 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDIRECT formula cell reference

    Any help would be greatly appreciated

  3. #3
    New Member
    Join Date
    Apr 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDIRECT formula cell reference

    Bump

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: INDIRECT formula cell reference

    this is an example to combine indirect() and offset(), see if you can adopt it for your problem

    ABC
    15Sheet2
    210
    315
    420
    525

    Sheet1



    Worksheet Formulas
    CellFormula
    A1=SUM(OFFSET(INDIRECT($C$1&"!$A$1"),ROW(A1)-ROW($A$1),0,1,5))



    ABCDE
    111111
    222222
    333333
    444444
    555555

    Sheet2




  5. #5
    New Member
    Join Date
    Apr 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDIRECT formula cell reference

    I have the formula which works in pulling correct values I just have my reference as texts and trying to use a cell reference so I can drag formula

  6. #6
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: INDIRECT formula cell reference

    What do you want the formula below the original one to be doing if you drag it down one cell? Using A6:A7, A7:A7, something else?

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: INDIRECT formula cell reference

    Maybe this one which does A6:A6, A6:A7 etc as you drag:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A6:A"&ROWS($A$6:A6)+5),$B12,INDIRECT("'"&data&"'!D6:D"&ROWS($A$6:A6)+5)))

  8. #8
    New Member
    Join Date
    Apr 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDIRECT formula cell reference

    Quote Originally Posted by steve the fish View Post
    Maybe this one which does A6:A6, A6:A7 etc as you drag:

    =SUMPRODUCT(SUMIF(INDIRECT("'"&data&"'!A6:A"&ROWS($A$6:A6)+5),$B12,INDIRECT("'"&data&"'!D6:D"&ROWS($A$6:A6)+5)))
    thanks

    I inputted this formula and pulls but it’s still not dragging.

    So D6 is basically one month but if I drag it stays as D6 when I need E6

  9. #9
    New Member
    Join Date
    Apr 2018
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: INDIRECT formula cell reference

    Still struggling trying to get “D6:D6” to reference as cells and drag across vs as text

    Would really appreciate the help!

  10. #10
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: INDIRECT formula cell reference

    You need to be answering the question in post 6. I presume you are dragging right. So what is the formula you want in the cell next to the original to be? Put it here in exactly the same way as you did for the original formula but the cell to the right. I would need to know what needs to change from cell to cell.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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