Green Triangles in Tables - how to fix?

[gooniegirl180]

Hi folks, I have a number of tables that are used to create a number of different reports. I use a helper field in some of them to select and order which products are reported on - here is a sample of one of the formulas. This one is in row 10 and is copied down the column so that the cell references are not counting anything before the current row:

=IF(SUMIF([Sales Forecast Code],[@[Sales Forecast Code]],[Current Year Forecast])+SUMIF([Sales Forecast Code],[@[Sales Forecast Code]],[Current Year Actual])=0,"",IF([@[Item Group]]<>Selected_Product_Type,"",IF(COUNTIF($D$9:D10,D10)>1,"",MAX($T$8:T9)+1)))

where column D is the Sales Forecast Code and column T is the column this formula is in. The first row of data in the table is in row 9.

This basically weeds out any product that doesn't have any forecast or actual usage, and returns an index number (or a counter, if you like) for each unique Sales Forecast Code which I use to generate a report based on whatever Product Type a user selects. This means the index numbers change depending on the user's selection.

Because this formula is in a table, sometimes I get those pesky little green triangles because the COUNTIF and MAX cell references get themselves out of alignment. I do need those green triangles to tell me when it happens, so I don't want to eliminate them showing - I want to eliminate them occurring in the first place.

I suspect there is a more stable formula I could use to do the same thing, but I'm not knowledgeable enough to know what that is. Any help would be great, because at this point, I'm going to have to tell my users to fix any that come up, and I'd rather not do that.

Thanks,
GoonieGirl180

 

[Jeffrey Mahoney]

You could an IF statement in that formula like this:
IF(Row([Sales Forecast Code])>9,<Your regular formula here>,"")
Add this to all the rows in your helper column. The formula will be the same, therefor the green triangles should disappear.

 

[gooniegirl180]

You could an IF statement in that formula like this:
IF(Row([Sales Forecast Code])>9,<Your regular formula here>,"")
Add this to all the rows in your helper column. The formula will be the same, therefor the green triangles should disappear.

I'm not understanding how this prevents the green triangles from appearing? Wouldn't it be better to be using structured references in place of the cell references in the COUNTIF and MAX parts of the formula?

Thanks,
GoonieGirl180

 

[Jeffrey Mahoney]

I assumed that the green triangles were appearing because you didn't have a formula filling the entire column. I was attempting to provide a formula that could be used in the entire column, but no show data in rows less than 10

 

[gooniegirl180]

I assumed that the green triangles were appearing because you didn't have a formula filling the entire column. I was attempting to provide a formula that could be used in the entire column, but no show data in rows less than 10

Ah. That's not the case. The formula is appearing in all rows of the column. The formula is trying to result in a index number for the first instance of each sales forecast code for a selected Item Group, which is then used as a lookup for a report on another sheet.

What's happening is that the formula is contained within a table, but the COUNTIF and MAX functions are using cell references instead of structured references. For some reason I'm not advanced enough to understand, it's causing instability in those ranges and they sometimes become misaligned. When this happens (which is not all the time, but at the same time is not infrequent) I get the warning green triangles. These are important because they're telling me when the formulae have misaligned and then I can fix them. The problem is, I have to hand this spreadsheet over to other users who aren't technically minded so I need to find a way to get the same result with structured references to prevent them occurring in the first place.

Here's an example of what I'm trying to achieve:

Selected Item Group = A <==The user chooses this based on the Item group they want to report on

Item Group Sales Forecast Code Index # (Index # if the Selected Item Group was B)
B ABC123 1
A XYZ123 1
A XYZ123
A XYZ999 2
B ABC999 2
B ABC123
A JKL123 3
A XYZ999

I use the cell references so that I can look at the rows above and see what the count is, then I can add 1 to that if the Sales forecast code hasn't been listed before this row and the Item group on this row is the same as the Selected Item group.

Basically, how do you create a counter for rows in a table?

Thanks,
GoonieGirl180