dont know if this can be done using a formula rather than VBA...but is there a formula that would change the countif range based on another cell entry. so the formula below has a column range of 2...so the other cell entry would be '2'.
=COUNTIF(B1:C1,"=Y"). If i then changed the cell entry to '4' the formula would change to. =COUNTIF(B1:E1,"=Y")
I have a percentage in R3.
If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.
I will try putting the Code Tags in correct this time, I think I figured out what I have been doing wrong.
This code will allow one cell to make a sound when a condition is met. I am trying to have each cell stand on its own. Example cell A1 would sound a Ding if the number >100 and cell A2 would sound a Tada if the word Cat would be returned ....
I have this forumula in a cell:
=SUM((COUNTIF(F7:F69,"<=" & TODAY())),(COUNTIF(B7:B69,">=" & D7)))
and the trouble I am having is that in the second Countif, I need it to check the cell in the "D" column relative to what cell is being checked in the "B" column. Right now it is checking every cell in "B" against "D7" instead of "D8, D9, D10", according to what cell in "B" is selected.
=IF(E14<=0,0,IF(N9="yes",MAX(E15*C15,30),30))
I am currently using the above formula and need to make an addition to it.
If D8 is greater than 9000 and less than 9999 then the entry will be 35 rather than 30. Any other entry in D8 would leave it at 30
i'm trying to basically use VBA, so that when a list of work tab names are entered into a column in say Sheet 1..these tabs are then selected.
View 4 Replies View RelatedI have a dilemma.
1.I want to be able to a have a cell be filled with a certain value when the user enter data in Column C.
2.I know that this can be accomplished by a formula, but my spreadsheet will work my efficiently with a macro.
3.I do not know much about VBA but I believe that I will need a macro for each sheet that fills in the word.
i'm sure this can be done but i cant get an angle on the method. i want to use VBA to put a formula into cell G3 based on the users entry in cell D3 so, for example the user enters M in cell D3 and the VBA code puts the formula "if D3="M",A3,0"into cell G3
i know i could use a formula in the cell but i want it to work with multiple entries so i figure VBA is the way to go.
I would like to write the code or create a macro that will execute when the value of a range of cells is greater than null. The macro or code that I would like to execute will UNHIDE a group of consecutive rows.
View 14 Replies View RelatedI have a workbook with 3 worksheets which contains a single spreadsheet where the user enters data and 2 worksheets containing all the named ranges and formulas(divided up by product model) used on the data entry worksheet. What I've been trying to accomplish is to choose one worksheet based on which product model I select on the data sheet.
View 1 Replies View RelatedI need to force our salespeople to enter information in a cell based on a condition. This is the condition: If I5=3, then I need to move the user to cell I3 with a message that says "Enter miles to site" -- I don't want them to be able to move from the cell until they enter the information. If I5 does not equal 3, then I don't need anything to occur. I need to apply this code to four separate worksheets in the same workbook. Also, they will be entering a number in cell I3, so is okay to leave I3 formatted for Number or General Number? Note: This question is also posted at the following link, but has not yet been answered by that group: http://database.ittoolbox.com/groups...dition-2270643.
View 2 Replies View RelatedIs there a Countif that works on the criteria of the font being 'Red' or 'Bold' ? This needs to work based on conditional formatting I have on the cells in question.
View 7 Replies View RelatedI have a sheet that I fill out with customer data then print and start over with the next customer. This requires me to tab and delete through the sheet before starting the next entry and I am wondering if there is some way to auto clear the unlocked cells based on a single entry IE when we entered new data in the 1st field this would clear the unlocked cells and make them ready for new data?
View 14 Replies View RelatedI am trying to do with data validation, trying to stay away from vba on this... and it is probably very simple:
Cell A1, they can select Rice, Cheese, or Rabbit
I want to use custom data validation on B1, so that if A1 = Rabbit, they can only enter 1. If it is blank or the other two choices, they can enter 1 through 10.
Can I do that with data validation? I can't get any if thens to work in it.
I have the following formula in cell H2:
=AND(EXACT(G2,UPPER(G2)),(LEFT(G2)>="A")*(LEFT(G2)<="Z")*(MID(G2,2,1)>="0")*(MID(G2,2,1)<="9")
*(MID(G2,3,1)>="A")*(MID(G2,3,1)<="Z")*(MID(G2,4,1)>="0")*(MID(G2,4,1)<="9")*(MID(G2,5,1)>="A")
*(MID(G2,5,1)<="Z")*(MID(G2,6,1)>="0")*(MID(G2,6,1)<="9")*(LEN(G2)=6))
I would like to prohibit data entry in G2 if H2 reads "false" and allow data entry in G2 if H2 reads "true" .
i am trying to find a way to automatically copy information from a worksheet on my computer([list.xls]-List of accounts) to a worksheet on a shared folder([summary.xls]- accounts that have paid)
example:[list.xls]Sheet1!A:A has account numbers, C:C has account balance and D:D has notes on account.
If D:D is "paid", copy acc# to [\foldersummary.xls]Summary!A1, balance to B1 and notes to D1.
If D:D is "payment pending" do same as above but in row 2 and so on.
Trying to write a formula that looks for the first number in the same row, then returns the value in that column in a different row. In the example included, cell A-7 should contain the formula. Within row 7, determine the column where the first number is listed (column D in this case), then return the value listed in row 3 of that column ('C' in this case).
I've tried Lookup, Index, and Match functions, but can't seem to get the right combination.
I have a row of dates in row 2 (all sequential, from Nov through to June next year)
I have a row of number entries in row 3 (to correspond with the date in row 2 it was entered on).
I want to be able to add a new entry (a number) into a text box, click a macro button, which makes the text box input be moved into the cell underneath todays date (so every day the cell will move one column along)
I've managed to make myself a textbox, and a macro button, but I'm stuck with how to make it all work.
If A1 = "Yes"
Then don't allow input into cells B1 and C1. Or delete anything in B1 and C1 perhaps?
Cell A1 would be the result of a formula
How would I go about creating something like the above?
I am looking for a forumla, which I think will be an If forumla, to allow/block entry into a cell depending on what is entered in another cell.
So if "Yes" is entered into cell 1, I want cell 2 to show "N/A", and if "No" is entered in cell 1 I want the user to be able to enter data into cell 2.
I have a spreadsheet with 'due date' for returns in column L and 'received date' for returns, in the adjacent column M.
I would like to get the due date to turn red when it is overdue, ie. the date in the cell has arrived and no return was submitted. By trawlling the forum, I managed to accomplish this with the following in conditional formatting:
Cell value is less than or equal to =TODAY()
All's great.....but - I would like the date to turn back to black if I enter a date in the adjacent received column, column M.
In other words, I want to flag a problem if the due date has passed, but once I receive the return and enter the date I receive it, i would like the due date entry to revert back to the original formatting ie. black. So only the outstanding returns are highlighted.
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.
1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.
2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
I am currently using a color count function to both count by color and count by color and cells containing certain characters, such as # or %. This works great.
However, I need to modify the UDF to count all the cells in a range in columns C:Z that contain alpha, AND IF the Interior.ColorIndex of a cell in column B within the same row of the counting formula equals the Interior.ColorIndex of a cell in column B within the same row of the selected cell in the range.
Basically, Column B is a header row, and I want to count the cells in a range in each column C:Z if they contain a name AND their corresponding header cell's color in column B matches the color of the header cell in the row containing the formula.
i have an address that is all in one cell displayed like
101 hampton Court, Hampton heath, Hampton Town, Hamptonshire, HA01 1AS
but i need to have it split in to individual cells so
Cell A1 would be 101 hampton court
B2 Hampton Heath
C2 Hampton Town
D3 Hamptonshire
E5 HA01 1AS
each part of the address is split by a comma, so i have tried to use that as a identifier as to where that part of the address is, but failed on that, i can separate out the first part and the post code with a find and replace but not the middle.
also i need it to work backwards ie
it finds the post code first,
then the county
then the town
as those 3 are always the last 3 parts, but the address could only have 1 line of addres beofre the town or 3, and it would get messed up as all the post codes, county ans town needs to be in their respective columns
Below is my function. I use it to count the number of times a value in A9 appears in Sheet2. I would like to edit my function to count the value in A9 that appeared in a given month in U6.(U6 is 12 for decemeber)
=IF(LEN(A9),COUNTIF(Sheet2!A:A,"*"&A9),"")
This is what I thought it would have been but I think I'm wrong because the function is not counting by month.
=IF(LEN(A9),COUNTIF(Sheet2!A:A,"*"&A9),IF(MONTH(Sheet2!D14:D9000)=U6,"")
I need to have cell with the total rows of Column E that equals TODAY() AND Column F = "Repair". I have tried various COUNTIFs with not much luck.
View 5 Replies View RelatedI have data in two columns. Column B has calculated string values of "BMAJOR" and "BMINOR" and column C has date values.
I am trying to get a count of how many occurences of "BMAJOR" or "BMINOR" occur for every week starting today. I have column AJ which returns today() and column AK which returns today()+7. I can individually count number of occurences of dates that fall within these dates with the following formula. This for row 2.
=COUNTIF('Decision Tree'!$C$4:$C$215,"
i have the following formula which counts various cells containing dates and i need to add in a count for 1 cell, BV21 but it needs to count 3 if theres a date and 0 if the cell is empty or reads NA
=COUNT(AS21:AX21,BH21:BN21,BR21:BU21,BW21:BZ21)+
(COUNT(AY21,BB21,BE21,BO21)>0)
+(COUNT(AZ21,BC21,BF21,BP21)>0)+(COUNT(BA21,BD21,BG21,BQ21)>0)
+IF(CB21>0,CB21,0)+IF(CD21>0,CD21,0)+IF(AP21="Y",2)[/size]
might be simple to some but it really has me stumped.
I have to make eighty to ninety entries daily in Column B. Then transfer these entries to another column N,(by copy/paste) after that a maco "text to column" is executed. Now I have linked Column B to N like =B2, =B3, etc. etc. thus instead of copy/paste, the data when entered in Column B, is automatically going to Column N. I wish that the macro (text to entry) may be executed by each entry made in Column B. Is it possible with a macro?
View 7 Replies View Relateddo Different SORT based on data entry.
I have a macro in a workbook that looks like this: