I have one tiny qs which I hope someone can answer for me.
I need to allow a user to see a dropdown list (with options) only when the previous cell has the word "complete".
For example, cell B1 can have:
On going
Complete
Blank
If B1 has complete then I want the user to be able to select a number from a drop down list in C1 otherwise I want it to say "wait" or if its Blank to be [Blank].
I tried the Validation > Custom and put in
=IF(B1"Complete","Wait",OptionsList)
Where OptionsList is a defined list of 5->1 but this lot didnt work. I tried similar things in conditional formating but nothing again.
I have long text values that include names in cells A1:A100.
In cell D1:D10, I have a list of names that I would like to check if any of them is found in each of the cells in A1:A100 and if yes, return TRUE in column B.
So in a way, what I need is a a bit like the SEARCH function, only that I need to find multiple FIND_TEXT values. If just one of the names in D1:D10 is found, the formula should return TRUE.
In the attached spreadsheet I would like to create a list of part numbers if column D E & F all have a #N/A value. Right now I'm using a True/False but this list contains around 3000 items and I don't want to have to scroll down to find the True values. Is there a way I can just create a list of corresponding part numbers to the True Values?
I have two data columns: the first is a list of the last 100 calendar dates, and the second is an ON/OFF (or 1/0 if you prefer) value corresponding to each date. If the last value in the second column list is ON, I want to find how many continuous days the value has been ON for. If the last value in the second column is OFF, I want it to return a zero. Note that in the 100 day span of data, the value may switch between ON and OFF several times, but I only want the continuous, uninterrupted ON days at the end of the date range.
I have two lists, one that is 99,000 lines and the other is 150. I am trying to find out where the word(s) in the list of 150 is present in the list 99,000.
1st list of 99,000 is in Sheet1 A1-A99000 and the second list is in Sheet2 B1-B150.
The caveat is that in column A there is additional words in that cell so you can not do a simple vlookup, because there may or may not be an exact match.
I have been stuck for hours and the best i can get is to use the match function but it is not working because it is not exact. Last thing i want to do is use the CTRL F key to lookup all the words.
I want to compare a hand entered data sheet to my system data. System data is exported in. Then that data is used to populate a pivot table. I use a lookup list that fills in matching data from my hand typed sheet. From there I want to use true/false to see if the data matches. However, the formula will not copy down. I am sure it has to do with the formula holding a value, but I can’t seem to correct it.
Recap 1.System data populates sheet 2.Pivot table pulls info I need. 3.Lookup list cross references hand entered data sheet and puts value next to pivot table. 4.I need to do a true false to see if data matches but can’t get it to work
Example worksheet: On the worksheet you can see that the true false statements are incorrect. The system data and the hand entered data match two times not once.
I have a worksheet where I have around 300 rows, each with 7 columns. What I want to do is add a checkbox to each column. I plan on setting non-applicable checkboxes to mixed status and locking the worksheet. I will unlock applicable checkboxes and sumif or countif their value according to row-based scoring, for example, each checked checkbox represents a value of 3. I do not know VBA and have chose to use the form control checkboxes rather than ActiveX.
I believe that a formula for this would be something like: =SUMIF(B1:B3,True,"3") or =COUNTIF($B$1:$B$3,True)*3
I am wondering firstly if I have that right and secondly if there is a way to stop my checkboxes from displaying labels. Currently, if I click on one it displays True behind the active checkbox. If I uncheck it, it displays False.
If a cell changes to true i want it to send an email to the address that is in another cell,
For example if F5 CHANGES to true then send email to address in G5,
From here i can add in the subject line, and body i just cant figure out how to get it to send to a specific email address based on the cell value, and also only do it once, when it changes to TRUE rather than everytime the sheet is active, so i would require a macro to constantly be running, or run of off the cell when it changes
DATE A B C D E 2/22/2008TRUEFALSEFALSEFALSERon 2/23/2008FALSETRUETRUETRUEPhill 2/24/2008FALSETRUEFALSEFALSETracy 2/25/2008FALSEFALSEFALSEFALSESharon 2/26/2008TRUETRUEFALSETRUEBill
On sheet two I need to list any date that has three or more true statements with the coresponding name.
I am trying to Sum lines of info with "True or False" and "Yes and No". I would like to assign 1 to True and Yes and 0 to False and No when I total the rows. Never tried this in Excel, on Lotus and the formula does not work. I can find and replace, but I would like to be able to use a formula.
how to denote a cell as blank? I'm trying to do an IF formula for if the cell is blank.. Actually it would also work if the formula reads if the cell was NOT blank. Either one would work I just can't figure out the name of an empty cell. So I'm looking for this with the right word in the formula. =if(A1="blankcell",true,false)
I have a worksheet with a cell range where the cells can be either logical TRUE or FALSE as a result of recalculations elswhere in the sheet. Only one cell in the range can ever be TRUE at any given time. For much of the time all cells in the range will have the status FALSE Each cell in the range has the same relative logical formula linking to values in other cells in the worksheet I need an event driven macro to find and move to any cell in the defined range if it changes status to TRUE No action is required when the status of the cell changes back to FALSE from TRUE
I have a credit card payment estimation sheet that i am trying to set up a cell that will display the same data as the last cell in columb A that the IF function displays true. I have attached a sample of what i have so far. Would like the data do display in the yellow cell with the red border. File is in Excell 2007 Format.
If you look at the attached spreadsheet you will see 6 tabs. Main, New Cust, Quote, etc.
"Main" will be the sheet that everything is inputted. If there is a "Y" under new customer, I want the entire row to copy to the tab "New Cust". If there is an "X" under quote, struct design, or graphic design I want it under its approp. tab. The Lisa tab should take certain Sales-Person and copy entire row on the Lisa tab. The certain Sales-Persons are BS, PK, PB, PD.
Buttons are fine. The "main" page will be getting updated daily w/ new info so after entering new data, i can press the button and it should copy the above to its locations.
If Not IsEmpty(ActiveCell.Value) And ActiveCell.Value >= 4050 Then
The code in the above if statement is running when I hit a cell with no contents. When there is no such cell, it works fine. I've tried using "" as well but the same thing happens.
I have been looking through about 4 pages of these threads and searching the internet and can't seem to find my answer.
I am trying to count about 400 cells in column N if a different criteria is true in column M. The numbers in row N are a dollar amount, and the criteria in column M is the type of donor. I need to know how many donors there are of each type. The trick is that there are two other columns with dollar amounts for each donor, and I am also counting based on what event donors made gifts for.
Column M - Donor Type (designed by a letter "E", "A", or "P") Column N - Annual Appeal (dollar amount by each donor) Column O - Off-cycle (dollar amount by each donor) Column P - Event (dollar amount by each donor)
I have a summary chart at the top of all this that should be able to tell me how many donors of type E donated for the Annual Appeal, how many donors of type E donated for the Off-Cycle, and how many donors of type E donated for the Event, etc based on donor type.
I've tried COUNTIF using multiple criteria, SUMPRODUCT, all kinds of things. Usually what happens is a get a #VALUE returned or the function counts the total number of donors by type (ie 269 E donors when what I want to know is how many of those donors donated to the Annual Appeal).
I am trying to make a sheet that I can make selections from a list of things on one worksheet and have the selections pull through to another worksheet. I have attached a mockup of the data, the ideal outcome and the logic is basically this:
If Cell A = Yes, then populate this cell with Column B Data
Repeating until ALL Cell A = Yes have been reviewed
I wish to do the following & and am struggling (attempted to do it in Conditional Formatting but have not succeeded)
I want to do the following :-
If the date in cell A1 has no date entered i.e blank then fill D1 in Yellow If a date in cell B1 is past then fill Cell D1 Red If a date in cell C1 is entered with a date(any) then fill D1 in Green
Here is the qtn if two cells A1 and A2 has "ab 1" and "ab 2" the answers for comparing cells should be true as both cells has first 2 letters as alphabets then space followed by numeric..format of cells is same.
In the below link u will find each state driving license formats of ..example for alabama 7 Numeric..for ALASKA Up To 7 Digits..what am i looking is if we select a specific state and then insert any driving license of that state in next cell if the format matches i want that next cell to be true if not false ..wonder if we can create anything like this ..there are about 50 states with different license formats..
Is there a formula or VB code like the =TYPE() function that will return whether a cell contains a FORMULA? Here's the issue:
I'm writing a macro to update 10,000 sheets. Each sheet has a column that I'm updating with a new formula. Some of these formulas have been previously overwritten with a random number that would delete the formula from the cell. I have to leave the overwritten values where there is no formula, and replace the values of the formulas where they are still intact. I assume that if I can identify whether there is a formula in the cell or not, I can choose that cell to skip or update.
I am trying to get an average number of Networkdays where specific cell values are true. If the project Status is 'ongoing' or 'overdue' in Sheet2, what is the average Networkdays of the open projects for each project lead for Column C in Sheet1?
Sheet1 Column A Column B Column C
Project Lead Count of Projects Avg Age of Projects
I want to return a true or false result that I can use in a formula to indicate whether "Edit directly in cell" has been "set". The VBA code is Application.EditDirectlyInCell = True
Trying the following syntax and I do not get any value in the cell if either of the conditions are true. Pretty sure I'm nesting the functions incorrectly. Maybe I should use Index/Match? =IF(AND(E24="SI",F24="SI"),"ER2","",)IF(AND(E24="sn",F24="sn"),"er2","")
