New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Hyperlinks, Zero Values, Plotting only values > '0'

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Hyperlinks, Zero Values, Plotting only values > '0'

Postby tamarina » Tue May 24, 2016 2:04 pm

I have a graph that is automated to update based on entries on a separate workbook using a hyperlink. I need to add an 'if' statement formula to each cell that returns a '0.0%' because the data has not yet been entered on the referenced workbook so that it returns a blank instead of a '0.0%'. The graph shows production numbers and since it's automated for future dates, my boss thinks I'm forecasting zeros. :(

I tried =IF(A1=0,"",referenced workbook), but that still returned '0.0%'. I tried =IF(A1=0,"",FALSE) and =IF(A1=0,'N/A') nothing has worked so far. I still get the '0.0%'.

I'd like to maintain the automation, but return a blank cell when the hyperlink returns '0.0%' so that the graph does not plot the zeros.
  • 0

tamarina
Rookie
 
Posts: 2
Joined: May 24, 2016
Reputation: 0
Excel Version: 2013

Re: Hyperlinks, Zero Values, Plotting only values > '0'

Postby tamarina » Tue May 24, 2016 4:04 pm

Apparently, I was missing a few key pieces in my formula. Here is what worked:

=IF(referenced workbook=0,NA(),referenced workbook)

I was using the cell itself (A1) instead referencing the linked workbook in my original tries. Once I used the linked workbook and remembered to put the '()' in after the 'NA', it returned the result #NA which does not plot on my graph.

I also tested the linked worksheet with newly input data to ensure that when it is NOT '0' it returns the new number (or live data). It worked!
  • 0

tamarina
Rookie
 
Posts: 2
Joined: May 24, 2016
Reputation: 0
Excel Version: 2013


Return to General Excel Questions

Who is online

Users browsing this forum: No registered users and 21 guests