when using a pivot - the word "BLANK" transfers in when the data sheet has a blank area...
How can I get that to be just blank so that I can use that cell in a conditional formatting scenario?
I want to say...
=IF(Q8="blank")
if Q8 is blank, then turn red
if Q8 has a value, then turn green
Angela
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Chuck - that did it - THANKS!!
Angela
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
Angela: In the case of Excel 2003, what appears in the PivotTable is
(blank), and it appears to be treated as text. The conditional IF
((a1="(blank)", T, F) appears to work well.
Now the color....that I don't know how to do.
Chuck
You can also go back into the list from which your PivotTable was derived
and fill in all the blanks with N/A or a period or whatever character you'd
like. Steps:
1. Select an area of the spreadsheet that encompasses all the blanks.
2. Do an Edit>GoTo
3. Click on the Special button
4. Select the Blanks radio button and hit OK
5. Type in the replacement character.
6. Do a Ctrl-Enter to fill in all the blank cells.
Adapted from "Pivot Table Data Crunching" by Jelen and Alexander, p. 21.
Chuck
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks