Without addressing your exact formula, something like: =IF(ISBLANK(A2),"",A2/100) but sometimes the ISBLANK doesn't evaluate properly because A2 looks blanks but contains a formula so then =IF(A2="","",A2/100) or =IF)A2<>"",A2/100,"")
Compare Excel | Excel Templates | DownloaderXL Pro
Finance Templates & Add-In Bundle | NeuroXL Predictor | Construction Estimating
Merge Excel The Easy Way | Trading Add-ins For Excel | Convert Excel Into Calculating Web Pages
Excel Web Pages | Produce Clean Efficient VBA Code Every Time | Build Automated Trading Models In Excel | Excel Web Pages | Excel Video Training
I have numerous linked spreadsheets. the master spreadsheet is a summary of eight other spreadsheets. So for example, cell A3 on the master might be cell A3 on spreadsheet 1, plus cell A3 on spreadsheet 2, plus cell A3 on spreadsheet 3 and so on. The problem is, if cell A3 on all the 8 spreadsheets is blank (because it concerns data to be entered in December), the master spreadsheet cell A3 value is given as "0". This messes up the linked graph, as it returns a figure of 0 for December, when in fact we haven't got to December. Is there a formula that could be used, e.g. "if the cell A3 in spreadsheet 1 and cell a3 in spreadsheet 2... is empty, then master cell a3 = empty, otherwise master cell a3 = (cell a3 in spreadsheet 1, plus cell a3 in spreadsheet 2 etc.) Or is there an easier way?
On the same lines, when I link a cell directly to a cell in another spreadsheet, and the source cell is empty, again it returns a value of "0" in the linked cell, rather than leaving it blank. Is there a way to link the two so that the linked cell on the graph is null if the source is null?
Thanks for your help,
Pete
Last edited by petehenson; October 24th, 2005 at 21:31.
Without addressing your exact formula, something like: =IF(ISBLANK(A2),"",A2/100) but sometimes the ISBLANK doesn't evaluate properly because A2 looks blanks but contains a formula so then =IF(A2="","",A2/100) or =IF)A2<>"",A2/100,"")
Barbara - aka The Cat Lady
Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.
Try this to return nothing if the sum of cells A1 on sheets 1 & 2 is 0 or sum them if it is not.
=IF(Sheet2!A1+Sheet1!A1 = 0,"",Sheet2!A1+Sheet1!A1)
You could try using a pivot table and pivot chart as these enable you to hide value such as blanks or months that you dont want to report on yet.
Thanks for that. The one thing I'm struggling with is calculating if multiple cells are blank, e.g. =IF(A2<>"" and B2<>"" and C2 <>"",A2/100,""). This formula does not work. What must I use instead of 'and'?
You can nest if's but only 7 at a time. Does my solution not work? Should do, although its not dependent on the ISBLANK function it is just saying if the sum of this + this+ this = nothing then display nothing otherwise do the sum.
The complication is that some values may well be zero in the data, not null. But your formula would show them as null on the master when they should be zero. Put crudely, you may be recorded as having done no work in July, Auugust and September, and recorded as nothing in October, November and December because those months are not complete. I would need the total for July-September to return as "0", but the total for October-December to return as blank. Otherwise on my graph it will show that you have done no work in Oct-Dec, when in fact the graph line should stop at September.
Also, I can't hide the future months on the graph, as the graph is used to project future performance up to the end of the financial year.
Last edited by petehenson; October 24th, 2005 at 22:25.
=IF(COUNTA(A2:C2)<>0,A2/100,"") or =IF(COUNTA(A1:B1,D1))<>0,A2/100,"") is one way but isn't always reliable is one of those cells has a formula that evaluate to "" and the othe 2 are blank.Originally Posted by petehenson
Barbara - aka The Cat Lady
Please do not U2U (private message) me directly for questions that should be posted to the forum; any such U2Us, unless requested by me, will be deleted.
why dont you try using a pivot chart? Its easy to then not report what you dont want to. so you could de select months oct-dec.
Thanks both of you. Barbarr, I should have said that A2, B2 and C2 are on different spreadsheets which makes it more difficult. I think I should be able to use nested If's though as you suggest, Neil.
The reason I'm trying to do this is that I have 56 spreadsheets of 8 pages each to update each month, which get their data from a single master spreadsheet (provided by someone else, so I'm limited a bit in what I can do).
At the moment it's a case of adjusting every worksheet each month so the most recent month's data is showing. I'm trying to make this easier, hence my question. I think it'll take a lot of work out of it if the nested if's idea works, certainly for the graphs.
Thanks again,
Pete
I sorted that now and it works fine on the spreadsheets. However when doing it on a graph - If(A1="","",A1) it plots A1 as 0 on the graph even if A1 is blank. There are two lines on the graph. The other line is the target for the whoel year, sp the graph must have all the months on display.
Is there any way to display just part of the other line, i.e. not the months that are returned as blank by the above formula?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks