I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
I have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
I want to count the number of cells in a row containing a value greater than a fixed number but less than a number entered in another cell.
For example, in the cells A1..K1 I want to know the number of cells which have an entry greater than 7 but less than the value I have entered in cell M3. I have used the COUNTIF in other circumstances but I cannot find out how to use it with this "greater than and less than" condition?
I'm trying to make an expression to calculate some fields in Excel 2007. Trying to check fields up to a given number and give them a high multiplier, and add the rest of the numbers with a lower multiplier.
I have multiple fields with numbers in hundreds. I want to multiply the sum of this fields (up to 500) with 4. But the expression must also start the count from the first field, and when im hitting 500 as a sum I want the rest to be added with 2 as multiplier (the reason for the prioritizing of the fields is that they also have their own values/multipliers, but I'll skip that to avoid to much confusion here).
It's probably a lousy description. I'll give an example:
Field 1__Field 2__Field 3____Sum 100____300_____500______2800
How in earths name did I get that Sum you might ask. ((100 + 300 + 100) * 4) + (400 * 2) = 2800
400 is the number left from the third field wich I only want to multiply with 2 instead of 4.
Field 1__Field 2__Field 3____Sum 300____600_____500______2000
((300 + 200 + 0) * 4) + (900 * 2) = 3800
900 is taken from the rest value of field 2 and 3.
If there any way of doing this "sum check and multiply, and then add the rest with a different multiplier"?
With Excel you can do If(array, array2, false) to poplulate another array. Is there a way to replication this without looping. For instance can the IIf hold an array as its expression or is there any other way of replicating this If statement without looping?
I'm trying to figure out the whole Regular Expression thing...so I figured Hotpepper's EXDATA UDF would be a good place to try and rewrite with Regular Expression...I got it to work...but not sure why as it seems opposite of what I would have expected...
eg pattern [^/d] i thought would give me everything except numerics...but it appears to be giving me numerics....
here's the code and sheet...can someone help explain what's going on? .......
I am currently using a SUMIF statement as follows: =SUMIF(W1:W1500, "Charnwood" & "*", S1:S1500). The problem i am having is the fact that SUMIF doesn't work with a reference to a closed worksheet. I tried using SUMPRODUCT but it did not work. The reason it didn't work is because I need to find in column W anything with Charnwood and anything else past it, auch as Charnwood Estates. This is what I tried: =SUMPRODUCT((W1:W1500="Charnwood")*(W1:W1500="*")*(E1:E1500="S"),S1:S1500) which of course is a syntax error.
I have been reading the different post here on the forum. I have found a snippet of code that will open a pop-up message from a value of a cell. I would llike to do this on one of my reports. Here is the scenario.
Range (R5:S5) 0.15 them MsgBox "Note Must Be Entered For +/- 15% change" But There are 14 different cells that I want this to work for. The value differs from day to day, and this should pop-up happen without a user having any actions other then the calculated value. But ones the note is entered in the "Notes: cell the message will not return. Hope I explained it correctly.
The value of the cells in question are calculated in each cell with a formula example "=IF(ISERROR(Z20/H5),0,IF(ISERROR(Z20/H23),0,(Z20/H23)))"
I have a large spreadsheet in which I have reached the limit for conditional formatting. (i.e. 2050 rows)
To resolve this I can add a single expression for conditional formatting across all cells. This means I need to create a more complex expression.
I effectively have two types of conditional formatting that are very similar. I have the standard Yes, No & A/L which I wand to shade accordingly. I then have alternating columns of Yes , No , A/L . I have introduced spaces to stop these values from being counted by another formula but still display yes and no. I want the shading to be the same in both cases.
I have built the following expression which does not seem to work.
(Excel 2007) The issue I'm having is that a loop statement of mine is failing to recognize that a couple values are equal. It works for the first 3 iterations of the loop, then stops recognizing. I tried copying another set of numbers in the failing one's place, and that actually worked. I can't attach anything, so I'm including a link to a detailed screen shot with some notes on it and code that I'll host on my site. Not sure how else to explain the issue, but if you take a look at the screen shot you can see the excel sheet being worked on and the vba code running. I will also include a text file of the code
Here are the links to the screen shot and text file of my code Text file Screen Shot
I am using the following to check if a string has a sequence of 6 decimal digits in it. But am getting an error. If(str Like *######*). I have to check if str has values like 123456USA ; ABC725439 ; jh658478hd. I thought # represents a single digit and * represents any no of characters
I've some code that I use to scan HTML to find images. The basic part string I use is:
WildSought = "**"
The problem is that there are several ways to close an image tag. The above closes using /> but you could also use "> for example. Is there a way in my regular expression to say "this OR that". Maybe it would look something like:
I've attached the workbook to make this easier. If you look at the file master.xls you will see 2 tabs. On the Tool tab I've highlighted some blue cells and some rose cells. If you look at the blue cell G68 there is a long formula that reads up to 3 cells from the Specs tab and then plugs in the correct data from the blue cells on the right side of the Tools tab page. I need the rose cell F69 to do the same thing only with the data from the rose cells to the right. When I try and duplicate the fromula from G68 into F69 I get errors. I hope I made this clear enough. The formula in G68 is long, is there a way to shorten it? Also sometimes in this workbook when I try to type a formula in a cell I get the text I typed instead of the formula. For example I might enter in A1, =B2. Instead of getting the data from B2, it reads =B2. I've looked at how the cell is formatted but can't get it to work.
I know that a variable of string type is equal to "" before it's assigned a value. So I reset the value of strings to empty (i.e. "") when I want them clear of a value. I also know that variables of Variant Type are equal to NULL when they are unassigned so I reset them to NULL when I want to clear them of a value.
But what about a Long? When you declare a variable of Long Type or Int Type or Double Type, what is it's inital value when empty and unassigned. I assume it's not 0 since 0 is an actual number.
I want to reset my lng variable and clear it of a value. So I'm wondering what should I set to... How can i do this?