Excel 2010 :: Using Data From Two Cells To Match Data Within Another Sheet

Dec 7, 2012

I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.

I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.

View 1 Replies


ADVERTISEMENT

Excel 2010 :: Match 1 Cell And Column In Sheet 1 To 2 Columns In Sheet 2 Return Data From A 3rd

Jul 23, 2012

I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.

My attempt is in cell D2 on sheet1.

View 5 Replies View Related

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

Apr 16, 2013

I am using Excel 2010. I am a novice user.

I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.

pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d

[Code] .........

Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.

pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d

[Code] ......

So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.

reg_[0-9]+_+[0-9]+/d

The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".

Similarly folder paths names can contain "_" so can't split string on this either.

As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.

I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:

Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function

If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?

Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g

26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d

So my table would show the name "data_out_reg" and the range of values 8-32

View 1 Replies View Related

Excel 2010 :: Index - Match With Data In Rows

Jan 7, 2013

Using Excel 2010.

Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.

Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.

I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.

Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.

View 1 Replies View Related

Excel 2010 :: How To Match 2 Columns And Retrieve Data From 3rd Column

Jan 16, 2013

How to match 2 columns in excel and retreive data from the 3rd column ?I have an example here as to what I want to do..

ColA ColB ColC ColD

niki delhi neha
vinay mumbaihardik
kapil bangalorevinay
neha patna
pooja goa
hardik kerala

Here is what I want to do for the above.

ColA and ColB contains a standard information which is supossed to be my reference. ColC contains my queries for which I need information about their place in ColD.

So I need to match ColC with ColA, so as to retrieve the matched data (between ColC and ColA) from ColB to ColD. Following is the way I expect my result to be..

ColA ColB ColC ColD

niki delhi neha patna
vinay mumbaihardik kerala
kapil bangalorevinay mumbai
neha patna
pooja goa
hardik kerala

I received an answer in that link

"=INDEX($B$2:$B$6,MATCH($C2,$A$2:$A$6,0))",

Which when tried, surely worked a few months back. I am now using excel 2010. I tried the same again, but this time it does not work for me. Is there something else to do which has been changed in the new excel 2010 ?

View 3 Replies View Related

Excel 2010 :: Pulling Data From Non-blanks On Sheet

Aug 21, 2012

I have created a comparison sheet and compares my old one from last week to the new one I create for this week.
It populates cells with the differences and shows me the data from the new sheet. But since my they are so massive A5:AZ20,000 so you can imagine how many blanks there are.I want to create an auto summary or something from the non blanks.

Column A has each properties Unique Identifier And the rest is various info for the property. I would like it to return my unique ID and then the columns with new data that has showed up from the comparison some are text and some are numbers.

Also I am using Excel 2010.

View 9 Replies View Related

Excel 2010 :: Automatically Transfer Data From Master Sheet To Others

Apr 26, 2012

I work for an insulation company and we have all of our jobs, completed and in progress, on a master worksheet.

Currently there are 437 rows of data (but will increase), and columns A to N with various bits of data.

Row A is a location field - there are 5 locations currently.

I would like to be able to add a new line at the bottom of the master sheet, and then this automatically identifies the location from column A and which worksheet is it to be copied to and then copies the data from that new row to the bottom of the relevant location sheet.

I would also like to be able to update the data in the existing entries (e.g. when a job has been assessed initially, and then completed, I need to put the dates in) and for this to update on the relevant worksheet.

Each worksheet has the same format (columns A to N have the same headings in row 1, then data to begin in row 2).

I wouldn't say I'm an advanced Excel user (otherwise I wouldn't be asking this question), but I do have a fairly good working knowledge of it. Currently using Excel 2010. Would ideally like to be able to do it without VBA as it needs to be uploaded to Google Docs and for others in the company to access online.

View 9 Replies View Related

Excel 2010 :: Chart Data Labels On Protected Sheet

Oct 4, 2013

I'm using Excel 2010 and I've got a chart on a worksheet and the worksheet needs to be protected. I'm able to manipulate the chart in any fashion EXCEPT the position of the Data Labels. Is this a bug in Excel 2010?

View 3 Replies View Related

Excel 2010 :: Cross Checking Info With VLookup And Extracting Data From Sheet

May 9, 2014

So in my excel document I have it such that on the first sheet (Labeled 'Sorted') the data is set up as follows: LOCATION, EXTENSION, NAMEWith the appropriate data under each header. On the second sheet (Labeled 'Locations_Ext') I have a named range called Locations; it contains extensions from a separate document, names of people associated with the extensions from the document, and their locations on the map. I am trying to have it such that the excel matches the extension number given on the first sheet with the ones that I extracted from the document on the second and output the location into the first column on the first sheet. The code I have set up for that process is:

=VLOOKUP(B(//SomeValue),Locations,1,FALSE)

I then want the excel to cross check the information that I took from the Visio to see if the visio is up to date with the latest info we have received (Which is the data on the first page under Extension and Name). The code:

=IF(C(//SomeValue)=VLOOKUP(B(//SomeValue),Locations,3,False),"Good","Error")

Both codes seem to be giving me this: #N/A as the results for every cell.. I'm using MS 2010

View 2 Replies View Related

Excel 2007 :: Macro - Match Data And Copy Rows To Existing Sheet

Jan 11, 2012

I've already found a TON of threads about this process but nothing that matches specifically what I'm trying to do.

I have a spreadsheet that I'm using to auto fill other tabs with data that only matches specific criteria. Here's what I'm looking to do:

Columns I, J, K, and L may be marked as either Y or N (or blank). I have different sheets that require 1, 2, 3, or 4 of those columns to match Y. For example, on sheet 2 I want to copy the entire row if there's a "Y" match on column I and J. On sheet 3 I want to match "Y" against, I, J, and K. Sheet 4 I need to match only L, etc.

I need the data copied into the existing sheets to start on row 7. I have other data on rows 1-6 that cannot be moved.

I'm running Excel 2007.

View 5 Replies View Related

Excel 2010 :: Referencing Cells Containing Dynamic Data In Another Worksheet?

Jun 15, 2012

I have a a spreadsheet that pulls user information from our corporate address book. I would then like to copy that data to another worksheet for additional data scrubbing. The problem I am facing is that, I can get the reference to work the first time but if I pull new data down, the destination spreadsheet now shows #REF!

I've tried using the below formulas but they all wind up the same way. ' Results' is the name of the spreadsheet I am making the reference to which has the dynamic data in it.

=Results!#REF!
=INDIRECT("Results!$B3")
=indirect (cell("results",B3)

View 1 Replies View Related

Excel 2010 :: Conditional Formatting Ignoring Cells With No Data?

Sep 27, 2013

I'm using Excel 2010.

I have created a spreadsheet with 3 month inspection schedules on using the formula below.

=DATE(YEAR(C50),MONTH(C50)+3,DAY(C50))

I then copy this in to a cell where I want the date to appear... and then in to another cell where I want a further 3 month date to appear. Obviously changing the cell reference.

I would like the formula to ignore the very first cell "C50" if there is no date inputted in this cell.

View 2 Replies View Related

Excel 2010 :: Select Specific Cells In Filtered Data

Nov 1, 2013

How we can select specific cells in the filtered data using VBA in Excel 2010.

I need to select 10th column, 5th row data .. or 10th column, 6th row data .. or 13th column, 8th row data...

How to select this data using VBA.. I am struck here .. If I give the below code.. the hidden cells inbetween the filtered data is getting selected.. only the first row is getting selected correctly.. i.e, ..rnVisible(1, 2).Select .. If I change the row like rnVisible(2, 12).Select its selecting the hidden cells ..

Dim rnVisible As Range
Set rnVisible = ActiveSheet.Rows("2:10000").SpecialCells(xlCellTypeVisible)
rnVisible(2, 12).Select
MsgBox ActiveCell.Address

[Code ........

View 1 Replies View Related

Excel 2010 :: Autofill Entering Data Against Blank Cells

Jan 23, 2014

In earlier versions if I used the double clicked the autofill handle it would only fill down to the next adjacent cell containing data. Now it fills down to the bottom of all the data regardless of whether there is a blank cell or not e.g

X X X Z
X X X Z
X X X Z
X X
X X X

Now this happens

X X X Z
X X X Z
X X X Z
X X ...Z
X X X Z

How can I stop this as I need to enter different data in the next section?

View 1 Replies View Related

Excel 2010 :: Auto-populating Calendar Based On Conditions From Data Table On Another Sheet?

Apr 26, 2014

I'm trying to auto populate a calender style sheet in Excel 2010 based on data from a Work Schedule sheet. The work schedule sheet contains a list of jobs, with each row representing a different job. There is a column for the start date (e.g. 25/04/14) and a column for the end date (e.g. 26/04/14). There are other columns which select resources such as people and vehicles. Each resource may appear on any one of several columns for each row, e.g. Site Operative 1, Site Operative 2 etc.

On the calendar sheet, in which one cell represents one day (e.g. 25/04/14), all the dates are shown along the top going right and all the resources are shown on the left going down.

On the calender sheet, in every cell I want a formula that will look at the Work Schedule sheet and see if that particular resource is being used on that particular date. If it does, the cell can display information from another cell such as the job number or job name to which the resource is assigned; if it doesn't, the resource isn't being used so it can display "Free" or "Available".

View 2 Replies View Related

Excel 2013 :: Highlight Cells If Data In 2 Columns Match

May 27, 2014

Please see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.

The only situation where highlighting would occur is if the title/author pairing appears in another row.

I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?

I'm using Excel 2013.

Pairing example.PNG

View 14 Replies View Related

Excel 2010 :: Transfer Data Into Form - Multiple Rows Cells

Mar 27, 2014

Every day I receive a csv file of NAMES, PLACES, ADDRESSES, IDS, SPECIAL ID (ETID), ITEMS, QUANTITY, AND OTHER INANE INFORMATION. which is a list of people from places that are ordering item(s) for each ETID)

I CONVERT TO EXCEL BECAUSE I HAVE TO CLEAN THE DATA FORMATTING, AND SPLIT A COLUMN INTO 2 (LAST NAME AND ETID ARE TOGETHER).

I need to put the items into a form, one order per ETID.

I tend to receive on NAME, PLACE, ADDRESS, sending orders for multiple ETIDS.

I don't know the easiest or best way to get the info into the order form. I have designed the form in word and excel.

View 1 Replies View Related

Excel 2010 :: Macro Based Search Function Using Data From 2 Cells

Mar 15, 2012

I have been using a macro to search and highlight customer addresses for me, however I have changed my worksheet and now have the addresses in two columns instead of one (D for the numbers, E for street names) for sorting purposes.

What I would like to do is either; find a way to search using data from 2 cells to get a result, or have multiple results highlight and popup.

Using: Excel 2010

Code:
Sub SEARCH_FUNCTION()
Set FoundCell = Sheets("Route").Cells.find(What:=Sheets("Intro").Range("G15"), LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Not FoundCell Is Nothing Then
With FoundCell

[Code] ...........

View 2 Replies View Related

Excel 2010 :: SUMPRODUCT Answer To Return Blank When No Data In Specific Cells

May 12, 2013

I am using Excel 2010 and I am trying to average the amount of days in a month to a daily average per person in my worksheet.

Total sales per person
A5 = 10 - This is the Grand total per person for column A
A6 =4
A7=6

Daily average per person
C5=2.6 - Average for all persons here
C6=2.0
C7=3.0

The formula I am using is:

=(SUMPRODUCT($A$6:$A$15,C6:C15))/$A5

Which gives me an answer of 2.6 in cell C5 as shown above which is what I am wanting.

Please note that my cell range for my staff goes from 6-15 for both Column A and C where the other cells are blank in both columns.

My question is, If I was to clear all the data in both Columns A6:A15 and C:6:C15, cell C5 would return to a #VALUE. How to I change the formula so that if the cells were Blank, cell C5 would also be blank until I enter data for each person again?

View 2 Replies View Related

Excel 2010 :: Access Data Connection Query - Missing Data All Of Sudden

Oct 19, 2012

I created a slick little excel sheet with the data coming in automatically via Access query. It has been working fine for months. Now all of the sudden there are a bunch of cells with missing data. The weird part is it's not as if whole columns are missing data, more like 90% missing. When I go to Access and run the query all cells are populated as the should be. There have been no changes to the query at all during this time.

btw...I am running Office 2010

View 2 Replies View Related

Excel 2010 :: Click On Pivot Chart Data Point And Display Data

Apr 22, 2014

Pivot Chart. I would like to set up something to where a user can click on an individual value on a pivot chart (currently a line chart set up with 4 data series) and somehow display some underlying data. I have a lot of information stored in a data worksheet that I can't display all at once, but if a user sees a questionable data point, he/she can click and learn more about it from source data, or even a new query of the data worksheet.

I am using Excel 2010

View 2 Replies View Related

Excel 2010 :: Formula To Indicate Alphabetical Data Connected To Numeric Data

Jan 8, 2014

I have a question about using conditional formatting in excel (2010); I made a table with the following columns:

"Supplier Name" "Supplier Lead Time" "Internal Lead Time" "Total Lead Time". I made two different scenarios to show different supplier lead times and different internal lead times, and used the minimum function in the "Total Lead Time" column to find the smallest total lead time to select the best supplier.

The last thing I would want to do with this set of data, is plug in a formula that would somehow indicate which supplier corresponds to the shortest total lead time (which supplier has the smallest supplier lead time). I'd like to be able to use a formula that enters the name of the supplier in a designated cell, which I could indicate as the "Preferred Supplier" cell. If this is not possible perhaps there is a way to highlight the supplier's name with conditional formatting?

View 2 Replies View Related

Excel 2010 :: Link Data In Different Spreadsheet And Data Will Be Automatically Updated

Nov 3, 2013

I am working on Excel 2010. I want to find a way to link data from one spreadsheet to another one and whenever I update the first spreadsheet, the second one will be automatically updated?

View 2 Replies View Related

Excel 2010 :: Compare Data In Five Sheets And Pull Out Missing Data

Oct 2, 2013

I have one excel 2010 workbook with 5 work sheets, each work sheet contains a list with first/last name(one column) and the company name, some have a 3rd column with their email address in each sheet represents each year starting at 2008 thru to 2013 i have to find out if the people that attended an event in 2008 also attended it in 2009/10/11/12/13 and if they didnt, put their name and company name onto a blank worksheet within the same workbook without using a macro, how can i do this?

View 2 Replies View Related

Excel 2010 :: Comparing / Merging Child Spreadsheet (purified Data) Into A Parent One Raw Data)

May 17, 2014

I have a parent spreadsheet with raw data(with errors) and a child spreadsheet without errors. I want to merge the child into parent. (:{). I am thinking of comparing multiple columns from each sheet to ensure maximum accuracy. And when those columns match up we paste the corrected column data from child sheet to parent one. I am using windows 7 and Excel 2010.

View 1 Replies View Related

Excel 2010 :: After Applying A Data Filter And Sorting The Data / How To Revert Back To Original

Dec 20, 2012

I'm using Excel 2010 and I applied a Data Filter to a simple table. I then messed around with the drop downs in each column, sorting the data by different criteria. After doing this, is there a simple way to get the table to revert back to its original order/form?

View 3 Replies View Related

Excel 2010 :: Converting Hourly Data Into Daily Data

May 8, 2014

I have a problem converting hourly data into daily data for electricity import capacity. My file has the following form:
| Connection | Date | Period | Available (capacity) | Where 'Connection' identifies the physical location of the capacity (4 possibilities, Germany -> Netherlands; Norway -> Nehterlands; Belgium -> NL; UK -> NL), 'Date' refers to the date, Period refers to hour of the day (e.g. 18:00-19:00) and 'Available' refers to the import capacity of electricity on that conncetion during that hour. I need daily aggregates and tried several things such as DSUM but I cannot manage I've shortened the attached file quite a bit because of the upload size restriction. I am using Excel 2010. I've tried some suggestions in similar topics but none worked for me.

View 3 Replies View Related

Excel 2003 :: Match Two Sets Of Data And Display Specific Data?

May 12, 2012

I have two separate worksheets:

I'm trying to find a formula that looks at Column A on both sheets (each client is allocated a unique number) and if they match enter in column D of the referral sheet the month they were seen but only if it is a 1st contact (appt type on column D of contact sheet)

Referral
A
B
C

[Code]....

way to do the calculation using Excel 2003

View 9 Replies View Related

Excel 2010 :: Create A Sheet Which Captures The Worked Hours For Users In Various Cells?

Apr 1, 2014

I am trying to create a sheet which captures the Worked hrs for users in various cells. The SUMPRODUCT does not seem to be working for me .

Attached is the sample sheet which i would like to get the SUM using SUMPRODUCT.

Col D under Data Sheet has the names and these are repetitive. The Hrs / min in column E & F (Regular Hrs) & Additional Hrs / min under H & I.

I would like to have the Regular hrs + minutes to SUM up against each individual and displayed in consolidated under Col D (Regular hrs) & Col E (Additional Hrs).

I am using Excel 2010 and when i have used the same formulae in 2003 it worked. Not sure why it is not working in 2010.

View 2 Replies View Related

Excel 2010 :: Sales Daily Sales Data To Be Aggregated Into Monthly Data (not Using Pivot Table)

Mar 27, 2013

I cannot solve with Excel 2010 and I have searched all over for the answer.

I have sales data that is approximately daily and would like to count the monthly data and summarize it as an average for the month in a separate column.

For example, I would like to turn this:

3/2/2005 $xxxx
3/5/2005 $xxxx
3/20/2005 $xxxx
4/2/2005 $xxxx
4/10/2005 $xxxx

Into this:
March 2005- $xxxx (monthly average)
April 2005- $xxxx (monthly average)

I have a feeling some 'countif' formula would work but I am not sure how to do this.

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved