Translate yes or no to digit and summarize in YTD sheet

[rizmomin]

Hello:

I have excel workwook with Month sheet Jan,Feb,Mar....Dec and YTD

In each monthly sheet i will have either "yes"or "No" in cell B66:K75.

Below is what i need to do in YTD sheet cell B66:K75.
Each cell in TYD B66:K75 will have 12 digit number

Need to go thru each monthly sheet for each of the above cell and find if Yes or No and translate that into number (yes = 1, no = 0)

Example:

If B66 in Jan, Feb and Mar is Yes and B66 in Apr thru Dec is No then the number in YTD in cell B66=111000000000



Let me Know if you have any questions.
Thanks.
Riz

[alansidman]

For the first part of your request, here is some untested code, as you did not provide a sample worksheet:

Please Login or Register  to view this content.

I believe that you can do a simple concatenate in the YTD sheet and copy it down and over. Really no need for any VBA as it is a simple formula to be copied

[GC Excel]

Hi,
Here are 2 proposal :

With a formula. Example in B66 = YTD(B66)
(note that it does not refresh automatically)

Please Login or Register  to view this content.

Or with a sub
(Adjust range / sheet names are necessary)

Please Login or Register  to view this content.

[rizmomin]

Hello GC Excel:

It looks like YTD sub will work.
Let me test and i will let you know if any issues.
Thanks a lot for your great help.
Riz

[rizmomin]

Hello:

It seems to work great, except when I run the code the number's have small triangle for error.
I would like to convert the range B66: K75 in number and IGNORE ERROR.
Please refer to attached pic
Is this possible.
Thanks
Riz

[rizmomin]

Hello GC Excel:

I got it by adding below code:

Please Login or Register  to view this content.

Once again I thank you for great help.

Riz