Formula / Formatting issue

[Vicki Jensen]

Hi everyone and thank you in advance for your help. I've learned alot from this forum.

I believe my issue is formatting - but I don't really know. Please see the attached spreadsheet. It has 2 sheets, basically the second sheet is recatagorizing the first (GrossChgs & Sam Smith). December numbers will not pull into the second sheet. I believe the formula to be correct because a) it's a copy from Nov; b) if you change the Dec date on 'SamSmith' to Nov 2012, Nov data will pull.

Also, on the first sheet, if Sam Smith is keyed or copied into cell A6, the formulas on the second sheet go crazy.

I've look at this so long I believe its a case of cannot see the forest for the trees. Any help is appreciated.

Vicki

[Richard Buttrey]

Hi,

There is a reference to cell $N$1 in the column M formulae on Sam Smith - incidentally a well know brand of beer here in the North of England, (in fact in all the columns. Change these to be $O$1 so that all the columns from GrossChgs are included.

[dredwolf]

the maind problem appears to be these Matches in the formula:

MATCH(M$4,GrossChgs!$C$1:$N$1,0)

the range does not extend far enough to catch dec, which is in column O
so if you adjust them to this:

MATCH(M$4,GrossChgs!$C$1:$O$1,0)

the formula seems to work fine

Hope this helps

[Vicki Jensen]

I knew the answer would be would make me scratch my head. I was using the Named Range for "dateheader", but it only worked in part of the formula. When I took out the named range, that when I messed it up.

Thank you all very much for the assistance.

[dredwolf]

You are welcome

Please remember to mark the thread as solved if you are satisfied with your solution :
To mark thread "Solved", go to the top of the thread,click "Thread Tools",click "Mark as Solved"

[Vicki Jensen]

The second part of my thread is in reference to adding the name Sam Smith to cell A6 on GrossChgs sheet. The formula returns #VALUE! because the cells are blank in GrossChgs. What is wrong with my formula?
Thank you again.

[dredwolf]

the problem appears to be because not all of the cells in row 6 have values...as soon as I put zero's into the blank cells, the #VALUE error disappeared...
looking further into it

[dredwolf]

Okay, whats happening is the formula in row 6 on 'Sam Smith' is trying to add a number to a text value, so the #value error appears, I'm not sure how these values are arrived at, but you need to make sure there is a number in that cell,or that the formula returns a 0 if the cell is blank

[Vicki Jensen]

I don't want to go thru the data and fill all the "blanks" with 0. These cells don't actually show up as blank when you use Find & Select, Go To Special to find the blanks.

Shouldn't the ISNA portion of the formula return a 0?

[dredwolf]

They show up as "" when you evaluate the formula with the evaluate formula tool (row 6 does anyways)...and ISNA returns "TRUE" or "FALSE"...which is the decision part of the formula, the if true or if false part is returning "", you COULD wrap that part in a VALUE() function to return a valid number ...