Tables in Excel 2007 (SP-1) - Information Goes Blank

[AlanS]

I have a set of data that I am trying to turn into a table.

In column D I have a date amounts entered.
In column G I have amouts entered.

The Column Headings in Columns H - O are year amounts, eg. 2007, 2008, etc.

The equations in the body of H - O are IF statements that say that if the
Year in Col D = the Year in the Column Heading, then put in the amount from
Col. G., if not put in "". ( I have also tried putting a 0 in for the false
answer and get the same result.)

When I am in the Excel 2007 Non Table mode everything works fine. When I
try to convert to the Table mode, the values derived from the IF equations
all vanish.

The cells in Cols H - O all are blank and the totals line that I had above
the Data area turns to 0.

The IF statements are still there but its as if they were gone since nothing
shows up and the total have all turned to 0.

Any thoughts?

Thanks

 

[Jim Rech]

It seems that when you made your table into an official "table" the column
headings were turned into text by Excel. So 2007 became "2007". So your
formulas are comparing 2007 to "2007" and returning no match. I wasn't able
to turn the heading years back to true numbers. Excel is keeping them as
text. So I tried turning the column D years into text by preceding them
with an apostrophe and that worked as did the formulas.

--
Jim
|I have a set of data that I am trying to turn into a table.
|
| In column D I have a date amounts entered.
| In column G I have amouts entered.
|
| The Column Headings in Columns H - O are year amounts, eg. 2007, 2008,
etc.
|
| The equations in the body of H - O are IF statements that say that if the
| Year in Col D = the Year in the Column Heading, then put in the amount
from
| Col. G., if not put in "". ( I have also tried putting a 0 in for the
false
| answer and get the same result.)
|
| When I am in the Excel 2007 Non Table mode everything works fine. When I
| try to convert to the Table mode, the values derived from the IF equations
| all vanish.
|
| The cells in Cols H - O all are blank and the totals line that I had above
| the Data area turns to 0.
|
| The IF statements are still there but its as if they were gone since
nothing
| shows up and the total have all turned to 0.
|
| Any thoughts?
|
| Thanks
| --
| Alan

 

[AlanS]

Makes sense. I didn't even think to check and see if the formatting had
changed. Somewhat disappointing that Excel doesn't keep the format the same.

Oh well. At least I understand what happened. Really aprreciate your help.

 

[AlanS]

I figured out a way around the problem. In the IF statement when I refer to
the column heading, which is now a Year but in Text format, I changed the
equation to read the Value(of Col Heading).

For example =IF(YEAR($D8)=VALUE(J$6),$G8,"")
That cause the items to both be values.

Once again Jim, thanks for your help.

 

[Jim Rech]

Better solution than mine!

--
Jim

I figured out a way around the problem. In the IF statement when I refer
to
the column heading, which is now a Year but in Text format, I changed the
equation to read the Value(of Col Heading).

For example =IF(YEAR($D8)=VALUE(J$6),$G8,"")
That cause the items to both be values.

Once again Jim, thanks for your help.