Results 1 to 2 of 2
2001-04-10, 17:46 #1
- Join Date
- Jan 2001
- Baltimore, MD, Maryland, USA
- Thanked 0 Times in 0 Posts
Prevent #N/A,Ref,etc., when equations ref "" cells
I have set up some forecasting and tracking models in the following manner:
1) I return data from an external source to a WS (say to columns A through N and starting in say row5, with row4 having headers. Of course, each time I do this I get a different number of rows that satisfy my query;
2) In columns O through R, I transform data from several of the A through N columns, entering functions like:
=left(B5,3) into say O5 and into P5 maybe something like =VLOOKUP(O5,Array_table,Col,False) to pick up a related variable from another source. For example, B5 might have a project name like PNA-CA2 (one of several PNA projects) and I need to roll up the current production for all these projects into a PNA and even PN subgroups (since all the PN's draw on a common set of resources). I use a PIVOT Table to get these subtotals (which I then input into planning models which tell me the resources (like FTEs) that I will need for the different product groups to meet their respective production deadlines (which vary for each project PNAA-CA2, etc.).
3) Of course, I was getting #N/A's and #VALUE's in all the rows below the last data row and, since some of the equations now had REFs in them, they would not recalculate the next time the refreshed data extended beyond the last row of a previous import. Of course, this invalidated the PIVOT Table and the subsequent model (and I still have a job, you ask).
4) So, I tried using IF statements which would return Blanks, when the equation encountered a blank in a reference cell; e.g., like for Col O (see above): =IF(B5="","",left(B5,3)); But this does not work either. The equations still fail to recalculate.
The bottom line, at this point, is that "I don't know my way outa here ..." (isn't this line from a Bob Dylan song?). So, I am calling for HELP. I need it bad this time.
<img src=/S/nuts.gif border=0 alt=nuts width=15 height=15>
2001-04-10, 18:58 #2
- Join Date
- Dec 2000
- Salt Lake City, Utah, USA
- Thanked 6 Times in 6 Posts
Re: Prevent #N/A,Ref,etc., when equations ref
"There must be some kind of way out of here,
said the joker to the thief.
There's too much confusion,
I can't get no relief."
All along the Watchtower, Bob Dylan, and quite descriptive of my attempts to understand VBA.
I usually use the form =if(iserror(formula),,formula) to handle these problems, depending a little on the type of error, though it's often easier to trace if you can have an intermediate range validating the data and eliminating invalid references, #n/a's and the like.-John ... I float in liquid gardens