I have a spreadsheet that get used in a number of countries.
Any I aim to keep the amount of VBA to a minimum.

There is current year cost data sorted via a pivot table into category totals.
Then the user is given the option to use this data in next years forecast.
So I would like to have a "TRUE or FALSE" value in a field from which I can
then manipulate the pivottable data.

The advantage of using true or false is that it changes language with each
user to their default.
Trouble is that I would like to make it a bit more friendly by using data
validation.
Each language's "Yes or No" can be got from the VBA XL constants for yes and
no stored in named cells in a hidden worksheet each time the spreadsheet is
opened. The issue is to show these named text values in the Validation list
but store TRUE or FALSE in the cell.

Can this be done?

You could use programming to change the selected item to true or false.
There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Chris_UK wrote:
I have a spreadsheet that get used in a number of countries.
Any I aim to keep the amount of VBA to a minimum.

There is current year cost data sorted via a pivot table into category totals.
Then the user is given the option to use this data in next years forecast.
So I would like to have a "TRUE or FALSE" value in a field from which I can
then manipulate the pivottable data.

The advantage of using true or false is that it changes language with each
user to their default.
Trouble is that I would like to make it a bit more friendly by using data
validation.
Each language's "Yes or No" can be got from the VBA XL constants for yes and
no stored in named cells in a hidden worksheet each time the spreadsheet is
opened. The issue is to show these named text values in the Validation list
but store TRUE or FALSE in the cell.

Can this be done?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra
Thanks, certainly a good approach.
I was hoping to format a cell containing a true or false to show something
else (in this case yes or no). Thus the user would see something they can
easily understand and I could make cell formulas do the work (less
maintenance/update overhead!). Unless my memory is playing tricks I thought
that an earlier version of Excel (Excel97?) allowed you to format cells
containing logicals. Hey my memory maybe playing tricks and I need to
increase my intake of fish oils!

Regards

Chris_UK

"Debra Dalgleish" wrote:

You could use programming to change the selected item to true or false.
There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Chris_UK wrote:
I have a spreadsheet that get used in a number of countries.
Any I aim to keep the amount of VBA to a minimum.

There is current year cost data sorted via a pivot table into category totals.
Then the user is given the option to use this data in next years forecast.
So I would like to have a "TRUE or FALSE" value in a field from which I can
then manipulate the pivottable data.

The advantage of using true or false is that it changes language with each
user to their default.
Trouble is that I would like to make it a bit more friendly by using data
validation.
Each language's "Yes or No" can be got from the VBA XL constants for yes and
no stored in named cells in a hidden worksheet each time the spreadsheet is
opened. The issue is to show these named text values in the Validation list
but store TRUE or FALSE in the cell.

Can this be done?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Perhaps you could use 1 and 0 instead of TRUE and FALSE, then use a
custom number format to display the related words.

Chris_UK wrote:
Debra
Thanks, certainly a good approach.
I was hoping to format a cell containing a true or false to show something
else (in this case yes or no). Thus the user would see something they can
easily understand and I could make cell formulas do the work (less
maintenance/update overhead!). Unless my memory is playing tricks I thought
that an earlier version of Excel (Excel97?) allowed you to format cells
containing logicals. Hey my memory maybe playing tricks and I need to
increase my intake of fish oils!

Regards

Chris_UK

"Debra Dalgleish" wrote:


You could use programming to change the selected item to true or false.
There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Chris_UK wrote:

I have a spreadsheet that get used in a number of countries.
Any I aim to keep the amount of VBA to a minimum.

There is current year cost data sorted via a pivot table into category totals.
Then the user is given the option to use this data in next years forecast.
So I would like to have a "TRUE or FALSE" value in a field from which I can
then manipulate the pivottable data.

The advantage of using true or false is that it changes language with each
user to their default.
Trouble is that I would like to make it a bit more friendly by using data
validation.
Each language's "Yes or No" can be got from the VBA XL constants for yes and
no stored in named cells in a hidden worksheet each time the spreadsheet is
opened. The issue is to show these named text values in the Validation list
but store TRUE or FALSE in the cell.

Can this be done?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Debra
Then we are back to some VBA to get the right words for the custom format,
very do-able however not as neat as I had hoped. Thanks again

"Debra Dalgleish" wrote:

Perhaps you could use 1 and 0 instead of TRUE and FALSE, then use a
custom number format to display the related words.

Chris_UK wrote:
Debra
Thanks, certainly a good approach.
I was hoping to format a cell containing a true or false to show something
else (in this case yes or no). Thus the user would see something they can
easily understand and I could make cell formulas do the work (less
maintenance/update overhead!). Unless my memory is playing tricks I thought
that an earlier version of Excel (Excel97?) allowed you to format cells
containing logicals. Hey my memory maybe playing tricks and I need to
increase my intake of fish oils!

Regards

Chris_UK

"Debra Dalgleish" wrote:


You could use programming to change the selected item to true or false.
There's an example he

http://www.contextures.com/excelfiles.html

Under Data Validation, look for 'DV0004 - Data Validation Change'

Chris_UK wrote:

I have a spreadsheet that get used in a number of countries.
Any I aim to keep the amount of VBA to a minimum.

There is current year cost data sorted via a pivot table into category totals.
Then the user is given the option to use this data in next years forecast.
So I would like to have a "TRUE or FALSE" value in a field from which I can
then manipulate the pivottable data.

The advantage of using true or false is that it changes language with each
user to their default.
Trouble is that I would like to make it a bit more friendly by using data
validation.
Each language's "Yes or No" can be got from the VBA XL constants for yes and
no stored in named cells in a hidden worksheet each time the spreadsheet is
opened. The issue is to show these named text values in the Validation list
but store TRUE or FALSE in the cell.

Can this be done?


--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html