Re: If Formula To Test For 3 Values
I'll e-mail it to you if you PM me your address or send an e-mail to my address with OzGrid in the subject so it won't get discarded.
Regards,
Re: If Formula To Test For 3 Values
I'll e-mail it to you if you PM me your address or send an e-mail to my address with OzGrid in the subject so it won't get discarded.
Regards,
Re: Inscribing Cells
You are welcome. Glad to hear it worked for you.
Re: Inserting Cell Values Into A Textbox
Glad it worked for you.
Regards,
Re: Inscribing Cells
Thanks for the improved description. I think I better understand what you are trying to do. Could you place your inscription in a comment? Then choose None on the Tools>Options>View tab.
I'm off to the dentist right now but will check back later.
Regards,
Re: Code Purpose Unknown
The code you have posted looks like it may have come from the macro recorder. The code itself is entering data in some cells, looking up a value in another sheet, multiplying a number, and some copy, pasting and filling.
The RC[] business is one of VBA's methods to reference cells. The R1C1 method refers to cells by a row and a column number. The A1 method refers to cells by a row number and a column letter. It is common to use the R1C1 notation in coding as it is easy to "move" about a spreadsheet in this fashion. The negative numbers after the C represent movement to the left (positives are movement to the right) from the currently active cell. Movement up (negative) and down (positive) is done via the number after the R.
Hope this helps you understand the code better.
Regards,
Re: Inscribing Cells
Quote from keekooceeaouThanks for answers, but unfortunately it won't help.
User can copy or move rows that contain those cells, so Your solutions won't work in this case.
First off, setting the input message of a cell's data validation displays that message when the cell is selected. Therefore it doesn't appear that using that method would be covert with regard to your user.
Second, data validation on a given cell will go along with the cell in a standard copy/paste operation.
Third, a named range where the definition is a given list of cells will not amend itself if a cell is copied and pasted elsewhere. For example, MyRange =Sheet1!$C$3:$E$7,Sheet1!$G$3,Sheet1!$I$3,Sheet1!$K$3
The following code will check to see if the active cell is in this range:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("MyRange")) Is Nothing Then Exit Sub
'Replace message box with your desired code
MsgBox ("Insert code to do what ever you want to this inscribed cell")
End Sub
If I have misunderstood what your desired outcome is, please post a more detailed description.
Regards,
Re: Inscribing Cells
You could use a named range and then check to see if the active cell intersects with the named range.
HTH,
Re: Special Day Of Celebration
Congratulations Rich (and Mrs. Rich)!!
Looks like the couple celebrating 72 years has raised the bar for all of us young 'uns (you are included in this case as well Rich ).
Well, this year (on 07/01) my wife and I will celebrate 18 years together. 3 sons, 2 careers, 20 moves (my wife's tracking skills seem to be pretty good too!), grandkids are still a way off. Seems that we've been blessed many times over as well.
Keeping the faith,
Re: If Formula To Test For 3 Values
I found what I had done previously, then redid it a bit anyway. No Credit/Debit column in there yet. The explanations tell what is being done, it is up to you to determine if this is what SHOULD be happening. Change the notes for any incorrect results column to what SHOULD be happening and I can make the formula do what is needed for you.
If you need/want explanations of how the formulas work when all is said and done, I'll be happy to do that as well.
Sorry for the long delay.
Regards,
Re: If Formula To Test For 3 Values
Don't know what I did to the "right one", so I'll redo it and post back tomorrow sometime. Really this time. I got sidetracked a bit with a broken tooth / eventual extraction and haven't been much good to anyone lately.
Regards,
Re: Inserting Cell Values Into A Textbox
You could try this:
Quote from From the Tips and Tricks page on the main siteLinked Picture
A good alternative to a textbox or any shape is a linked picture that reflects any changes made to its reference. To make one, copy your cell(s), select the destination cell and holding down your Shift key go to Edit<Paste Picture Link.
Regards,
Re: Loop Stops Early
Kigol,
I've given your most recent code a look and a test run. I'm not seeing what is going haywire. Charts are really not my bag of tricks.
I'm sorry I'm not getting you where you need to go.
Regards,
Re: Soccer Game
You can try this out if you simply want one to use. If your interest is to learn how to make your own, then hang in there, give it a whirl and post questions as you go along.
HTH,
Re: If Formula To Test For 3 Values
Don't know what happened, the file I attached is obviously not the right one. I'll take a look tomorrow. Sorry.
Regards,
Re: And Formula To Include 0
Quote from shgDisplay MoreIf I may disagree with Brandtrock,
AND(B3>C3,D3,E3,F3)
does perform a logical test on D3, E3, and F3. It is equivalent to
AND (B3>C3, D3<>0, E3<>0, F3<>0)
Regards,
My comment was based on using the formula evaluator in the Tools>Audit menu, which steps through B2 (inserts cell value) and C3 (inserts cell value) and the greater than logical (inserts TRUE or FALSE), then returns the answer without showing any additional consideration of D3, E3, or F3.
shg is correct in that AND(D3, E3, F3 does return TRUE when the cells all contain a value other than zero, including text or nothing. Glad to learn that little tidbit.
Regards,
Re: And Formula To Include 0
The IF formula's construction is:
IF(Logical test, condition if true, condition if false)
I am sure you are already aware of that, just included it in case someone unfamiliar with that fact happens along this thread.
Your logical test, originally, was:
AND(B3>C3,D3,E3,F3)
The AND function will return TRUE (and fire your condition if true) when ALL of the arguments evaluate to TRUE. As written above, you test for B3 being greater than C3 (will return TRUE or FALSE depending on the values in the cells). The next three arguments are simply cell references; no logical comparison is conducted when entered this way so the AND will return TRUE.
Three members have offered the correct solution for performing the checks with AND on multiple cells. If you want to use the OR function instead, it behaves differently.
If ANY argument is TRUE then the OR function will return TRUE. This has been offered as well.
In either the case of the AND function or the OR function, you can use the logical comparison >= to check if B3 is Greater than or equal to the target cell. This may give you the desired control over how a zero is evaluated.
HTH
Re: If Formula To Test For 3 Values
Firehorse,
I added some notes to the workbook that I have attached. Included a Debit/Credit column. The explanations of what each column is calculating is simply to help you understand what each formula is doing. If any or even all of them need to perform differently, add a comment or a note telling exactly which numbers you want to include and when (W, P, Stakes, etc).
Sorry I didn't get back to you sooner, but one of the posts on my car battery came out, so I spent most of the day bumming rides to school and the auto parts store. All fixed now though.
Regards,
Re: Sumproduct Formula Retuns #value Error
Using CTRL-Shift-Enter to place the formula above in the desired cell will return the correct answer unless text or a blank is encountered. More or less the same solution you already have though.
Regards,
Re: If Formula To Test For 3 Values
Ok, I'll give it a look and post back tomorrow. It's getting late where I am and I have to get my sons to school in the morning. This shouldn't be much of a problem though.
Regards,