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:


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:


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

View 2 Replies


Excel 2010 :: Extracting Data From Large Spreadsheet

Jan 31, 2014

I work with Excel 2010 and have a very large spreadsheet with data that I need to manipulate in several different ways. I have been filtering and then cutting and pasting but this is very time consuming . Is there a way to extract specific data from the spreadsheet and transfer it to different worksheets? I don't really know how to use macros.

[URL] .....

View 14 Replies View Related

Show Tick Or Cross Upon Checking Cell

Jun 2, 2008

i don`t know how to make this in VBA

But please allow me to explain, if I have numbers in Cell F9 I want image to be displayed as (X <---- which it means wrong) on G9 and message to be appear in H9 says only words are allowed. In case, cell value are words; I want it to show image <---- which it means right) and the message to be say correct. And if cell is empty I want it to show image (!) and the cell beside it the message should say (Please Fill up).

I want to apply this to words instead of numbers as well.

View 4 Replies View Related

Excel 2010 :: Checking Date For Two Columns?

Apr 15, 2013

I have two excel 2010 files that I want to run this on. Each one has about 10+ worksheets in them. I am looking to have a vba script that will look at columns D and E starting at row 4 and check if they are expiring in the next month or have already expired (before today's date). Then it would return a text file that will say which worksheet it is on, the row and column, and what date is in that cell.

View 9 Replies View Related

Excel 2003 :: Extracting Sub Sheet Of Data From Larger Schedule

Dec 31, 2013

I have sheet containing a schedule of data. It is a record of changes on a project, each change is sequentially numbered and contains a row of data (date of change, whether approved, cost of change etc). Each change is given a cost centre reference (1 of 10 cost centres are being used) depending on the type of change being made.

I need to keep that record of the overall schedule of changes but I also want to extract each of the cost centre categories into a sub table on the sheet to give a schedule of changes against each category.

I have almost achieved what I want but one method leaves blanks in the sub table which I want to avoid and the other method I used to remove the blank lines but needs the first method sheet calculated before it then goes to work and seems rather cumbersome and I suspect there is a much easier method. (I also want it to be compatible with excel 2003 so it can be used across several platforms.)

View 7 Replies View Related

Cross Reference (Vlookup) Based On Data Validation List Dropdown?

Jun 19, 2014

Attached is example of what I'm trying to do. I want to use the drop-down on the "Master Tab" and have the corresponding price by promo_month appear in the yellow cells.

Currently its on July, but I would like to switch that to August etc and have new prices populate in the yellow cells. Prices come from the "table" tab.

Example 06.19.14.xlsx‎

View 2 Replies View Related

Excel 2010 :: Checking If (Trust Access To Visual Basic Project) Ticked

Sep 17, 2012

I have a macro that seems to work okay for older versions of Excel - I use Office XP (2002) tell me if this will work for newer versions (2007 / 2010)?

KB1017 - Trust access to VBA project

Function VBATrusted() As Boolean
On Error Resume Next
VBATrusted = (Application.VBE.VBProjects.Count) > 0
Exit Function
End Function
Private Sub Workbook_Open()
If Not VBATrustedAccess() Then


I was thinking that i should probably have

VBATrusted = -1

as the third line so that it is negative unless the tick is there. No way of checking older versions right now though.

View 3 Replies View Related

Excel 2010 :: Consolidating Info In Correct Format

Sep 11, 2013

I have a spreadsheet with five cells (L3 - L7) that have dates entered into them in the format of 9/9/2013, 9/10/2013, 10/20/2013, 10/21/2013, 10/22/2013. In another cell, cell n2, I have the formula:

=IF(L2=0,"",IF(L2=1,L3,IF(L2=2,CONCATENATE(L3,L4),IF(L2=3,CONCATENATE(L3,L4,L5),IF(L2=4,CONCATENATE(L3,L4,L5,L6),IF(L2=5,CONCATENAT E(L3,L4,L5,L6,L7)))))))

In cell n2 I get: 41526415274156741568481569 This is the excel number for the above dates, but I need it to look like:

Sep. 9 - 10, 2013, Oct. 20 - 22, 2013

I also need it to be able to isolate single dates for example if the date in cell l5 was 10/5/2013 instead of 10/20/2013 then I would want n2 to look like Sep. 9 - 10, 2013, Oct. 5, 2013, Oct. 21 - 22, 2013. but my formula currently only changes that number in the string from 41567 to 41552.

The reason I need this is because this information is part of a identifier and also maybe used in reports, the people reading the reports won't know this long number is actually dates and won't be able to read them if they did.

Also the cells L3 - L7 data depends on cell l2, 1 = one date entered into cell l3, 2 = two dates, one in cell l3, the next in cell l4, and so on for up to five total days that may or may not be consecutive. Also, it seems to work as is, but only for one day.

View 1 Replies View Related

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 :: Find Matching Info And Return Another Number?

Dec 3, 2013

I am trying to create a formula for a work report and I am afraid I am a bit of a novice with the more complex formulas. Here is what I am trying to do: Sheet 1 has Sales order numbers that duplicate based on the sales lines. Sheet 2 has single lines of sales order numbers and their assigned PO number. I would like to have Sheet 3 where it combines all data and duplicates and fills in the PO number to match all of the sales order numbers that repeat on the other sheet. I hope this makes sense. I can't seem to figure out if I need a VLookup or If or Match formula (or all of the above).

View 1 Replies View Related

Checking Info Entered

Aug 5, 2009

I want add some code into a macro of mine which checks to see if J8 >20 and DA8 = "TRUE" then a msgbox appears saying "no auto margins".

View 2 Replies View Related

Excel 2010 :: Extracting Name From A List Based On Criteria

Nov 15, 2013

I am using Excel 2010 on Windows 7. Here is a link to the worksheet I have a question about : Example.xlsx

The only two relevant sheets here are "TestScores" and "Area 1." What I am trying to do is copy names over from the TestScores sheet to the Area 1 sheet based their test scores. It is important to know that a passing score is anything 85 or above. If you don't pass Phase 1, you move onto Phase 2, and if you don't pass that you move onto Phase 3.

Let me first explain how the TestScores sheet works. Here is a screenshot of the sheet:

I copied in column C, the names, from a roster sheet. Columns A, B, and D all VLOOKUP information from the roster as well. You can ignore columns E & F. Column G is just a test name that is manually inserted. They are all in the same order since this is an example worksheet I am doing the program on before I input the real data. Column H is also manually inserted, with data validation to only allow values 0-100 and a few different relevant terms (MS, EXT ABS, N/E). These next two rows are the formulas in columns I and J, starting in row 3.

I=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(H3="","",IF(H3>=85,"N/A (PASS)","Need")))))
J=IF(H3="N/E","N/E",IF(H3="EXT ABS","EXT ABS",IF(H3="MS","MS",IF(I3="","",IF(I3="Need","",IF(I3>=85,"N/A (PASS)","Need"))))))

What these formulas do is the following: If N/E, MS, or EXT ABS is in column H, it copies those over to the next two columns. If someone scores 84 or below, it says "Need" in the next column. If they score 85 or above, it says "N/A (PASS) in the next column(s). The user is meant to write over these to insert the phase 2 and 3 scores.

Here is the "Area 1" spreadsheet I am working on that has the problem.

First off, I created these formulas by comparing them to another post and replacing my ranges and criteria. In column A, it returns all the names of Team Members who passed in Phase 1 (have a score 85+ in "TestScores" column H). The array formula is below and it works great.


In column E I have a similar formula, except it returns the names of those Team Members who have "Need" in column I of "TestScores." This formula also works great. The formula in column D is just a VLOOKUP based on column E and works well, too.


Here is where my problem is. In column F, I want to return all the names of people who passed Phase 2, which means they have a score of 85 or above in column I of the "TestScores" sheet. However, not only is it not returning the right names, I can't even figure out what criteria the names is returns has. The current list, in (mostly, oops) red, is incorrect. The formula, below, is almost the same as the formulas above, with only 1 difference in each case.


It refers to column I, instead of H, compared to the formula in column A. Its criteria is >=85 instead of ="Need", in comparison to the formula in column E. Nonetheless, it still isn't returning the right names! The other weird thing, is if I replace the ">=85" with "=90" it will return the names of team members who scored 90.

View 3 Replies View Related

Excel 2010 :: Extracting And Manipulating Text From Cells

Sep 16, 2013

This could be a bit complicated, but hopefully also useful for others. We have the following sample of 10 cells from a large file with similar data:

N: 1; B: 162; M: 278, 278; T: 24; A: 103, 105; I: 393; Ḥ: 7240, 7390, 7465, 7546, 7617, 7756, 27399, 8380, 8741, 8894, 8985, 9559, 9671, 9741, 10119, 10211; W: 40; D: 766.
N: 2; B: 246, 889, 1136; M: 255, 255; A: 55; I: 286; Ḥ: 22731, 22802, 22857, 22906, 22948; D: 685.


We want to extract the same letters with its numbers and put the same letters with numbers in one column and do this for all letters, but - this is important - while keeping them in their original row. The purpose of this is addition of data and easy rearrangement of the letter plus numbers order, e.g., to: B M N A T I W D Ḥ. As you can see not all the rows have all letters, nor are the seizes the same. There are thousands of such cells. Some parts are partly in bold, we want to maintain that layout after sorting. Such a thing should be easy for computers, but how could this extraction and manipulation be done (I have Excel 2010)?

P.S. There are two spaces after every ; that were eaten up here.

View 9 Replies View Related

Excel 2010 :: Extracting Highest Number From A Cell Containing Alphanumeric

May 10, 2014

I have a cell containing alpha and numeric. I would like to extract the highest number in the cell

the cell looks like this

the answer I would like is



I am using Excel 2010

View 3 Replies View Related

Cells Cross Referancing Data From Another Sheet To Fill Themselves In?

Apr 15, 2009

I would like cells to fill themselves in automatically on one sheet, by getting the figure from the cell to the right of them, and seeing where the same figure lies on a different sheet. which ever row this figure lies in will be used to grab a different figure from colum "C" of the same row. This is the figure i would like to be put back on the first sheet.

Im aware that i havent explained this very well but i hope that my attached spread sheet with a couple of notes and highlighted bit will explain it much better.

Thank you very much in advance to anyone who can help me with this!


View 14 Replies View Related

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 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 :: Extracting Information From Multiple Form Control Dropdown Buttons

Aug 23, 2013

My problem is I cannot copy from a Form Control drop down box, and I need to do this for ~10 drop down boxes in about 200 xls files. The files follow a template so the drop down button Inputs are uniform. Here is some more detail:

I’m building an Access Database so that our data (currently in many, many Excel Spreadsheets) can be efficiently queried. The old system was that when asset data was collected, the data would be entered into an Excel spreadsheet, using a Template. So for example:

Project A1234

Main Street Pipe
High Street Pipe

Ref No.

Installation date

X Coordinate

Y Coordinate

We have hundreds of these files, but I managed to get all the files in one folder and Paste and Transpose the data from the relevant Cell Ranges in each file (using a VB sub) into ONE summary sheet. Success; or so I thought.

Unfortunately for one generation of the template (people changed them slightly every year), the template had people enter information into Drop Down Menus. Not a problem when they’re associated with a cell, but these were free floating Form Controls.. so you can’t actually click and copy from them, or call them in VB as you would a specific cell.. afaik.

So my issue is how can I extract the data from those Form Buttons? I’ll try and provide as much info as I can:

Firstly I tried to find some sort of identifier for each Form Button. The dropdowns are called Combo Boxes, (Developer tab > Insert > Combo Box (Form Control) ), and when you select them (by right clicking) and hit the View Code button in Developer Tab > Controls the following shows up in VB.

Sub DropDown44_Change()

End Sub

I’m guessing that the number is related to the button so that’s one useful thing, an identifier of sorts. There’s no actual code there though really from what I can see.. My problem is I have no idea how to extract from DropDown44!

The range of values in the drop down comes from a separate sheet where there’s just a list of the values. There aren’t any cells linked to the drop downs though. If I enter a cell in (Right Click) Format Control > Cell link: then it prints the number (e.g. 4) of the value in the range. So if the drop down options are “Monday”, “Tuesday”, “Wednesday”, “Thursday” and “Thursday” is selected, the linked cell says: “4”.

They didn’t link a cell, then I could call that cell in a VB sub and the job would be done. Unfortunately they didn’t, so I tried Recording a Macro and selecting the drop down form control.. That didn’t really work, except if I right clicked it, I got some more info from the macro VB:

ActiveSheet.Shapes.Range(Array("Drop Down 19")).Select

So is my drop down an Array? My understanding of an Array is that it’s a list of arguments.. all I get there is the name of the button. I am not experienced in VB so this is as far as I’ve got. Perhaps I can get a sub to select the button using the above line, but I need the info from it, i.e. which value is currently selected in the Drop Down.

(Using Excel 2010 but the .xls files are from ~2005)

View 1 Replies View Related

VLOOKUP Not Extracting The Data

Jan 7, 2009

I have a problem with VLOOKUP not extracting the data I need from 1 workbook.

I want to populate 4 different sheets in the "108" workbook with data from the "1st" workbook. I've uploaded the workbooks to show the formulas I have already. The data in "1st" workbook changes daily and can have upwards of 2000 rows. I've simplified the list for the example.

In the "108" workbook I want to fill in the first three columns with the formula down to about row 500.

I've tried different formulas to do this using EXACT, MATCH, and VLOOKUP but to no avail.

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

Checking And Indicating Data From One Sheet To Another

Jan 29, 2009

sheet1 has a list of names. sheet2 has a list of names and some of the names are the same as sheet1. What I want is for the names on sheet1 to indicate (preferably change text colour) if they are on sheet2.

View 5 Replies View Related

Splitting And Extracting Certain Info

Aug 6, 2009

I have a spreadsheet which contains addresses split randomly into several columns and need to extract the postcode for every row.


Above is a small example of 2 of the columns, most addresses are in 3 columns.

The postcode can appear at the front, end or anywhere in the address field. Depends on who typed it in. I need to somehow extract the postcode and have it in a separate column.

Text to columns wont work i think because the info isnt uniform, I've tried the search for the various postcode starting letters but still cannot extract the entire postcode.

View 10 Replies View Related

Extracting Info From Cell

Nov 18, 2008

I have a cell that has address and city with postal code.

I need to be able to extract the city and postal code on its own.

I know i could do Data - Text to columns but i would like to keep the cell intact and not have to split it up.

Here is an example of the cell in question:
1234 Parc street, Montreal H1A 2N2

View 9 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 VBA Checking Column / Row Of Data

Aug 11, 2014

I have a requirements in excel vba to evaluate and check the column data from other workbook. I would like to filter remarks for all "INTRANSIT" from my working file then If the data or tracking# is exist from the source file evaluate if transaction remarks is "Done" or if "Not yet" exist. If remarks from SourceFile is "Done" i will put "RECEIVED" or If "Not yet" i will put "INTRANSIT" in my Working file Remarks column.

Workbook WorkingFile:
Tracking# QtyModelRemarks

SouceFile --- to evaluate if Done or Not Yet

Tracking# QtyModelRemarks
D-8840862411|20mmmNot Yet
D-8840865000|230mmmNot Yet
D-8840865000|50bbbbNot Yet
D-8840862450|20bbbbNot Yet
D-8840862450|120bbbbNot Yet

View 11 Replies View Related

Extracting Info From Multiple Worksheets To Summarize

Oct 9, 2007

I have a workbook that we use for our internal customers to use to place orders for equipment. There are five worksheets within that file that outline the different products. The requestor will indicate what items they want by putting a number in the quantity cell that is appropriate for the product. This could be using a combination of all five worksheets for the one request.

What I want to do is to create a summary page that only extracts the information from the other worksheets that has a value marked in the quantity spot only.

View 9 Replies View Related

Restricting Vlookup Function For Extracting Data To Single Item?

Mar 16, 2014

Currently I am facing a problem that I want to copy data from sheet 2 to sheet 1.Here I am using vlookup function.But I have a problem that when there is two items of same name in sheet 1,it copies data from sheet 2 into both same items in sheet 1.How can I restrict and copy data to only 1 items.The sample data is as shown below :

Sheet 1 (Including duplicate item)


In sheet 2 (Unique item)

Apple 25
Ball 36
Cat 34

View 12 Replies View Related

Fill Info In Sheet One And Have Same Info Appear In All Sheets That Follow

May 12, 2014

I would like to fill in a a form on page /sheet one and have the same info on every sheet that follows is it possible?

View 3 Replies View Related

VLOOKUP - To Create A Cross Reference Tool

Mar 24, 2008

I'm using VLOOKUP to create a Cross Reference tool. CR is my "anchor" page that I'm attempting to tie the remaining worksheets WIT,TEC,COP back to the CR worksheet. I'm able to get VLOOKUP to work on the CR worksheet in columns H & I but unable to get the VLOOKUP to work in column J. The VLOOKUP function is entered but it does not return a value that I know exists in worksheet COP.

View 11 Replies View Related

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