If, Then Formula With Cells That Contain Lists
Feb 24, 2010
I have attached a few rows of data:
Current PositionCurrent Store FT MGR 212Proposed PositionProposed Store PT MGR 212
In the Curent Position Cell and Proposed Position Cell there is a list of the following options: FT MGR/PT MGR/FT ASST MGR/PT ASST MGR
Is there an IF, THEN formula that I can use to tell whenever the FT equivalent or PT Equivalent shows, AND the current and proposed store is the same, then "NO ACTION REQUIRED"
I was trying to send this as an attachment but I don't see that option
View 9 Replies
ADVERTISEMENT
Mar 29, 2014
My problem in the given excel file
1. Sheet1; how to Add "B000" in the List A or "B00" if the digits and 5 in the list
2. Sheet2; how to remove "B000" or "B00" from the list
3. Sheet3; how to highlight difference or find out the difference between to lists
Update_List.xlsx
View 10 Replies
View Related
Mar 11, 2014
In worksheet 1 I have a list of employee numbers (column A) and and names (Column B)and in worksheet 2 I have a list of employee numbers (column A) and their payroll contracts (Column B). 1 employee can have multiple contracts).
What I'm trying to achieve in workshet 1 is to have a dropdown list ( Column C) which returns just the contracts that relate to the employee number in column A.
View 7 Replies
View Related
Aug 12, 2014
I have two lists and I would like a formula which only returns unique values - similar to an advanced formula. Is this possible?
In the attached example my data is in columns C and D and I would like a formula in column E if possible. I need it to ignore the capitals. E.g. BLACKBURN and Blackburn appears in each list but I would only want one instance pulled through in column E.
Currently in column E I have the desired result.
View 5 Replies
View Related
Feb 18, 2010
I have 2 lists. One column has a about 200 values of 7 digit numbers. The second column has about 500 cells with text. This text should include one of the 4 digit numbers from the first list. If it doesn't, I want to purge it.
The 7 digit numbers always appear in the same spot in the column 2 figures. So say the number I'm looking for is 1122 and the second list has a cell with L2871122BD. Perhaps there is an easier way to do this, but my plan was to.
create a 3rd column that has formula =mid(B1,5,4) which should isolate the 4 digit value i want to compare to. Then create a 4th column that has the formula =ISNUMBER(MATCH("*"&C1&"*",A:A,0)) to check the newly created MID number to the numbers in column one. Afterward I would simply arange from small to large and delete all of the false entries in column 2.
The only problem is, my ISNUMBER function doesn't work when referencing from my MID formula to column 1. If I do the match and compare the smaller number in column 1 to the larger in column 2, it does work. Is there an easier way to delete or highlight entries in Column 2 whose middle 4 digits don't show up in column 1? Here is an example of what I'm working with.
View 2 Replies
View Related
Apr 23, 2007
is it possible to first make an IF check on a cell and then as one of the results (true or false) to give the cell a Validation List?
So for instance if the IF will come out true, the Cell would just diplay some text and if the IF will come out false, the user will be able to choose something from a Validation Drop-down List?
View 9 Replies
View Related
Oct 18, 2013
I recorded a macro:
Code:
Sub Makro4()
Range("A4:C15").Select
Selection.ClearContents
[Code] ......
This code is working home on my MacBook, but I wonder if you have any suggestions for improvement? I will assign this macro to a button.
View 3 Replies
View Related
Sep 13, 2013
I have a long master list of registered members, column C has last name, column D has join date.
Now I have a short list of last names with join dates.
I want to compare the short list with the master list to find names that are already there, by comparing the last name and join date.
View 8 Replies
View Related
Dec 16, 2007
I have 2 lists in column A and column B. For each cell in column B, I want to put a "YES" in the corresponding cell in column C if the contents (of the cell in column B) are somewhere in the entire list under column A (I think the list in both columns is about 5000+).
I have logged in after quite a gap and found that all my subscribed threads (gathered over a year) have vanished
View 3 Replies
View Related
Aug 30, 2009
I am using a number of lists that act as filters for a range of cells. For the filters, I have a few selections for each and I also would like to have an all filter (no filter) but without the formula being too lengthy (or code). Is there an easy way to be able to not use a specific filter if a certain selection is made ("All" for example)?
View 5 Replies
View Related
May 14, 2012
Do not use validated drop down lists in merged cells.
It will only be a matter of time before it breaks Excel.
(Both 2007 & 2010)
View 1 Replies
View Related
Aug 1, 2006
I'm trying to write a macro that will build a tree from 3 sets of lists and I'm really stuck!.
The thing is:
1. I have 1 worksheet with 2 sheets. First sheet "Lists" contains in every column a list of data.
2. Range("B:C") contains list od SERVICES1 - every value in list has its corresponding ID_SERVICES.
3. Range("D:F") contains list od SERVICES2 - every value in list has its corresponding ID_SERVICES2 and a parent id ID_SERVICES.
4.Range("H:I") contains list od SERVICES3 - every value in list has its corresponding ID_SERVICES3 and a parent id ID_SERVICES2.
5. Second sheet "Project" in Range("A:C") contain example of tree like: ...
View 3 Replies
View Related
May 13, 2009
Is there a way to make a drop-down list optional? When I create drop-down lists the user has to make a choice from that list.
Example: a list of doctors. I need the user to be able to select from that list if the patient saw one of those physicians, but if the patient saw another doctor not on the list, I need the user to be able to enter the name of that other doctor. Currently the user has to select from the list or not enter a doctor. I am using Excel 2003.
View 2 Replies
View Related
May 16, 2014
if there was a way to automatically add each student to a class list as their information is inputted. What i mean is, after input Sally Student in the main enrollment list she would automatically go to the class list for the class she has chosen.
I have attached a sample workbook with my desired results. Basically what i want to do is make this easier for those in charge to get the class lists from the main list without having to copy and past all the time.
The class lists are in the tabs in the workbook.
View 5 Replies
View Related
Feb 27, 2014
I have a sheet that lets the user enter a category and then a sub heading, I need the data to be displayed under each category heading in alpha/numeric order as each new entry is made into the list.
Please see the attached sheet for details. Book3.xlsx‎
View 5 Replies
View Related
Apr 20, 2009
I've been breaking my head over this and i haven't managed to make this work. The attached excel file contains a list of students that participated in exchange programs on the past. What i want this to do is that when i choose the university the student went on exchange with, that the city and country cell fill in automatiocally with their respectuve info. I have a worksheet on excel that has the university name, on the next cell the city, and on the next cell the country.
View 3 Replies
View Related
Apr 29, 2009
ive had to retype this so please bear with me. ive utilised the resources of this site before but ive come to the time where i need to ask a question of my own.
now i will refer to my attached document as what im trying to achieve.
my project is to create membership cards for our social club at work, out of a list of people we have on our manifest.
If you refer to column A, this is where i will dump the manifest of members as time progresses (keeping in mind that more names will be added to this list as it goes).
What i am hoping to achieve, is to start at Cell A2 (highlighted in yellow). For the example the membership name is "a". i want to hit the macro button and the following will occur.
1. data "a" from Cell A2 will be copied to B2 (highlighted in green) and G6 (highlighted in blue). This moves the name from being on the unregistered list over to a membership card and to the registered list, which allows me to cross check that im not doubling up on names with new applicants and current members.
2. once this occurs, i was cell A2 to delete and cell A3 to be moved up, so now, cell A3's data "b" (highlighted in orange) will be moved to A2.
3. a similar process as point 1 will now occur, where A2 will be moved to the highest empty cell in Column B, which would be B3 now. It will also copy to G18 into the 2nd membership card.
4. this process occurs until column A is empty in A2.
5. i wish to really only have an A4 page worth of membership cards before i print, so i wish to be able to "reset" the membership card boxes, so that instead of the membership cards continuing all the way down the worksheet, it will do say 12, then print, then start back in the 1st box. Ideally this is a loop so that it occurs until there are no more names in Column A.
This is a huge feet for me as an idea because ive never gone beyond using basic recorded macros, however im the type who just HAS to learn how to do stuff, so i will lap up any information or assistance i can recieve from anyone here.
View 11 Replies
View Related
Jul 1, 2009
Trying to create a dynamic costing spreadsheet, where the user has to make multiple selections from many dropdown lists, all of which end up showing a total cost.
So far I have managed to create relatively dynamic lists, so that the choice in one cell will dictate what options are available in the next.
But if a user selects an options form all the dropdowns, and then decides to go back and modify a previous option to something else, the effected dropdown lists do not change until you click on the drop down arrow again and see the options available have changed.
Let me try and simplify...
'Menu 1' has two option: 'A' and 'B'.
If 'A' is chosen, then the next menu, 'Menu 2', will have options '1' and '2'.
If 'B' is chosen from 'Menu 1', then the options in 'Menu 2' will be '3' and '4'.
Lets say the user chose 'A' from 'Menu 1' and then '1' from 'Menu 2'... AND THEN went back and changed their choice in 'Menu 1' to 'B'....
'Menu 2' will still be displaying '1', even though the options are only '3' and '4'.
Can I make the dependant menu's either reset or blank or highlight to show that a change made somewhere else has made them currently invalid
View 10 Replies
View Related
Aug 31, 2006
I've got a table that has to be manipulated by multiple users. Within the table I have several column ranges with validation lists (in drop down boxes) in order to limit responses. However, I'm finding that most of the users are pasting data in from other sources that may not have been in the validation list, or may be in a different format, etc., and it essentially undermines the reason for using the validation.
I've tried protecting the sheets using various options and tried looking around online for a solution, but I'm not finding how to get around this.
View 9 Replies
View Related
Mar 28, 2014
Is it possible to have a formula that generate a Unique list (List 1 and List 2) of Missing values in List 2 by comparing two lists? Something like this
List 1 List 2 Missing in List 2
1 1 3
3 4 6
5 5
6 6
View 3 Replies
View Related
Apr 24, 2008
I'm looking for a solution to a problem and from what I've read in the forums so far, this is the place to come. I first want to say that I have tried to solve the problem by using guidance from other similar threads and adapt those answers to my needs, but as my understanding of VBA is non-existent this was somewhat difficult. I don't want to post a repeat question and I hate wasting people's time if unnecessary, but I can't do this on my own.
That being said, here is the problem;
(I have attached a simplified copy of the worksheet I am working on for reference)
I would like to lock a particular cell in my spreadsheet based upon the selections made from two drop down validation lists (Cells “C4” & “D4”)
Essentially, if a correct choice is made in both, then the cell “E4” becomes unlocked for editing. If however, the correct selection is made in one list, but not the other, I need the cell “E4” to remain locked and a message to appear to prompt the user to correct their mistake.
Furthermore, both the validation lists have a whole range of selections that are very similar but have subtle differences;
E.g.
"Soft Eye by Talurit"
"Soft Eye by Splice"
Is it possible to write a VBA code that performs a similar function to the =IF(ISNUMBER(SEARCH("**",))) formula, so that if any choice that contains the word "Soft" is selected from either or both lists then the cell “E4” becomes unlocked, but if the selection does not contain that word then the cell remains locked?
If anyone could provide a code to make this happen, I would be very grateful! But please explain how to do this, as the word “Soft” won’t be the only parameter and I may need to alter the code so I need to understand the principle behind it; For example, on the spreadsheet, if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Soft Eye’ is selected then cell E4 is unlocked for editing. If in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Fused and Tapered’ is selected then cell E4 is unlocked for editing, but if in the ‘Inboard End’ list a ‘Soft Eye’ is selected and on the ‘Outboard End List’ a ‘Standard Eye’ is selected then cell E4 remains locked and an error message appears to alert the user that his/her choices are incorrect. If however, "Soft" is not selected in either list then cell E4 remains locked (it has to be selected in at least one of the two lists to allow editing).
This is a work in progress (not all the formulas that will eventually be used have been entered yet, so please excuse the untidiness of it all) and the choices are likely to change based on input from my colleagues, so I need to understand how the VBA works in order to alter it accordingly, but I don’t want to put any more work into this (it’s still in the early stages) if it isn’t possible to achieve these needs.
View 9 Replies
View Related
Jan 11, 2009
i need to work out a formula for my spreadsheet which I use to work out cutting lists for timber frames. I need it to work out if the width of a job is for eg 2400mm i need to work out how many timber studs I need so the space between each stud is between 400mm and 500mm and this will need to work for a range of different sizes of frames. I have it written at the moment and it just devides the width by 400 and gives me a amount of studs but it would work much better if it could space them between 400 & 500.
View 4 Replies
View Related
Dec 22, 2009
Here is the scenario:
A B C D
BananasApplesOrangesTotal113=A1+B1+C1
I need to reference the formula in D1 with the cells headers names.
In a perfect world, it would take
=A1+B1+C1:
and produce:
=Bananas+Apples+Oranges
View 9 Replies
View Related
Mar 29, 2014
Have you ever copy a row with formula in locked cells & insert it in a protected worksheet?
View 1 Replies
View Related
Jul 22, 2014
Is it possible to write a formula that skips x number of cells and have the formula autoincrement as usual. For an examples lets say I want to skip 3 cells and I have entered in the cells listed:
A1= "1"
A2= "2"
A3= "3"
Then I type in the simple formula in the cell
B1= "=A1"
and then drag down but it skips 3 cells so then
B5= "=A2"
B9= "=A3"
If it didn't skip then
B1= "=A1"
B2= "=A2"
B3= "=A3"
this is just an example as I will be writing more complex formulas and need some thing that I can write into the formula so that it skips.
Something similar to this B1 "=A1(skip3cells)"
View 4 Replies
View Related
Jan 31, 2008
I am looking to average a range of cells which won't always be the same size. How do I create a formula array that will omit empty cells in my formula.
View 9 Replies
View Related
Apr 18, 2014
What I'm trying to do is link a cell to give me the sum of 2 different cells located in 2 different tables I created.
So I want to have Cell B5 look for the number in cell B3 in the Table that starts in column Q1 and give me the corresponding number in the same row in the column R1
then lookup the number in cell B1 in the Table that starts in column T1 and match it with the number in Column U1 that corresponds to it in the same row
and Finally give me the sum of both corresponding numbers.
IE.. the number in B3 is found in Q13 which uses the number it corresponds to in R13. The takes B1 finds it in T16 and uses the number it corresponds to in U16.
Then returns the sum of R13 and X16 into B5. (B5 should show 176)
View 5 Replies
View Related
Jun 27, 2008
formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula
View 9 Replies
View Related
Dec 22, 2011
I try this method but data entry not allowed
Here is a scenario to protect
formula cells but allow access to data cells:
. Select all cells in the worksheet by clicking on the top left corner of the sheet.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Uncheck the Locked and
Hidden check boxes, then click OK.
These four steps will make all cells in the worksheet unprotected and can therefor be modified. Next step is to
protect the formula cells, and here is how we do it:
. Select all cells that have formulas.
. On the Format menu Click Cells.
. Click on the Protection tab.
. Check the Locked and Hidden check boxes, then click OK.
. On the Tools menu click
Protection and select Protect sheet
View 7 Replies
View Related
Sep 27, 2006
I am trying to write a macro which highlights all the cells with any reference to other cell. The macro should highlight a cell with formula like = 3 * A25 but not highlight a cell with following formula. = 3 * 0.4535566. 'Hasformula' property is not useful here as it can't distinguish between above examples.
View 3 Replies
View Related
