cells formatted to tick when text value "Y" if or null if "N"

Hi,

I'm trying to get a range of cells, in fact a column of cells, to be
formatted with a tick if the value contained within is Y. Unfortunately I
can't change the value to 0 or 1 and use simple custom formats as the the
information comes in from an xml file. This is further compounded by the
fact that I have some validation routines to check what has changed on a
reload of a similar xml file.

Hence, I need to find a way of formatting a cell so that it displays a tick
or nothing whilst maintaining the underlying string value Y or N. It seems
that I might have to write a function to interpret Y/N to 1 and 0 in any
case, followed by a custom format on the cells to display the appropriate
tick string. I'd rather not do this as I know it will give me a serious
performance hit on the xml upload routines.

I'm wondering if there is anyone there who can help me round this one.

Regards

Jason

How about just using Conditional Formatting to put a cross-hatch pattern
behind the Y, or put a colored border around the cell, etc?

Vaya con Dios,
Chuck, CABGx3




"Jay" wrote:

> Hi,
>
> I'm trying to get a range of cells, in fact a column of cells, to be
> formatted with a tick if the value contained within is Y. Unfortunately I
> can't change the value to 0 or 1 and use simple custom formats as the the
> information comes in from an xml file. This is further compounded by the
> fact that I have some validation routines to check what has changed on a
> reload of a similar xml file.
>
> Hence, I need to find a way of formatting a cell so that it displays a tick
> or nothing whilst maintaining the underlying string value Y or N. It seems
> that I might have to write a function to interpret Y/N to 1 and 0 in any
> case, followed by a custom format on the cells to display the appropriate
> tick string. I'd rather not do this as I know it will give me a serious
> performance hit on the xml upload routines.
>
> I'm wondering if there is anyone there who can help me round this one.
>
> Regards
>
> Jason

Can you use another cell?

=if(a2="y",char(252),"")
format it with a Wingdings font.



Jay wrote:
>
> Hi,
>
> I'm trying to get a range of cells, in fact a column of cells, to be
> formatted with a tick if the value contained within is Y. Unfortunately I
> can't change the value to 0 or 1 and use simple custom formats as the the
> information comes in from an xml file. This is further compounded by the
> fact that I have some validation routines to check what has changed on a
> reload of a similar xml file.
>
> Hence, I need to find a way of formatting a cell so that it displays a tick
> or nothing whilst maintaining the underlying string value Y or N. It seems
> that I might have to write a function to interpret Y/N to 1 and 0 in any
> case, followed by a custom format on the cells to display the appropriate
> tick string. I'd rather not do this as I know it will give me a serious
> performance hit on the xml upload routines.
>
> I'm wondering if there is anyone there who can help me round this one.
>
> Regards
>
> Jason

--

Dave Peterson

Hi Dave,

Thanks for the response, I actually stangle my application if I go for that
approach. The scripts I've written are all based on re-usable code that
allows me to reformat the workbook to parent header info in the style of a
form with lists of children and their properties. To add a further level of
complexity I then generate a dynamic matrix of grandchild items next to the
list. All of this is completely dynamic and I end up with a different no of
worksheets based on the number of objects in my xml file. The formatting is
all managed through a series of lookups and global parameters so I'd be
adding quite a bit of complexity going this way. So, although I looked at
that approach it doesn't really get me where I want to be.

About my best approach at the moment is to write a small inbound parser
function that turns my Ns into -1 and my Ys into 1. I can then set the font
to "a";""; Marlett where a = "Tick" and I have the desired result, although
I'm holding off doing this for performance reasons at the moment.

I'm really curious to know if it can be done without changing and recasting
my values, let me know if you have any other ideas

Best Regards

Jason Peel

"Dave Peterson" wrote:

> Can you use another cell?
>
> =if(a2="y",char(252),"")
> format it with a Wingdings font.
>
>
>
> Jay wrote:
> >
> > Hi,
> >
> > I'm trying to get a range of cells, in fact a column of cells, to be
> > formatted with a tick if the value contained within is Y. Unfortunately I
> > can't change the value to 0 or 1 and use simple custom formats as the the
> > information comes in from an xml file. This is further compounded by the
> > fact that I have some validation routines to check what has changed on a
> > reload of a similar xml file.
> >
> > Hence, I need to find a way of formatting a cell so that it displays a tick
> > or nothing whilst maintaining the underlying string value Y or N. It seems
> > that I might have to write a function to interpret Y/N to 1 and 0 in any
> > case, followed by a custom format on the cells to display the appropriate
> > tick string. I'd rather not do this as I know it will give me a serious
> > performance hit on the xml upload routines.
> >
> > I'm wondering if there is anyone there who can help me round this one.
> >
> > Regards
> >
> > Jason
>
> --
>
> Dave Peterson
>

Hi Vaya,

Thanks for the response, I looked at this route, but the tick is the
desirable target. I'm really curious to know if it can be done at this
stage. Take a look at my response to Dave Paterson for my nearest solution.

Best Regards

Jason Peel

"CLR" wrote:

> How about just using Conditional Formatting to put a cross-hatch pattern
> behind the Y, or put a colored border around the cell, etc?
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
>
>
> "Jay" wrote:
>
> > Hi,
> >
> > I'm trying to get a range of cells, in fact a column of cells, to be
> > formatted with a tick if the value contained within is Y. Unfortunately I
> > can't change the value to 0 or 1 and use simple custom formats as the the
> > information comes in from an xml file. This is further compounded by the
> > fact that I have some validation routines to check what has changed on a
> > reload of a similar xml file.
> >
> > Hence, I need to find a way of formatting a cell so that it displays a tick
> > or nothing whilst maintaining the underlying string value Y or N. It seems
> > that I might have to write a function to interpret Y/N to 1 and 0 in any
> > case, followed by a custom format on the cells to display the appropriate
> > tick string. I'd rather not do this as I know it will give me a serious
> > performance hit on the xml upload routines.
> >
> > I'm wondering if there is anyone there who can help me round this one.
> >
> > Regards
> >
> > Jason

If the choice were between empty and a "Y", then I think you could use a number
format.

But I don't know a way of keeping the value (Y/N) and showing the tickmark.

Jay wrote:
>
> Hi Dave,
>
> Thanks for the response, I actually stangle my application if I go for that
> approach. The scripts I've written are all based on re-usable code that
> allows me to reformat the workbook to parent header info in the style of a
> form with lists of children and their properties. To add a further level of
> complexity I then generate a dynamic matrix of grandchild items next to the
> list. All of this is completely dynamic and I end up with a different no of
> worksheets based on the number of objects in my xml file. The formatting is
> all managed through a series of lookups and global parameters so I'd be
> adding quite a bit of complexity going this way. So, although I looked at
> that approach it doesn't really get me where I want to be.
>
> About my best approach at the moment is to write a small inbound parser
> function that turns my Ns into -1 and my Ys into 1. I can then set the font
> to "a";""; Marlett where a = "Tick" and I have the desired result, although
> I'm holding off doing this for performance reasons at the moment.
>
> I'm really curious to know if it can be done without changing and recasting
> my values, let me know if you have any other ideas
>
> Best Regards
>
> Jason Peel
>
> "Dave Peterson" wrote:
>
> > Can you use another cell?
> >
> > =if(a2="y",char(252),"")
> > format it with a Wingdings font.
> >
> >
> >
> > Jay wrote:
> > >
> > > Hi,
> > >
> > > I'm trying to get a range of cells, in fact a column of cells, to be
> > > formatted with a tick if the value contained within is Y. Unfortunately I
> > > can't change the value to 0 or 1 and use simple custom formats as the the
> > > information comes in from an xml file. This is further compounded by the
> > > fact that I have some validation routines to check what has changed on a
> > > reload of a similar xml file.
> > >
> > > Hence, I need to find a way of formatting a cell so that it displays a tick
> > > or nothing whilst maintaining the underlying string value Y or N. It seems
> > > that I might have to write a function to interpret Y/N to 1 and 0 in any
> > > case, followed by a custom format on the cells to display the appropriate
> > > tick string. I'd rather not do this as I know it will give me a serious
> > > performance hit on the xml upload routines.
> > >
> > > I'm wondering if there is anyone there who can help me round this one.
> > >
> > > Regards
> > >
> > > Jason
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Agreed............I was toying with a small drawing object in each cell, but
ran in to the same problem.............

Vaya con Dios,
Chuck, CABGx3


"Dave Peterson" wrote:

> If the choice were between empty and a "Y", then I think you could use a number
> format.
>
> But I don't know a way of keeping the value (Y/N) and showing the tickmark.
>
> Jay wrote:
> >
> > Hi Dave,
> >
> > Thanks for the response, I actually stangle my application if I go for that
> > approach. The scripts I've written are all based on re-usable code that
> > allows me to reformat the workbook to parent header info in the style of a
> > form with lists of children and their properties. To add a further level of
> > complexity I then generate a dynamic matrix of grandchild items next to the
> > list. All of this is completely dynamic and I end up with a different no of
> > worksheets based on the number of objects in my xml file. The formatting is
> > all managed through a series of lookups and global parameters so I'd be
> > adding quite a bit of complexity going this way. So, although I looked at
> > that approach it doesn't really get me where I want to be.
> >
> > About my best approach at the moment is to write a small inbound parser
> > function that turns my Ns into -1 and my Ys into 1. I can then set the font
> > to "a";""; Marlett where a = "Tick" and I have the desired result, although
> > I'm holding off doing this for performance reasons at the moment.
> >
> > I'm really curious to know if it can be done without changing and recasting
> > my values, let me know if you have any other ideas
> >
> > Best Regards
> >
> > Jason Peel
> >
> > "Dave Peterson" wrote:
> >
> > > Can you use another cell?
> > >
> > > =if(a2="y",char(252),"")
> > > format it with a Wingdings font.
> > >
> > >
> > >
> > > Jay wrote:
> > > >
> > > > Hi,
> > > >
> > > > I'm trying to get a range of cells, in fact a column of cells, to be
> > > > formatted with a tick if the value contained within is Y. Unfortunately I
> > > > can't change the value to 0 or 1 and use simple custom formats as the the
> > > > information comes in from an xml file. This is further compounded by the
> > > > fact that I have some validation routines to check what has changed on a
> > > > reload of a similar xml file.
> > > >
> > > > Hence, I need to find a way of formatting a cell so that it displays a tick
> > > > or nothing whilst maintaining the underlying string value Y or N. It seems
> > > > that I might have to write a function to interpret Y/N to 1 and 0 in any
> > > > case, followed by a custom format on the cells to display the appropriate
> > > > tick string. I'd rather not do this as I know it will give me a serious
> > > > performance hit on the xml upload routines.
> > > >
> > > > I'm wondering if there is anyone there who can help me round this one.
> > > >
> > > > Regards
> > > >
> > > > Jason
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Thanks Guys,

I'm pretty happy that there's no obvious/simple way to do this without
changing the data which is actually what I needed to know (I can sleep well,
as it were).

Again, thanks for the input,

Jason Peel

"CLR" wrote:

> Agreed............I was toying with a small drawing object in each cell, but
> ran in to the same problem.............
>
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Dave Peterson" wrote:
>
> > If the choice were between empty and a "Y", then I think you could use a number
> > format.
> >
> > But I don't know a way of keeping the value (Y/N) and showing the tickmark.
> >
> > Jay wrote:
> > >
> > > Hi Dave,
> > >
> > > Thanks for the response, I actually stangle my application if I go for that
> > > approach. The scripts I've written are all based on re-usable code that
> > > allows me to reformat the workbook to parent header info in the style of a
> > > form with lists of children and their properties. To add a further level of
> > > complexity I then generate a dynamic matrix of grandchild items next to the
> > > list. All of this is completely dynamic and I end up with a different no of
> > > worksheets based on the number of objects in my xml file. The formatting is
> > > all managed through a series of lookups and global parameters so I'd be
> > > adding quite a bit of complexity going this way. So, although I looked at
> > > that approach it doesn't really get me where I want to be.
> > >
> > > About my best approach at the moment is to write a small inbound parser
> > > function that turns my Ns into -1 and my Ys into 1. I can then set the font
> > > to "a";""; Marlett where a = "Tick" and I have the desired result, although
> > > I'm holding off doing this for performance reasons at the moment.
> > >
> > > I'm really curious to know if it can be done without changing and recasting
> > > my values, let me know if you have any other ideas
> > >
> > > Best Regards
> > >
> > > Jason Peel
> > >
> > > "Dave Peterson" wrote:
> > >
> > > > Can you use another cell?
> > > >
> > > > =if(a2="y",char(252),"")
> > > > format it with a Wingdings font.
> > > >
> > > >
> > > >
> > > > Jay wrote:
> > > > >
> > > > > Hi,
> > > > >
> > > > > I'm trying to get a range of cells, in fact a column of cells, to be
> > > > > formatted with a tick if the value contained within is Y. Unfortunately I
> > > > > can't change the value to 0 or 1 and use simple custom formats as the the
> > > > > information comes in from an xml file. This is further compounded by the
> > > > > fact that I have some validation routines to check what has changed on a
> > > > > reload of a similar xml file.
> > > > >
> > > > > Hence, I need to find a way of formatting a cell so that it displays a tick
> > > > > or nothing whilst maintaining the underlying string value Y or N. It seems
> > > > > that I might have to write a function to interpret Y/N to 1 and 0 in any
> > > > > case, followed by a custom format on the cells to display the appropriate
> > > > > tick string. I'd rather not do this as I know it will give me a serious
> > > > > performance hit on the xml upload routines.
> > > > >
> > > > > I'm wondering if there is anyone there who can help me round this one.
> > > > >
> > > > > Regards
> > > > >
> > > > > Jason
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> > > >
> >
> > --
> >
> > Dave Peterson
> >