Solved: Problem With Choose & Match

[zoom38]

I have a workbook with 12 sheets named "Jan 2007" thru "Dec 2007". On the 13th sheet "2007 Totals" I am attempting to add up the sheet amounts based on the month chosen in cell L3 but in most of the calculations the amounts from the "Jan 2007" sheet are not included. As far as I can tell the formulas are correct. If you take a look at the attached file you will see my problem.

On the "2007 Totals" sheet in cell c9 thru c13 the totals are correct. The rest of the cells on the sheet do not include "Jan 2007" results.

Thanks
Gary

[Bob Phillips]

I cannot see the problem, Jan always includs as far as I can see.

I have an alternative approach though, see attached. Couple of things, June and July did not follow the sheet naming tab convention.

[zoom38]

Thanks XL for checking it out.

I did know about the naming difference but that doesn't seem to be the problem. If you look in cell G6 which has the total value, the following should be true.

Sum of C9 thru C13 should equal the value in G6.
Sum of C17 & C18 should equal the value in G6.
Sum of C22 thru C28 should equal the value in G6.
Sum of C32 thru C36 should equal the value in G6.
Sum of K7 thru K21 should equal the value in G6.
Sum of J27 thru J40 + L25 + M26 thru M40 should equal the value in G6.

Example of the problem:

If you select February in cell L3 on "2007 Totals" the total # in cell G6=22 which is the correct amount.

Sum of C9 thru C13 is 22 which is correct.

Sum of C17 & C18 is 21 which is incorrect. Should be 22. It is not adding the value of C17 from "Jan 2007".

Sum of C22 thru C28 is 13 which is incorrect. Should be 22. It is not adding the values of C22 thru C28 from "Jan 2007".

Sum of C32 thru C36 is 13 which is incorrect. Should be 22. It is not adding the values of C32 thru C36 from "Jan 2007".

Sum of K7 thru K21 is 13 which is incorrect. Should be 22. It is not adding the values of K7 thru K21 from "Jan 2007".

I don't know what is happening is the last block. Nothing adds up correctly.

I've checked and re-checked the formulas which appear to me to all be correct. I tried entering them in as array formulas which has no effect either. I don't know whats happening. I hope I cleared up any confusion.

Thanks
Gary

[Shazam]

Hi zoom38


The reason why its not adding up correctly because in worksheet "Jan 2007" in cell C17 is formated as text. Try formating the cell as general and double click inside the cell and hit enter. Then you'll get the 22 result as expected.

[unmarkedhelicopter]

Each of the month reports are slightly different, apart from Jan being text.
When you want a new sheet copy an old one, do any changes (applicable to all sheets) to ALL sheets simultaneously and you should never have this problem.

[zoom38]

Hi zoom38


The reason why its not adding up correctly because in worksheet "Jan 2007" in cell C17 is formated as text. Try formating the cell as general and double click inside the cell and hit enter. Then you'll get the 22 result as expected.

Shazaam Did you try it? I already formated everything to General but still no luck. UMH the sheets were copied from an original. The reason they appear different is because I had to remove some info from each sheet before posting.

[zoom38]

Well I started over from scratch and it works not. I do believe that the text format caused the problem but I don't understand why it would not work when the cells were formatted to general.
Thanks for looking into it for me.

Gary