Excel 2010 :: Lookup Multiple Criteria Across Multiple Sheets?
May 28, 2014
I have a Excel 2010 workbook used to rota in a large amount of staff for a call centre, which is split into four teams. Each sheet corresponds to a month of the calendar year eg Jan201, Feb 2014 etc..
What im trying to do is put in a sheet at the front of the workbook that I can select the team, which populates the list of staff in that team and then checking across a specified date range gives the shifts that those respective staff will be working for the set time period (probably be looking at a seven day period and a 1 month period). (This in turn will be printed out to give to the staff members.)
View 2 Replies
ADVERTISEMENT
Aug 5, 2012
I am using MS Office 2010. I want to count---on multiple sheets---the number of times that a given cell is greater than another cell if and only if a third cell is equal to a given value. I want to do this for 4 sets of data on each sheet. I thought I had it figured out with this formula---
=SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$R1"),2*(AND("'"&$H$1:$H$43&"'!$E1">"'"&$H$1:$H$43&"'!$F1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$S1"),2*(AND("'"&$H$1:$H$43&"'!$G1">"'"&$H$1:$H$43&"'!$H1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$T1"),2*(AND("'"&$H$1:$H$43&"'!$I1">"'"&$H$1:$H$43&"'!$J1"))))+SUMPRODUCT(COUNTIF(INDIRECT("'"&$H$1:$H$43&"'!$U1"),2*(AND("'"&$H$1:$H$43&"'!$K1">"'"&$H$1:$H$43&"'!$L1"))))
but it returns a value of zero each time. Clearly there is an error in the formula.
Here is some background:
-- $H$1:$H$43 is a block of cells that has the names of the sheets in the workbook
-- E1 and F1, G1 and H1, I1 and J1, K1 and L1 are the four groups of cells that I am comparing.
In the entire workbook, I want to add 1 (counting function) only when:
R1=2 AND E1>F1 or
S1=2 AND G1>H1 or
T1=2 AND I1>J1
U1=2 and K1>L1
on each appropriate sheet in the workbook.
View 1 Replies
View Related
May 6, 2014
I would like a macro to be able to save 26 tabs within the one document to individual PDFs.Preferably I would like to be able to specify each time exactly which tabs get printed, because often I don't need to print all 26, just the first 10 or so.I would like each PDF to automatically be named with the value in cell E10 of each tab.E10 already has a formula to create its final value. It references cells from other tabs within the same document. Hopefully the fact that this cell has a formula in it won't affect my ability to use the resulting value as a 'save as' reference?I would like it if the PDFs save to the same location as the Excel sheet from which they're generated is located. The location of the excel sheet will change every three months, so I'd prefer not to specify a location with a specific filepath, as it will have changed by the time I run the macro again.
I am using Excel 2010.
View 5 Replies
View Related
Feb 9, 2014
I have saved this on a 2010 workbook as I am at home but this will be used on a 2003 workbook.
I have several projects on one spreadsheet which multiple users will be working and I am trying to create a summary sheet of the work carried out.
Each user is expected to carry out a task on each row of the data held in each worksheet (research, call, update etc) and each task (Option 1-5) is assigned a value. Each user is expected to meet a certain level of points per day to calculate productivity.
I am looking for a sumproduct along the lines of the summary sheet attached but mine just takes one sheet into consideration and I need one for all sheets.
View 12 Replies
View Related
Aug 20, 2014
I am using excel 2010.
I have a spreadsheet with the following:
Column E is a product. If that product is ordered, any character is entered in that cell
Column F has a due date
Column I has the received date
What I want is to count the number of cells that have any character in column E AND the received date is later than the due date
These two formulas are working fine alone but I cannot get them to work together.
=SUMPRODUCT(--(F:F<I:I))
=SUMPRODUCT(--ISTEXT(E2:E1000))
I have tried all kinds of tweeks to the following to no avail:
=SUMPRODUCT(--(F:F<I:I),--(ISTEXT(E2:E1000)))
View 4 Replies
View Related
Apr 8, 2014
I have a sheet that contains a Microsoft Query (SQL) data connection. In cell C3, I have the month name and I also have a year cell (C4), which contains just the year based on what the current month (=YEAR(TODAY())) is.
For this example let's use May (C3) and 2013 (C4)
C3 and C4 are used as parameters for the SQL query, so if I change the month to January and the Year to 2010, data is refreshed to bring in that data.
Works smashingly.
I then want to add 11 more sheets (12 total), so each sheet is a month. The value in cell C3 on each sheet will be the month name.
If I just copy this sheet 11 times, Excel adds 11 extra data connections; 1 for each sheet. As the query in each sheet needs to be exactly the same, I don't want this. I want the 12 sheets to use the same data connection, only with the specified parameters at the sheet level.
Otherwise, if I need to add or remove a column, or change a column name, rather than just doing it once, I'd have to do it 12 times.
This can't be right, surely, as that would be the most ridiculous design flaw I've ever seen in a piece of software. How can having the same query 12 times be a good thing?
I'm a bit concerned that googling 'excel data connections multiple sheets' always wants to be 'excel multiple data connections one sheet' - I want 'excel multiple sheets one data connection'
View 1 Replies
View Related
May 16, 2013
I am trying to do a conditional formatting based on the result in one cell but it needs to add up multiple cells first to determine which conditional format it needs to use. I am not sure whether I need to do a formula conditional format or whether to use cell is greater than with =sum() in it. I have tried both but neither seem to be working correctly. I have attached an example of the spreadsheet and the conditions I am trying. I am using 2010 version.
Basically, the closing amount for Monday in prod 1 (cell c4), needs to display in red if sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4) & Fri (R4) is greater than that value or display in amber if the sum of the cumm value for Tuesday (L4), Wed (N4), Thur (P4).
I need to complete the same for the whole column for Monday (col C) for each appropriate product. Then the same for column B but this will sum different cells but the principal will be the same.
View 3 Replies
View Related
Feb 10, 2014
I'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies
View Related
Aug 11, 2013
Excel - 2010
I have the following formula:
=SUMIFS(FEB!TRANS_Cost,FEB!TRANS_Cat1,CAT_Main3,FEB!TRANS_Cat2,"Computer Items",FEB!TRANS_Status,"Paid")
TRANS_Cost = B:B on FEB tab
TRANS_Cat1 = C:C on FEB tab
TRANS_Cat2 = D:D on FEB tab
CAT_Main3 = "Everyday_Expenses"
TRANS_Status = E:E
In short the formula adds the total cost of anything 'tagged' as Everyday_expenses, computer items, paid. I want to adapt it so that it takes the value from a defined cell such as Month, so that data can be pulled by month.
View 2 Replies
View Related
Apr 25, 2014
I would like to shade my cell yellow if the priority equals A or A-NEW and ACV=o.
In the data below, items 4 and 13 would be shaded yellow.
I am using Excel 2010.
Priority A, B, C
Consumer UPC
Item Description
ACV Weighted Distribution
[Code] .......
View 8 Replies
View Related
Jul 31, 2014
I'm basically working from 3 sheets for this so I'll start with an example of the data I'm using:
1st sheet:
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
I have 180 rows of data like this one in the first sheet
Second sheet(named sheet 1) is not used for this
Third sheet(named sheet 2):
table.tableizer-table {
border: 1px solid #CCC; font-family: Arial, Helvetica, sans-serif
font-size: 12px;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
[Code]....
Basically I'm trying to find column #3 value in my third sheet and return the second column value. Problem is that the data is located more than once in the third sheet so I need the value of each one of them. So, with this example, Q5942X is located twice in the third sheet and each time, it has a quantity of 2. I would need either to return the value 2 twice horizontally or even better, add the two together. The first sheet, the example is row #45.
This formula should be in the column following "majoration".
I am using excel 2007 and windows 7.
View 3 Replies
View Related
Jun 19, 2014
Using Excel 2010, I am trying to do a Sumproduct formula with two criteria, one of which needs to ignore text values.
Here is the set up:
Column AColumn BColumn C
(Side)(Qty)(Price)
Buy5,51215.67
Sell119,428null
Buy24,20945.77
Sell20,05412.25
...
I'm trying to find the sumproduct of Qty * Price if the side equals "Buy" (or "Sell") but ignoring the "null" value in column C. The formula I have is =SUMPRODUCT(--($A$2:$A$20="Buy")*IF(ISNUMBER($C$2:$C$20),--($B$2:$B$20*$C$2:$C$20)))
The result in the cell is 0, but if I open the Insert Function dialog box, I see the correct value being returned.
View 5 Replies
View Related
Apr 25, 2014
I am trying to write a macro in VBA excel 2010 that compares 2 sheets.
The macro should be something along the lines of if column 7 on sheet 1 = column 1 on sheet 2
AND
on that same row if column 6 on sheet 1 = column 3 on sheet 2
highlight green
** also on sheet1 there can be the same batch ID so if it is the same batch ID it needs to calculate the sum and look at that amount...
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] .......
Results >
Sheet1:
settleid
min Tran Date
Payment Vehicle
total Deposit
total CF
total MRI
RMBATCHID
475-T
03/03/2014
Connect
$562.95
$19.95
$543.00
6G000001450835
[Code] ..........
View 9 Replies
View Related
Mar 14, 2012
I have a sheet (see Sheet 1) from a report we run which lists the following information: Personnel Number, Amount, Wage Type. This is generated for 1000's of employees, with each personnel number being repeated several times in column A.
I am trying to pull specific data to another sheet (see Sheet 2), which would ideally generate the sum of "Amount' for a specific wage type for each personnel number. The issue is is that there may be dplicates of the wage type for each ID number (which is also repeated).
For example, the total salary amount on sheet 2 for ID#12345678 would be 0, while for #9876543 it would be 1250. Is there a formula I could use on sheet 2 column B that would generate this?
Excel 2010 ABC1Personnel NumberAmountWage Type212345678550Payment312345678400Overtime412345678300Overtime512345678250
Payment612345678750Vacation798765432800Salary898765432250
Payment998765432100Overtime1098765432450Salary1198765432300Overtime
Sheet1
Excel 2010 ABC1Personnel NumberTotal SalaryTotal Overtime212345678398765432
Sheet2
View 3 Replies
View Related
Apr 11, 2007
After going through multiple threads in the forums, I got this code to do a multiple VLOOKUP method.
=IF(E2+F2=0,"",INDEX(C2:C10,MATCH(1,(A2:A10=E2)*(B2:B10=F2),0)))
It works perfect on a sample sheet. But when im trying to implement it in a sheet with too much data, it always fails.
I have attached the sheet I am trying the formula on. I have grayed down the columns which needs formula's. The data is picked out from the second sheet.
This is how I have modified the formula to suit me..
=INDEX(Data!$G$2:$G$663,MATCH(1,(Data!$A$2:$A$663=$C2)*(LEFT(TEXT(Data!$E$2:$E$663,"mm/dd/yy hh:mm:ss"),8)=$I2),0))
<<Please note that all the dates and numbers in the sheet are in "text" format for ease of use>>
View 9 Replies
View Related
Oct 21, 2006
I am trying to solve a problem. I am currently using this formula
= SUMPRODUCT(--(Sheet2!B2:Sheet2!B300="MARKETING"),--(Sheet2!D2:Sheet2!D300="200612"),Sheet2!E2:Sheet2!E300)
This formula works for me as it is but I would like to add more months to 200612. I want this to also be 200701 and 200702. In another cell there will be up to 10 months. Is there a way to do a Vlookup or something that will look up these months in another table, rather than keep typing them out in the formula?? Otherwise my formula will be very long.
So the info looks like this in excel
MARKETING 200612 -10
MARKETING 200701 -25
MARKETING 200708 -50
ECONOMICS 200709 -30
The info goes on and on. The two variables are the MARKETING column and the month column. My problem is that I would like a seperate table that can be the months. So 200612 and 200701 is one table, and 200708 and 200709 is another table. The table changes often so I dont want to mess with the formulas, rather a table.
View 5 Replies
View Related
Jun 24, 2014
I've attached a sample sheet to this message.
What I'm trying to do is grab data from the raw data sheet and paste it into the master sheet based off of certain criteria. I want to list any accounts that are not correct.
For example, I want to look through the Raw Data sheet, find the account that contains campaigns which are not correct, and then list the name of the account on the Master sheet. I only need the account name listed once on the Master sheet.
Sample Data.xlsx‎
View 4 Replies
View Related
May 5, 2013
I have a spreadsheet that has about 50 sheets and a summary sheet at the front the layout of the summary sheet is column A has skill sets from A4 to A20 and across the top it has week commencing dates from F3 to about BA3 (dates go from w/c 06/05/13 to 08/12/14). On the other sheets they have the dates in the same columns but the skill sets vary as to each project, some projects may have two of the same skill set in column A because there is two people needed who can do the same thing, I need to total up the days over the sheets for each week and skill set.
I have been using the SUMIF formula as follows:
=SUMIF('Sheet2'!A8:A10,'Summary'!A4,'Sheet2'!F8:F10)+SUMIF('Sheet3'!A8:A15,'Summary'!A4,'Sheet3'!F8:F15)
And so on for each sheet and then change the formula for the next skill set and then again for the next column.
Is there a way of doing this without having to enter a formula into each cell, at the moment I am coping and pasting the formula into word to do a find and replace to change the SummaryA4 to SummaryA5 etc and then into the next cell for the corresponding date.
So to make this seem like it makes any sense I am looking for say an 'Administrator' in all the project sheets over all the dates and for it to add it up for the summary sheet. Under the corresponding week against the skill set.
So it would be F4:F20 for w/c 06/05/13, G4:G20 for w/c 13/05/13 and so on until all skill sets.
View 5 Replies
View Related
Jun 17, 2014
I need to convert data from column IDS into separate rows, all other columns need to stay in tact. There are several distinct patterns for the IDS column, main identifiers are always starting with FILER or TEAL and the trailing numbers behind it have no more than 6 digits.
BEFORE MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911**/**FILER123188 ^** FILER877119*118
[Code] ........
AFTER MACRO
ID
AREA
TYPE
CLASS
QTY
IDS
1
COAL
TYPE9917312
CLASS881345
2
FILER756911
[Code] ......
What the MACRO would look like? This is for Excel 2010.
View 3 Replies
View Related
Jul 2, 2014
I have attached a test workbook excel 2010 (ignore ref# errors, I've cut the workbook down for uploading purposes) What I would like to do is have a 'Button' on my 'information Sheet' which when clicked would clear certain cells. I have searched the forum but can't find a solution, everyone seems to want to delete rows or columns but I just want to clear certain cells. The workbook will have 11 sheets each named 'caravan 1' through to 'caravan 11' The uploaded test workbook only only has 3 sheets.
On 'caravan 1' (which is slightly different to the other 10) I want to clear the content of cells
B4 & B5
C4, C22 & C41
D4 & D5
E4,E5, E22,E23,E41 &E 42
On all other 'Caravan sheets' I want to clear the contents of cells
D4 & D5
E4, E5, E22, E23, E41 & E42
It would be icing on the cake if it could give a warning such as " are you sure you want to clear these cells" but that isn't really necessary. The worksheets will be password protected, but the cells mentioned above will not be. If it proves too difficult to clear all the cells on all the sheets with one click, then perhaps a simpler solution might be to have a button on each sheet instead
View 8 Replies
View Related
May 6, 2014
I have a schedule that covers 3 shifts for the entire month. I have another sheet that is used for crew sign in that I want to auto populate with all the people on that shift for that day.
I need to be able to use 2 different cells on the sign in sheet for reference for the formula (one for the day, and the other for the shift. ex. Day 3rd, shift D). I'm wanting to use an array formula to accomplish this. I've uploaded a stripped schedule as this is for the military and can't have any data on it that pertains to what or who it's for.
BTW our current method is to due it manually for every shift for every day that wastes about 12-16 man hours every week. I have excel 2010 at work currently. Access is out of the question and Macro's/vba may or may not work as security is always being increased on our systems so either a single or multiple formula is the route I'm looking to do that will work the the next few years I hope.
View 6 Replies
View Related
May 3, 2013
I have two workbooks of clinical data: one main of 1,400,000 rows and one small of 30,000 rows. What I want to do is compare the entries in small to find out whether they exist in main. All I need is a TRUE/FALSE result.
Eventually I want to run a comparison of :
Unique number, surname, firstname, DoB and gender. (amusing, excelforum will not allow me to type ***). For now I am just trying to figure out how to do it with the unique number.
I have got the hang of using the vlookup function, but have now found that it cannot search across multiple worksheets. Main is spread across 16 worksheets.
View 2 Replies
View Related
Mar 12, 2008
I have a workbook that has separate tabs for each month. In cell D1 in each sheet is the first day of each month (1/1/08) then in E1 (1/2/08) until the end of the month.
In each sheets - the total for each day is in D30 then E30, etc...depending on which column the last day of the month ended up in.
Then I have a "Report" on using its own worksheet in the same workbook. I would like to be able to type in a date in cell F4 "Report" tab and then for cell A9 "Report" tab to be able to search through JAN D1-?, then FEB D1-?, then MAR D1-?, etc. until it finds the date. Then I would like for it to return the corresponding value found in D30 or E30, etc.
View 9 Replies
View Related
Nov 23, 2006
I'm having trouble looking up text from several worksheets. I've tried hlookup;
=HLOOKUP("H1", '1'!B6:O32,27,FALSE)
but get nowhere when text is in different sheets. I've attached simplified sample... lookup "H1" - holiday1 on sheet1 and return date to "Holiday" sheet. I've tried vlookup across worksheets code, but can't figure out how to customize.
View 2 Replies
View Related
Mar 9, 2009
I'm trying to figure a formula that provides a numerical value when certain criteria are met across a range of cells. The criteria are numerous though and here is where I'm having trouble.
For example, the formula needs to look at Cell A5, then at a range of cells (C5,G5, E5, I5) and depending on the value of those cells provide a predetermine result in Cell K5.
For example, IF (A5 = 'A', and C5=1 or G5=1 or E5=1 or I5 = 1, then P5 must = 15), or if (A5 = 'A', and C5 or G5 or E5 or I5 = 2, then P5 must = 10), or if (A5 = A, and either C5 or G5 or E5 or I5 = 3, then P5 must = 6), or if (A5 = A, and either C5 or G5 or E5 or I5 = 4 then P5 must = 2), or if (A5 = A, and C5 or G5 or E5 or I5 = 5, then P5 must = 1)…….this needs to be duplicated for a list of possiblities.
View 6 Replies
View Related
Dec 26, 2012
I'm making a schedule grid has varying start & stop times. I've made a table so the people using it can check that there is overlap when they are scheduling themselves and the members of their shift.
The rows have the employees and the columns has the hours, (one row for each hour in the year 2013)
Each employee has a 1 under each hour where they work and they can easily see where their shift overlaps onto the next persons and by how many hours. I also have a row on the bottom that checks how many people are on at any given time so everyone can check that we have a minimum number of people on and a maximum number as well.
As it is pretty difficult to look at this grid as it is, i made a 2nd table which we could print to see which days we shoud come in and what time. (with each column being the day/ date and the cell having the time frame one comes in.)
The problem i have is that if any adjustments are done to the first table, (with the hourly columns) it isn't reflected in the 2nd table.
I've created a LOOKUP table with each hour/ shift, but i do not know how to have lookup check the starting hour, the ending hour, and what date it is.
I was considering placing a look-up formula in each cel for each employee for each day for the entire year, i just don't know what the formula should look like.
View 2 Replies
View Related
Dec 27, 2012
I have a spread sheet with multiple columns. I need a formula to look at criteria from three specific columns in the spread sheet and spit out a description from a key I created. Here is an example.
This is the key I created:
Record Kind
Group
Group Name
Category
[Code] .....
The spreadsheet has the same columns so I need a formule to look in the spreadsheet and if it sees each combination as in the key to spit out the category name from the key.
View 1 Replies
View Related
May 17, 2013
I have attached a demo spreadsheet to define the problem.
I wish to populate column K (Risk Rating) with data retrieved from the table, based on the corresponding information from Columns I & J.
e.g. I5 + J5 = Short term illness or injury + Unlikely. This corresponds to cell E9 (11) in the table so required response in K5 would be 11.
Help Book1.xlsx
View 1 Replies
View Related
Jun 3, 2014
I have a cell that contains a long string of text.
I want to be able to lookup in it to see if any word from a list is in it, if it is to return text dependant on which word is in that cell.
Seems like it should be easy but looking up the multiple values is making it difficult.
View 6 Replies
View Related
Aug 6, 2014
I've attached a sample of the data I'm using.
I have two spreadsheets (the samples for which I have shown side by side in Sheet 1 of the attached file).
Spreadsheet 1 is about 30,000 rows and too large for me to change the formatting and structure.
Spreadsheet 2 is the output I need and the format is required by other stakeholders.
In spreadsheet 1 I want to sum quantity in stock for Type 1, Type 2 and Type 3 for each product and allocate it to spreadsheet 2 according to the month in which the product expires. For example, there will be a total of 92 units of product 413302 which will expire in Nov, 2014. Therefore I want 92 to be placed in cell N6 of Spreadsheet 2.
Unfortunately the product number is not unique - there are multiple sub products in spreadsheet 1 but they all have the same quantities of stock. The sub products are referenced in other parts of the report so I can't consolidate by Product Number. This also prevents me from using the SUMIFS function as it will duplicate the number found in the sub products.
What I need, perhaps in a combination of functions, is to find the first instance of product 413302 in Spreadsheet 1 that is expiring in Nov 14, sum the product types and give the result in cell N6 of Spreadsheet 2.
View 3 Replies
View Related
