Is there a way to make this a bit shorter without altering the result.
IF(OR(AI10="",ISERR(VLOOKUP(AI10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AI10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
+IF(OR(AK10="",ISERR(VLOOKUP(AK10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AK10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
+IF(OR(AM10="",ISERR(VLOOKUP(AM10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))),0,VLOOKUP(AM10,'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$CE$79,F8,FALSE))
I initially had a SUMPRODUCT formula that looked like this:
=SUMPRODUCT(--(ISNUMBER(MATCH('Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$A$8:$A$79,F1:F5,0))),'Q:5yr Branch reviewBalance Sheet[SC2 Apr09 - Mar 10 Actuals.XLS]bze'!$Z$8:$Z$79)
However, I turned the page into a dynamic page where the month can be selected from a drop down menu and changes the column index number in Cell F8. And because VLOOKUP can give me an error result if there is a mismatch, I used that combination of Blank cells or ISERR. In the range AI10:AM10 there should be three lookup values (sometimes only one or two), and empty columns between the three values columns. I also will use this formula like a 1,000 times so, arrays is not advisable.
I've got a worksheet with a 1-row gap in between every two rows, and I'm hoping to delete those gaps. In the attached spreadsheet, worksheet 1 (Initial) is what I'm starting with, and worksheet 2 (What's wanted) is what I hope to achieve.
Now obviously, if it were only 10 lines of actual info, I'd just cut/paste. But, I've got ~950 rows of info.
I think this must be fairly easy to do. I've found various posts here and tips elsewhere that indicate ways to do this, but I've not found one I could get to work.
I have a column with numbers in it. These numbers are formatted as TEXT (because they are 001, 002, etc). This list is generated by pulling the unique values from another column where these numbers are repeated.
In the following example, B has the original data. H has the same data with just the unique values. That is how far I have got. What I now want is what you see in Column I (the unique values with NO BLANKS in it).
B | H | I | ------------------------------------------- 001 001 001 001 002 001 003 002 002 002 002 003 003 003 003
I am aware that I can use an Advanced Filter (not that I manage to get that to work without it generate an erro). But this is not what I want, as I'd like the data in I to be dynamically generated such that if more unique values are added to B then H will reflect this (it does that at present) and then I will also reflect this.
I have attached a workbook where the data in columns D:N is collected from another source using Vlookup. In Row 5 There are three numbers 60, 74 & 93. I need a way of condensing those 11 rows down and getting those numbers to appear in a summary for the week (the yellow box) on the right.
Any way of shortening the attached code. The first part 'Sub Loop1()' works great but I can't figure out how to shorten the second part. As you can see the distance between columns is always consistent i.e. add 5 columns to find the next outcome to work out the next result. Ideally I wanted to somehow loop through the next 5 columns again and again until the cell is blank.
I have data that is being pulled down from an external source into an Excel spreadsheet. The data comes in the form of a 1 for TRUE and a 0 for FALSE. This is in the picture below.
I want to take the "1" or "0" and use an If statement so that if the cell contains a 1 then the word TRUE appears in a second sheet and 0 is FALSE. I only want the 1 or YES rows to be copied as in the picture below.
Pretty much each column in the data that has a 1 or 0 will be turned into words in the second sheet.
i have two excel files which are data and master..both files will be use for salary calculating.. the vlookup formula will be use in master files for dragging the salary data from Data files..the formula as follow VLOOKUP(B4:B225,'D:Salary[Data.xls]AUG'!A$1:F$65536,6,FALSE))
the vlookup working fine to me.. but my problems is i want the data to be auto calculated when they have same value in two columns.. or if the name is similar/match in two columns (one for salary and another one for overtime), the salary should be auto calculated.
Data files will contain of these: a:Employee ID b:employee name c:Employee salary/Overtime
Master files will contain of these: a:Employee ID b:Emplyee Name c:Employee Nett Salary (that will be dragging from Data files)
is there any formula that i can used to combined with my vlookup formula?
I have a formula in a cell which is a lookup on another worksheet:
The worksheet name is Staging, and I want to replace the actual name of the worksheet with the value from a variable. I have created a one cell Name called "WS" and in that cell is where I place the name of the worksheet (comes from some VBA code).
how to replace 'Staging' with the variable WS into the VLOOKUP formula.
For informational purposes, the value of the variable changes based on some VBA code, and can contain one of three different worksheet names.
Working on a college football spreadsheet where 14 people wager $100 per week (just for fun, not real dough) on games using the vegas spreads. The basic function of this spreadsheet was to display the name of the person with the highest monetary winnings. For this, I used IF forumlas since there were only 6 players. Now we have 14 and I've discovered the rule of 7 with the IF formula making my spreadsheet non-functional. I read some on VLOOKUP and even watched a copy trainings on YouTube but I'm not sure that's best for this particular spreadsheet.
Here is a copy/paste of my previous forumla: =IF(A2=Sheet1!C22,Sheet1!C2,IF(A2=Sheet1!D22,Sheet1!D2,IF(A2=Sheet1!E22,E22,IF(A2=Sheet1!F22,Sheet1! F2,IF(A2=Sheet1!G22,Sheet1!G2,IF(A2=Sheet1!H22,Sheet1!H2,IF(A2=Sheet1!I22,Sheet1!I2,IF(A2=Sheet1!J22 ,Sheet1!J2,))))))))
The formula refers to Sheet1 which contains =LARGE(Sheet1!C22:P22,1). This forumla displays the highest value for the current monetary winnings. Anyways, I hope I did a sufficient job explaining the current workings of my spreadsheet and my current problems.
Range B4:B18 contain names and the columns C-F contain dollar amounts.
In row 20 I want to show the name from column B who has the highest dollar amount in the column.
In C20 I entered =VLOOKUP(MAX(C4:C18),$B$4:$F$18,1,FALSE) but rather than returning the name of the person with the highest dollar amount in that column, it returns #N/A. The cells showing the dollar amounts pull the data from another worksheet with an equals formula - can this be the reason?
I can't seem to get the format of the Vlookup formula right for what I am using it for.
I attached a copy of the spreadsheet. What I am trying to do is input a value in cell E7. Then have Vlookup search column L6 to L75 for a match and return the value in column Q next to that match, and put that "score" in cell E9.
I have a spreadsheet that currently includes the following formula:
Col A is numeric output Col B is staff names Col C specifies top ten output numbers from Col A
The above formula goes in column D and It returns the staff names with the top ten output. My problem is that if two members of staff have the same output it formula will repeat the name only one staff member in both rows. Is there any way of altering the above formula so that it will show both staff names in each row?
Objective: To find out which customers order certain items and which customers dont order certain items. Many customers may order the same item eg customer A, B, C, D all order item "4567"
I have 2 worksheets.
Worksheet 1: Showing 30 item codes, item description and customers. Items in col A (A2:A31), description in col B (B2:B31) runnning down vertically. Customer name in cell 1 of all other columns running across horizontally, eg C1, D1, E1.... (C1:GF1). There are 186 customers. (A formula needs to start at C3 and dragged to GF3)
Worksheet 2: Raw data showing customers in column A and items in column B, There are 3,753 rows. Customer in column A are duplicated as the same customer may order a number of items so for eg
I have created a sheet that contains a new diet program, calculated down to the precise calorie required for my training routine. Please see below for an example of one of my daily meals:
I need a formula that will help me to create a weekly shopping list (as the values in the example above will change on a regular basis). So I need to take all values from the from columns A & B, multiply them by 5 and then show me the totals in another sheet.
Similarly, I need to do the same with columns E & F, only they need to be multiplied by 2. The totals then need to be added to give me the required amount (in grams) for the week.
Im trying to build a little database and the closest thing i have come to manage what i want to do is with IF and Vlookup function.
I have 1 "main page" lets call it "sheet1" Then i have nomerous of "secondary pages" we call them "sheet2", "sheet3" etc.
The idea is that on my "main page" im using 3 colums "A","B" and "C". "A" and "B" helps deciding where my VLOOKUP function should find the correct value.
The "A" column is planned to decide in what sheet to look for seach word(which is written in "C" column)
Basicly if "A1" is saying "2" its gonna do my VLOOKUP in "sheet2" , and if "A1" sais "3" its gonna look for my "search word" in "sheet3" etc.
My formula at this point (witch is working for 2 pages) =IF(A1=2;VLOOKUP(B1;sheet1!A1:B6;2;FALSE);IF(A1=3;VLOOKUP(B1;sheet2!A1:B6;2;FALSE)))
This is working perfectly. If i write "2" in "A1" and "car" in "B1" VLOOKUP jumps to "sheet1" lookup "car" and return the value in the second column (in this case 2" and if i write "3" in "A1" and keep "car" in "B1" VLOOKUP jumps to "sheet2" and return the carvalue for this sheet (in this case 22).
Then the problem The problem is ofcourse that if i wanna continue with this formula in the same box, i wanna make it keep looking in more sheets depending on what number i have in "A1" If i put number 5 in it goes to "sheet5" and look for "car" and return valuve.
But at this point the formula is too big for excel.
So i guess my question is. Is there any workaround for this? Can anyone come up how to approach this in another way? (im out of ideas) Or am i doomed and have to learn programming to get my idea to work?
$G$130 refers to a value in that cell which is user defined from a drop down list and in this instance, the value is 162. Therefore, I would expect to see all of the items that contain 162, under the list column. As you can see, it has not returned all of the values relevant to 162. It has missed AVS002, AVS005 C5 ENV and CAL0005 in this example. The ommissions seem random
The lookup is for 00.2014, but this is to fixed. Want to use the same code next year to. So I already defined the variable 'jaar' which the user can choose with a validation. (and next year they set it to 2015).
I thought this code would do it but no luck:
So what would be the correct way? Been shifting with the " " but its only make more and more mess.
I have attached a sample. The formula seems to be correct, as I have the same formula in another spreadsheet and it works fine. As you can see, in A9 I have placed a VLOOKUP formula, but the formula wording stays in A9 when I hit ENTER instead of inputting the answer. The purpose of the formula is to look up B9 on Sheet2 and input the correct customer code (in column B on Sheet2).
Currently I am using a VLOOKUP to return a value, and I am trying to apply a calculation formula to the returned value. Below are both the VLOOKUP formula, and the calculation formula. Each of the formulas works well while in separate cells, however am I able to combine the two formulas into one cell?