Results 1 to 5 of 5

Thread: Indirect Address Column Formula help needed

  1. #1
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0

    Indirect Address Column Formula help needed

    The formula below looks for a date selected in a combo box, then goes to another worksheet and returns the column letters of that date. The dates in the other worksheet is a rolling 21 week date range.

    My question: Is there a way to display a "N/A" in the cell if the latest week's data is not updated yet? Right now, if there's missing data during the week selected, the cell shows a "0"... I tried to do a IF statement where that cell equals "0" but it kept giving me a circular error and it never worked anyways.

    Any help is greatly appreciated.

    =INDIRECT(ADDRESS($A313,COLUMN(INDIRECT(""&$E$4&"! "&G$4&"$3:"&G$4&"$509")),1,TRUE,"DMI"))

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    May be..

    =IF(ISERROR(1/INDIRECT(ADDRESS($A313,COLUMN(INDIRECT(""&$E$4&"! "&G$4&"$3:"&G$4&"$509")),1,TRUE,"DMI"))),NA(),INDI RECT(ADDRESS($A313,COLUMN(INDIRECT(""&$E$4&"! "&G$4&"$3:"&G$4&"$509")),1,TRUE,"DMI")))

    or you could even custom format the cells

    [=0]"#N/A";mm-dd-yyyy
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0
    BRILLIANT....it works! thanks so MUCH!

  4. #4
    Junior Member
    Join Date
    Mar 2012
    Posts
    21
    Rep Power
    0
    I tried to be creative and see if it worked using the same formula to calculate week over week. However, the above formula isn't working for me too well. If the reported week is not calculated, it technically shows a zero, which you then help me to show "n/a" instead. That works...however, when I have another column that looks at week over week change, when it sees the "n/a", it will calculate the week over week average to be at -100%.

    Is there a way to fix the below formula to show that if the result is -100% or if the actual reported week shows "n/a", that the week over week column also shows "n/a"? Any advice is GREATLY appreciated!

    =IF(ISERROR(1/(INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4&"! "&F$4&"$3:"&F$4&"$509")),1,TRUE,"DMI"))/INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4&"!" &J$4&"$3:"&J$4&"$509")),1,TRUE,"DMI"))-1)),"n/a",(INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4 &"!"&F$4&"$3:"&F$4&"$509")),1,TRUE,"DMI"))/INDIRECT(ADDRESS($A260,COLUMN(INDIRECT(""&$E$4&"!" &J$4&"$3:"&J$4&"$509")),1,TRUE,"DMI"))-1))

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    Better to use helper column/row.

    Put the original formula in the helper column/row. In the actual cells try something like

    =if(or(helpercell="n/a",helpercell="-1"),"n/a",helpercell)

    The original formula I meant was the first one.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

Similar Threads

  1. Replies: 15
    Last Post: 06-04-2013, 07:02 PM
  2. VBA to avoid - "indirect" Formula
    By leopaulc in forum Excel Help
    Replies: 2
    Last Post: 10-23-2012, 05:01 PM
  3. Replies: 11
    Last Post: 10-07-2012, 12:05 AM
  4. Sum Ifs Formula needed with Max Date range
    By trankim in forum Excel Help
    Replies: 2
    Last Post: 09-19-2012, 09:50 AM
  5. Replies: 6
    Last Post: 04-03-2011, 09:46 PM

Posting Permissions

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