IF A1 = Yes, then add 1 to B1

[ThanksAlot]

Hello, and thank you for looking.

I wish to add 1 more to B1, if A1 = "yes."

If A1 = "no," then B1 stays the same.

The cells of Column A are updated everyday to "Yes" or "No." Column B cells only change if cell A = "Yes."

Thanks, Jon

[:) Sixthsense :)]

Please describe whether this event should happen only if any change in A1 cell alone Or the code should run on any cell in Column-A if there is any change?

[HaHoBe]

Hi, Jon ,

how is updated: by formula, by manual change directly to the cell, by Query?

Ciao,
Holger

[AlvaroSiza]

A code example (though a boolean formula would suffice)

Please Login or Register  to view this content.

[HaHoBe]

Hi, AlvaroSiza,

any time the cursor is placed in A1 the update would take place - let´s see if that´s what Jon is after.

Ciao,
Holger

[ThanksAlot]

SixthSense and Holger, thank you for your assistance. But, now, I have been smoked out.

I tried to keep the scenario simple, but it is rather complicated for me.

Column A is updated by A1=X1

Column X is the result of =IF(AX1>AO1,"YES","NO")

So, the formula that is under your consideration should run on all cells in Column A, including the "NO" cells.

I hope this answers your respective questions.

Thanks, Jon

[ThanksAlot]

Friends, I am a complete Excel knucklehead. I can only hope to learn this by the seat of my pants. What AlvaroSiza provided is absolutely, 100% over my head. Dim lngB As Long is some guy's name for all I know. Sorry.

[HaHoBe]

Hi, Jon,

what about the other two columns mentioned here: is data entered manually or a query? If entered manually we could use the Worksheet_Change-Event for these two columns to monitor any change and a possible update to Column B from there (leavoing Column A on it´s own). If it´s a formula there as well I´m lost as I would not know how to find a limitation to the update (I would think that any new calculation would trigger an event we need to augment the cells or set Calculation to manual and use that for an update).

Ciao,
Holger

[ThanksAlot]

Holger,

Column AX is entered manually (copy and paste).

Column AO is VLookup formula from another page.

Thanks.

[HaHoBe]

Hi, Jon,

I had hoped for a quick solution to your problem here but the Formulas in Column AO give me a hard time as they may deliver some other result when the data for the VLookUp is altered. If that data table isn´t altered we may rely solely on column AX and loop through all cells which are entered there in order to find out whether the criteria for an update in Column B is given or not.

Ciao,
Holger

[ThanksAlot]

Holger, the data table is changed every day. Today's Column AX is tomorrow's VLookup data table. I am going to show you a formula that was worked out by WEAZEL from another forum. Some of the same columns are referenced in his formula. I was thinking I could dissect his formula and glean something for the column in question. Hang onto your seat.

=IF(AND(AM2="Yes",AN2="Yes",Y2>=1,Y2<=20),Y2+1,IF(AND(AM2="Yes",AN2="Yes",Y2>=-10,Y2<=-1),1,IF(AND(AM2="Yes",AN2="No",Y2>=1,Y2<=20),-1,IF(AND(AM2="Yes",AN2="No",Y2>=-10,Y2<=-1), Y2+-1,IF(AND(AM2="No",AN2="No"),Y2,"")))))

[HaHoBe]

Hi, Jon,

would it be possible that you attach a sample workbook with some data and some formulas to have a look at?

Ciao,
Holger

[ThanksAlot]

Hi, Holger.

I have created a sample workbook that will show you exactly what I wish to do. Having difficulty uploading it. -Jon

[HaHoBe]

Hi, Jon,

maybe the description may help (if the workbook is not too big in size for the forum - I must admit I don´t know the limits for EF):

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

If the workbook is too big maybe use some free uploading service (but that would only be the second choice).

Ciao,
Holger

[ThanksAlot]

IF F2 = YES.zip I think this will work. It is compressed to work withing the size limits. Cheers!

Holger, the size limits are shown in the Question Mark above the upload button.

Thanks for taking a look. -Jon

[HaHoBe]

Hi, Jon,

as you noted in the sample there are 2 columns which are updated and deliver the foundation for any calculation we will take care of these and use them for an event to raise the values in other columns. Both Column L and M on Front page are monitored. Any manual change (including the pasting of new values) will trigger an event when macros are enabled. A check is fulfilled to see if the number of days has increased and if so the numbers in Column B and C according to the monitored columns as well as those to compare with will be augmented.

Code goes behind Sheet Front page (right click on the worksheet tab, chosse ViewCode, paste the code into the code window):

Please Login or Register  to view this content.

Ciao,
Holger

[ThanksAlot]

Thanks, Holger. I will try your solution, and let you know how it works. It will take me a few days to get back to you, but I will. I have never worked with code like this, so I'll be stumped until I get the hang of it. Cheers! -Jon