Worksheet 1: In column A I have a people's initials. In coumn B I have text boxes with miscellaneous text. (The same person could have multiple rows within this sheet.)
e.g. AAA blue BBB orange AAA round CCC smelly AAA elongated
Worksheet 2 I want to show: A2 = initials, B2 = first text box associated with that person, C2 = second text box (different row) associated with that person (if applicable), D2 = third text box (different row) associated with that person (if applicable), etc.
e.g. AAA blue round elongated BBB orange CCC smelly
I need to look up a value from a worksheet using multiple criteria, and return MULTIPLE results.
I have a table with comments written by different people about specific items. I would like to be able to type in that item name and return all those comments. BUT - it's not JUST the item name, but also on a specific date or some other criterion.
Currently I have managed to do this successfully with a single criterion, as in this array formula:
I then copy that formula to a few more rows and I indeed get a list of different names (F1:F1001) which match my criterion "ABC". But - I have at least one more, or even two more criteria. I've tried a whole lot of different things and I just get errors.
Has anyone tried something like this? If you have - how should I modify the above formula to allow for additional criteria? For example, not only A1:A1001 equal to "ABC," but also B1:B1001 equal to "XYZ", and so on.
I have attached a sample spreadsheet for your review. I think I need to combine multiple IF statements into one single statement. See column I:
There is currently a formula in Column I that looks at Column H and computes a ROUND formula based on this information. Unfortunately, this formula only works if Column J says "MB". If Column J says "Minutes" then I need Column I to compute with this formula: =IF(H2<30,0.5,ROUNDUP(H2/60,1))
How can I combine this with the current formula to get what I am looking for? Here is the current formula (for MB): =IF(H2<0.125,0.125,ROUND(H2,4))
I have attached a copy of my monthly work schedule. Sorry if it looks too noobish but I am. The question I have is on the schedule right now, it uses to of the same schedule, one is called request off and the other the final. The request off right now is where all changes are made, and they reflect on the final. I did it this way because I could not figure out how to make the cells autofill according to the day of the week, like if it was friday i needed it to fill in the shifts and normal days off automatically.
What I am trying to do now is eliminate the need for the second schedule, the request off one. I think I have figured out how to have the cells autofilled according to the day of the week, and also let the user enter in other stuff such as sick, vacation or whatever, and have it autofill if the cell is left blank, using vba. this is what i have so far, and it is working I was just wondering if it was possible to set it up for a range of cells instead of having to enter each one in manually.
If my input page B13 is either New Lease In-House or New Lease w/Co-Borker andmy input page B43 is - then take cell D57 in my Analysis report, mulitply it by cell B32 from Input page and divide it by 2, otherwise, take cell D57 on the analysis report, mulitply it by B42 on Input page and divide it by 4. I think I have a mess up with my brackets. This is what I have: =IF((OR('Input Page'!B13="New Lease In-House",'Input Page'!B13="New Lease w/Co-Broker"))AND('Input Page'!B43="-",'Analysis Report'!D57*'Input Page'!B32/2,'Analysis Report'!D57*'Input Page'!B32/"4")
I want to create a formula that will turn B2 in floors if A2 has /1 with its formula, Ceilings if it has /2, walls if it has /3, and M&E if it has /4. For example 456-401/2-1569 in cell A2 will populate ceilings in B2. I know I can create If statements but I can only figure out how to do this to search for one text and populate one word. I know Vlookup is possible but this spread sheet will be blank and as people populate there information I need the cells to automatically populate the data into the B column. how I can create the right formula?
I have a userform with 14 independant checkboxes on and would like it when the operator presses the 'go' button for the code to look at each box and if ticked put a "Y" in the column on a spreadsheet associated with that checkbox.
I have this portion of code set for each checkbox within the sub function, but it's a lot of code for something relatively simple:
I have are two listboxes set up, one with all the states, the other with 7 possible variable selections. So in my case I have 371 (DC, Virgin Islands and Puerto Rico are included as states in this) possible combinations here. With each combo the user needs to be able to enter up to 2 years of quarterly data (8 quarters, so 2968 pieces of data possible) and then press a button to save that data to a specified location which I will need to access at run of the overall macro.
Is there an easier way to do this then to simply assign each combination a cell? Maybe an If State = AK and Variable = TUR then find column = AK & row = TUR (per quarter) type solution?
I'm using one spreadsheet "Take Off", to pull data from a cell which dictates an objects shape, based on this shape I have a very large IF statement that pulls data from an adjacent cell where an objects dimensions are entered, the statement than proceeds to use these dimensions to calculate the weight of the object.
I've run into the problem with only 7 IF statements, I have gone online and tried to figure out how to avoid this since it is a common problem, but I can't figure out how to do it, most of the examples show very simple and basic formulas. I am unable to figure out how to translate this into my IF statement where I make reference to many cells as well as us differant formulas within the IF statement.
My latest solution was to breakdown the IF statements and try to my my own functions but I think I am too green to have done this properly. Any guidance would be greatly appreciated.
The following is the code I'm working with followed by how I tried to remedy the situation:
and so on until hCalc, but when I put them together in a cell like this: =aCalc()+bCalc()+cCalc()+dCalc()+eCalc().... I get an error #REF.
I realize a problem might be that I'm referencing a specific cell within my formulas, and many examples online show cells referenced in the form =A$1$, is there a differant way for referencing a cell, because currently I rely on Excel to automatically do this by way of relative reference.
ADDED: Ok I scrapped making the functions because I'm not sure and I did some research on VBA so I tried this one:
Function Weight(Shape As String, Amount As Double, Dimension As String, Optional Length As Variant) If (Shape = "BPLT" Or Shape = "L") Then If (Amount > 5) Then Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 + Get_Word(Dimension, 5) / 1000 * Get_Word(Dimension, "Last") * 7850 Else Weight = Get_Word(Dimension, "First") / 1000 + Get_Word(Dimension, 3) / 1000 * Get_Word(Dimension, "Last") * 7850 End If
But when I try to put Weight(C6,D6,F6,E6) I run into the error "Sub or Function Not Defined"
I need to multiply the result of the 3 if statements as shown below in one cell Each of these 3 if statements output an integer and I need to multiply the value of each of these 3 output integers to get the final output integer. syntax of multiplying if statements outputting an integer?
Here is the first required formula, which will go in cell P7:
If N7>=75 a value of "Y" should be returned. If not, a value of "N" should be returned. BUT if N7 is blank, a blank should be returned.
Here is the second required formula, which is more complicated, and will go in cell Q7:
(1) If Cell P7="Y", then a "Y" needs to be returned in the cell. (2) If Cell O7>=2, then a "Y" needs to be returned in the cell. If not, then a "N" needs to be returned. (3) If Cell P7 is blank, then the cell needs to remain blank.
Here is the final formula, which will go at the bottom of the page.
I need the Q column to return an average of the number of "Y" values, not counting the blanks as numbers. (For instance, if there were 18 "Y"s, 2 "N"s, and one blank, the value returned would be 0.90.).
I'm running a macro with multiple statements for hiding columns, and it has been running well for years, now today i'm getting a error message in the macro while debugging that states "Unable to set the Hidden property of the Range class", and when I reset the macro, and try to manually hide the range, I get the error message "Cannot shift objects off sheet".
I want to have cell C8 in Worksheet1 to equal cells A25-A29 in Worksheet3 and also when cell F33 in Worksheet 1equal the Cells A35-A37 in worksheet3 a calculation is done based on the info in cell D18 in worksheet 1 and if it returns a limit under a set value the set value is returned…... Example;
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A35 in worksheet 3, I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2 then divides it by 1000, but if the answer is less then 550 I want it to return 550.
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A36 in worksheet 3 I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2.42 then divides it by 1000, but if the answer is less then 600 I want it to return 600. **** Only exception in this one is when C8 in worksheet 1 = A29, the calculation of D18 is still 2.00 not 2.42)
When cell C8 in worksheet 1 = A25-A29 in worksheet 3, and Cell F33 in worksheet 1 = A37 in worksheet 3 I want it to do an equation where it takes the amount from cell D18 in worksheet 1 and multiples it by 2.70 then divides it by 1000, but if the answer is less then 650 I want it to return 650. **** Only exception in this one is when C8 in worksheet 1 = A29, the calculation of D18 is still 2.00 not 2.42)
I have a data set where a1 is the starting cell and it needs to equal cells b9 or b6 and if the cells in the a column match the data in b9 or b6 it should be assigned an 1 if the cell data in column a does not match the data in cells b9 or b6 it should be assigned a 0. I've tried multiple formulas and have not been able to find the right one
Is what I've come up with so far. However, this returns 0, even though there are 3 items which should resolve the criteria. The main problem is here: IF((AND(APR09!$G$2:$G$500<>Department!$N$29,APR09!$G$2:$G$500<>Department!$N$30))
Where I am trying to make the sum increase by 1 if the cell from the row in question, having made it through the first 3 IF statements is not equal to either of those two cells (a range would be better) then the Sum needs to be increased by 1.