Lookup Related Detail Using 2 Columns
Mar 5, 2008
I want to write a formula so that if an item in column A = " name" and column B = " date" (name and date to be selected from a drop-down box), then it will return the adjacent cells text in column C.
For example;
ColAColBColC
nick01-Marabc ltd
mick01-Mardeliver it
dan02-Mardiy
nick03-Margo ltd
mick04-Marfreebie
dan03-Marhelp ltd
If I select "nick" and "3/3/08", then the formula will return "go ltd". I know how to do filters, my aim is to create a master sheet and then for someone else to be able to select their name and a month. The month will then update a mocked up calendar (i.e. a 7 by 5 sheet where cell A1 = Date selected, A2 = Date+1, A3 = Date+2 etc). I've attached a basic mockup of what I want to do. If I select nick and march, the yellow highlighted cells should populate in C9 and E9 with abc ltd and go ltd, the others blank.
View 3 Replies
ADVERTISEMENT
Feb 26, 2008
I am trying to track a score. A master capacity sheet has two columns with identifiers (upstream and downstream nodes) relative to a pipe segment. I have a separate sheet (condition sheet) with the corresponding scores. I want to enter the upstream and downstream node numbers in 2 columns (A & B) in the master capacity sheet, and based on the the upstream and downstream node numbers, have the corresponding score (from the condition sheet) on that row copied in the master capacity sheet.
View 6 Replies
View Related
Jul 18, 2006
I've tried LOOKUP, HLOOKUP, VLOOKUP, MATCH, SUMIF, ETC... Nothing working correctly. Here's what I need the function/formula to do: Find a # number value in column C, and then whatever text in another column ( range) I specify (like column A) corresponds with that same ROW. For example: VALUE: $40.00
1. Search column C3:C40 to look for $40.00- let's say it finds the value $40.00 in cell C21.
2. In cell A21 there is text "Gena's Sales".
I want the new cell the return a value of "Gena's Sales" because the $40.00 is in the same ROW as "Gena's Sales."
I think the reason I'm having such a problem is because it's numbers and text combined, and also LOOKUP does this "ascending order" thing that screws up the output. I'm getting values with LOOKUP from column A, just not the right ones. I don't need any ascending or descending orders. I just need to find the value in a range, then find the text in another range in the same ROW.
View 2 Replies
View Related
Dec 1, 2008
I am trying to do a multiple compare.
First I have a table that has these headings with data..
Name, Level, Req1, Req2, Req3, Req4
Bob, 2, 423, 506, 649, 798
Fred, 4, 601, 799, 1023, 1547
In another section I have..
name Drop down, level drop down. with the 4 Req.
Basically, when you select Fred and 4, I want to to display 601, 799, 1023, and 1547 in the columns to the left of where you do the selection. (which is not in the same place as that table at the top of this question.
View 9 Replies
View Related
May 15, 2007
I have a large order spreadsheet with 3 date related columns. One for customer requested delivery date, one for factory quoted delivery date and one for actual delivery date.
I want to be able to set something up that if it gets near either the requested or quoted delivery date (by say a week), that the cell flags red so it's a visual reminder to check whether the order has been dispatched etc.
View 9 Replies
View Related
Nov 28, 2007
I have a long list of 2 columns containing data as follows:
col A col B
country1 date1-1
country1 date1-2
country1 date1-3
country2 date2-1
country2 date2-2
country3 date3-1
country4 date4-1
country4 date4-2
country4 date4-3
which I would need to move to get one row per country in column A with all related values from col B in multiple columns on a single Row, e.g....
View 9 Replies
View Related
Apr 7, 2014
I have a table of data (say Column1 to Column 5) with multiple rows.
Column 1 to 4 will have the lookup values in multiple rows and Column 5 data should be picked up using vlookup or other lookup function.
I managed to somehow bring all these lookup values in (Column 1 to 4) in a single column in another sheet. I am now trying to use some lookup or other functions to match this single column and pick column 5 data in original sheet. Result i am expecting is lookup value in first column and next to it column 5 value.
It is basically a lookup wherein lookup value is spread over multiple rows and columns and result column is fixed. I tried using vlookup, but lookup value column and column number had to change every time when i moved from column1 to 4.
View 3 Replies
View Related
Mar 10, 2009
I have a list of names and corresponding costs charged for each entry. The names appear multiple times for multiple costs charged, but contain any combination of characters after their name.
I am looking to get the sum of all entries for each name. I have tried the following formulae, but it does not return any information:
=SUMIF('Jan line items'!E:E,(CONTAINS:A:A),'Jan line items'!F:F)
View 3 Replies
View Related
Jul 29, 2006
Is there any way to capture the comment that comes when we use "Track Changes", the date & time when the cell content changes should be retrieved in the adjacent automatically. Is there any way to do it using formulas/ VB code.
View 7 Replies
View Related
May 8, 2007
I have two sheets....one sheet has a all the detail I need on it. On the second sheet I am trying to pull all the detail by category but want to eliminate any zeros in between. Anyone know how to do this?
I feel like I am being very vague so if I need to give more detail just let me know.
View 10 Replies
View Related
Sep 18, 2005
I am trying to re-jig a formula that shows hours worked for a particular
day, to showing any absence detail shown for a particular employee. I have
the fllowing formula which doesn't work and I'm not sure why. A similiar
formula works for me getting basic hours worked etc
=SUMPRODUCT(--(DailyDate=F67)*(StaffNumber=$C$11),(AbsenceDetail))
My range names are correct and all have the same 'length'. F67 is 09/05/05;
StaffNumber = 100 and the value that is within the AbsenceDetail cell for
the 09/05/05 for # 100 is Holiay, except my formula returns 0 (I have it
formatted as General)
View 14 Replies
View Related
Aug 25, 2013
I have a spreadsheet that has detail and summary info. I want to update the summary with the detail. I currently have the summary next to the detail, but I may move it to it's own tab.
View 6 Replies
View Related
Jul 9, 2009
i'd like when i lookup abt specific in range (4 Columns is my lookup range)
get the results in one column with skipping blanks Cells "Note" Blanks cells it contains fromulas with "" so it blanks but have formulas ....
View 9 Replies
View Related
Jun 17, 2013
Recently our IBS provider change something in their data. As a result after the export I can no longer use
HTML Code:
Set PTCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=Range("A1").CurrentRegion)
I had to change the SourceData to:
HTML Code:
Set PRange = WSD.Cells(1, 1).Resize(FinalRow, FinalCol)
Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
xlDatabase, SourceData:=PRange.Address)
Since I have done this when I get to this statement
HTML Code:
Cells(FinalRow - 1, FinalColumn).ShowDetail = True
It no longer creates "Table1". Which sends a Subscript out of Range error on this statement
HTML Code:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=10, Criteria1:= _
">0", Operator:=xlAnd
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=12, Criteria1:= _
"
View 1 Replies
View Related
Nov 19, 2006
When you double click on a number in a pivot table data area you get a new sheet with that information showing the detail, is there anyway to "trap" this action?
I want to run a macro on the detail data but can't see a way to do it automatically, have tried, BeforeDoubleClick, PivotTableUpdate, NewSheet events but all have there draw backs if you are doing something other than showing the detail for the pivot table. Of course I can just run the macro after the sheet is made but it would be nice to do it on its own.
View 9 Replies
View Related
Dec 9, 2008
I have recorded the below macro to select a pivot table field (on demand) and show the information on anthother sheet tab.
Obviously if the order of the fields change then the macro is buggered, any ideas so it only selects the on demand field and shows that info.
Sub Macro1() ....
View 9 Replies
View Related
Jul 27, 2004
When you create a pivot table and double-click on the output of the pivot repot you access the detailed info that make up this particular number.
Is it possible to preserve the format of the raw data file that has been used to create the pivot table output?
For instance, the raw data has $. When I create the pivot table I can format the table to reflect the $. However, when I double click on the pivot table number where I have the $ I access the detail info but the format (in this case $) is lost.
I called Microsoft and told me it is a flaw in Excel. But, I'd like to check with you before I give up.
If I can't do that. I have an alternative: double click on all the numbers of the pivot table then format the sheets to include the $ and then create a hyperlink from the pivot table to the detailed report tab.
View 9 Replies
View Related
May 25, 2007
I use the GetObjects function to gain access to the properties of a mailbox and want to fill out a table with some detail information. The code works fine until I have a mailbox which name has an apostrophe included.
Set o = getobjects("winMgmts:!\VS1
ootMicrosoftExchangeV2:Exchange_Mailbox.LegacyDN='/O=TEST/OU=TEST/cn=Recipients/cn=co'brien',MailboxGUID='{59A40254-9F12-4502-9A03-3FB5CB0BBD38}',ServerName='VS1',StorageGroupName='sg1',StoreName='mb1'")
I highlighted the section that causes the problem. The string within the string is encapsulated in apostrophes, hence the apostrophe in the mailbox name co'brien confuses the code and raises an error. Microsoft provides a solution by using escape characters, but that seems to work only in vbscript, not in vba. Here is a link to Microsoft's solution:
http://www.microsoft.com/technet/scr...4/hey1217.mspx
I tried some workarounds that are being used for a similiar problem with SQL statements as well, but again without success - these are the versions I tried:
co/'brien
co'brien
co''brien
View 4 Replies
View Related
Sep 16, 2009
I have a some detail in the tabular form with different criteria as size, colour ,purity,cut ,by combining all these i have price list in a tabular form , if I want to intersect all this and find the price how can i do so
View 9 Replies
View Related
Dec 21, 2011
a query as i'm not too good with vba codes but can record macros but not for what i need.
I have got a pivot table which has about 200 rows. In Column A is the provider and in column B is the total number of people.
I now need to create a sheet per provider and the manual way to do is to double-click on the numbers.
Is there a code that would this automatically?
View 9 Replies
View Related
Mar 21, 2014
This file contains 2 sheet Main M and Detail D.
In Sheet Detail D, When I choose Code, month of Project, it will be shown the result.
By the way, tell me how to delete the Auxiliary column in Main sheet.
View 3 Replies
View Related
Aug 31, 2013
I have an excel sheet containing data as given below, wherein we receive certain report from each department on monthly basis and the same is mentioned as received against each month under header column. We need to remind the department about non receipt of report for particular months which contain empty cells. Macro is required to generate the report of empty cells mentioning the months against each department.
Available Data
Dept
Sep-12
[Code]....
View 3 Replies
View Related
Feb 15, 2007
I am trying to do a (what I thought was a) simple lookup involving 2 columns.
Pls see attached example. I am trying to find the "?" values, by matching column A&B of the input to the Data columns and returning the $$$ column.
I thought this wouldve been a piece of cake but its got me stumped!
View 9 Replies
View Related
Feb 27, 2009
[Excel 2003] I have 2 spreadsheets: one to summarize data from a 2nd detail spreadsheet. I'm analyzing work order information for a service operation.
I'm using dynamic name ranges, as follows, for the detail:
WO_Num =OFFSET('WO Tracking Log'!$A6,0,0,COUNT('WO Tracking Log'!$A:$A),1)
Other detail data is defined as these examples show:
GM_X =OFFSET(WO_Num,0,8)
OpenDate =OFFSET(WO_Num,0,1)
All detail data begins in Row 6 in the detail spreadsheet.
In the Summary spreadsheet, it appears to make a difference where my calcs are located in order for my COUNTIF's to work correctly. As long as I keep my summary calc (to total the number of work orders in the detail) in Row 2 of the Summary, it works fine...but if EITHER I move my calc down a row OR if my detail drops down a row because a row was added above (where I have just header info), my summary totals change?!?! I don't understand.
Here are two examples of the calcs I'm using in the summary:
=COUNTIF(WO_Num,">0")
=COUNTIF(GM_X,"X")
Can someone tell me what is going on? What I'm doing wrong?
View 8 Replies
View Related
May 16, 2014
I'm trying to come up with a lookup formula that would look at Column A and Column B then return the matching result from column C. If I entered A in a cell it would return 11, but if I entered 1 it would still give me 11. how to do a lookup with two columns, but didn't get far.
lookup.JPG
View 4 Replies
View Related
Mar 16, 2014
Here is my requirement...
Oct-13
Nov-13
Dec-13
Item name
Cost
MRP
Qty
Cost
MRP
Qty
Cost
MRP
Qty
[Code] .....
I need to find the MRP value of stone clip for nov-13.
View 9 Replies
View Related
Aug 1, 2014
I have read through other similar topics and tried using the index and match function to lookup but been unable to make the formula work.
I am trying to use the lookup function to pull data from a table using criteria in two columns and one row. I have attached a spreadsheet for example.
I want to use criteria of Day (eg . 1), Total height (cm), and jar # (eg. 1). I inherited this table from a previous coworker and don't have time to organize the data better.
In the spreadsheet I want to return the value of 8.8 (highlighted in yellow). The criteria I want to use to lookup the value are highlighted in red.
View 4 Replies
View Related
Apr 1, 2009
Im trying to return the number value of how many contracts have been signed in a particular month. So in my example I want to look pick out say "IM" and check to see how many of those types have been signed in say "Jan-09" and return that as a number. Have tried various mixes of count, sum, countif and sumif but dont seem to be getting anywhere.
View 4 Replies
View Related
Aug 12, 2009
I have been already fighting for about a week with this problem. I attached the Excel-file. I have three input data and as output I want to get also three different data. For Vendor I use the VLOOKUP function. For agreement# I have used (not in this file) CONCATENATE by adding an extra column to the data table and then use VLOOKUP.
The actual problem is to find the price because it is based on the three input data. I can't seem to find a function for it. I've tried INDEX, MATCH, IF, nested them for as far as my knowledge reaches.
The problem lies for as far as I know in the del.date cell. For example, input date is:
Client#:912345, Product:E569831, del.date: 16.7. (July 16th). This input date should return cell G12.
View 3 Replies
View Related
Nov 16, 2012
A
B
C
Ap1
queens Carrots
Goja Apple
Ap2
fujja Apple
fujja Apple
Ca23
Goja Apple
queens Carrots
I tried to write a function so that it would search for a word from Column C in Column B, and then when it found it, give me a a combined result of Column A and B. For instance. I lookup up C1 (Goja Apple) in the range of all of column B; the function finds it in B3 (Goja Apple); then it returns the combined result A3 and B3 (Ca23 Goja Apple). I tried the =lookup, but I think the first column you select must be in ascending order, and that wont work for me.
View 3 Replies
View Related
