show non-blank as 1
 

Hi All

I'm running a crosstab query in access, importing into excel and using it to
show on another sheet if a step is used or not. The crosstab query counts
the number of times the step is used. Example, my first yes shows 8. Here's
the formula working on the second sheet.

=VLOOKUP(A12,CapModXtab!$A$3:$I$280,3,TRUE)

It returns the value, 8 for my trues, blank for false. I'd like the 8's to
show up as 1. Is there a way to add that into my formula or format the cells
to do this.

I was able to change the cell format to yes or no. That would work for me,
but people are used to seeing "1" meaning yes we use that step.

Thanks!
Barbara

Assuming you want the blanks returned as zero:

=MIN(1,VLOOKUP(A12,CAPModXTab!$A$3:$I$280,3,TRUE))

spottkitty wrote:
Hi All

I'm running a crosstab query in access, importing into excel and using it to
show on another sheet if a step is used or not. The crosstab query counts
the number of times the step is used. Example, my first yes shows 8. Here's
the formula working on the second sheet.

=VLOOKUP(A12,CapModXtab!$A$3:$I$280,3,TRUE)

It returns the value, 8 for my trues, blank for false. I'd like the 8's to
show up as 1. Is there a way to add that into my formula or format the cells
to do this.

I was able to change the cell format to yes or no. That would work for me,
but people are used to seeing "1" meaning yes we use that step.

Thanks!
Barbara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

I'd like the blanks to stay blank, otherwise they'll be counted in a pivot
table. Very close. This works for the fields that have numbers, the blank
fields are returning a #value error.

Thank you

"Debra Dalgleish" wrote:

Assuming you want the blanks returned as zero:

=MIN(1,VLOOKUP(A12,CAPModXTab!$A$3:$I$280,3,TRUE))

spottkitty wrote:
Hi All

I'm running a crosstab query in access, importing into excel and using it to
show on another sheet if a step is used or not. The crosstab query counts
the number of times the step is used. Example, my first yes shows 8. Here's
the formula working on the second sheet.

=VLOOKUP(A12,CapModXtab!$A$3:$I$280,3,TRUE)

It returns the value, 8 for my trues, blank for false. I'd like the 8's to
show up as 1. Is there a way to add that into my formula or format the cells
to do this.

I was able to change the cell format to yes or no. That would work for me,
but people are used to seeing "1" meaning yes we use that step.

Thanks!
Barbara


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

=IF(your_formula=8,1,"")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------*------------------------------*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------*------------------------------*----------------


"spottkitty" wrote in message
...
Hi All

I'm running a crosstab query in access, importing into excel and using it
to
show on another sheet if a step is used or not. The crosstab query counts
the number of times the step is used. Example, my first yes shows 8.
Here's
the formula working on the second sheet.

=VLOOKUP(A12,CapModXtab!$A$3:$I$280,3,TRUE)

It returns the value, 8 for my trues, blank for false. I'd like the 8's
to
show up as 1. Is there a way to add that into my formula or format the
cells
to do this.

I was able to change the cell format to yes or no. That would work for
me,
but people are used to seeing "1" meaning yes we use that step.

Thanks!
Barbara

Thank you Debra and Ken ~

Once I got the format right, both formats seem to work. I think the IF
statement is the better option. On initial run, worked great.

New SLIGHT problem. :( My reference table needs to be updatable. I
updated it and a step was removed from the reference list. It didn't change
to a 0 on the other sheet.

=IF(VLOOKUP(A784,CapModXtab!$A:$I,3,TRUE)=1,1,0)

That's the formula. I had the reference table as a query pointing back to
the db so I could update it in excel. Didn't update when step changed.
Removed that and copied the reference table in, didn't change either.

It's probably basic, but what am I missing?!?!

Thanks much!!!
Barbara

"Ken Wright" wrote:

=IF(your_formula=8,1,"")

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03