find a value from 1 column in another column on another sheet

TheMSsForum.com: The Microsoft Software Forum

The MSS Forum ‹ Excel Misc
- Archive
  - Biz
  - MCSE
  - CRM
  - Drivers
  - Framework
  - ADO
  - ASP
  - Compact
  - Forms
  - Dotnet
  - C#
  - VB
  - FontpageGen
  - Excel
  - WorkSheet
  - Exchange
  - Setup
  - Fox
  - Fontpage
  - ASP
  - IIS
  - Entourage
  - Money
  - Messanger
  - PocketPC
  - Powerpoint
  - Project
  - Publisher
  - Excel
  - VB
  - Security
  - Portal
  - Services
  - SQLServerDev
  - SVCS
  - SQLServer
  - VB
  - VC
  - MFC
  - ExcelGen

- Previous
  - 1
    - random merged cells Does anyone know why excel is randomly merging cells across multiple worksheets. I haven't found any viruses, reinstalled excel 2007, but issue still occuring. any insights would greatly be appreciated. Tag: find a value from 1 column in another column on another sheet Tag: 802667
  - 2
    - How to Average elapsed time values? I have a column with elapsed time values like 01:45, 02:25, 01:58, etc. I can't seem to get the average to work out right using other posts, so can anyone help me out! I have 667 values in colum M. All the values have a 0 as first number. If I remove the 0 from the time, I have no problem, but that would take forever, at least with my skill level! Tag: find a value from 1 column in another column on another sheet Tag: 802662
  - 3
    - How to return formatting to cells rather than value for "IF" cmnd Can formatting of cells be returned after an "IF" logic formula rather than a value? I'd like to have cells be colored when a specified cell has a specific number entered in it when the "IF" statement is true and not colored when the "IF" statement is false. Tag: find a value from 1 column in another column on another sheet Tag: 802659
  - 4
    - Hide/Unhide Using the function Group and Ungroup, I wonder if it is possible to ungroup a set of rows without displaying/unhiding any hidden lines included within the grouped area/block? The problem is that every time I ungroup the rows any hidden lines (not grouped ones) are displayed/unhidden. Thank you. Tag: find a value from 1 column in another column on another sheet Tag: 802658
  - 5
    - Excel graphics fill options I need to use the "old style" fill options with Excel graphics. In 2007, I can't find anything but textures. I need diagonal lines, checkerboard, etc. for overlaying on maps. Surely they didn't just do away with all of that. Suggestions, please. Tag: find a value from 1 column in another column on another sheet Tag: 802657
  - 6
    - AutoCalc Excel 2007 - I am trying to turn off the AutoCalc function. I am also turning it off (so it manually calculates when I press F9) in the default. However, when I close and re-open the spreadsheet it is back on and starts calculating right away and when I am working on it. Is there a way to turn this off so it never calculates unless I press F9? Tag: find a value from 1 column in another column on another sheet Tag: 802656
  - 7
    - How do I take ownership of a document I i created a document in EXCEL and would like to make sure people could view or add data to it but without being able to change who created it originally. Is there a way to mark for a word or excel doc as mine. Thank you Tag: find a value from 1 column in another column on another sheet Tag: 802652
  - 8
    - Copy (Formulas and Formatting ONLY) no values to other rows I have a workbook (tracker) that adds new records from a workbook called (data). When I add a new record from data to tracker, I want to copy the formulas and formatting from the row above the empty row in tracker and paste them into the empty row. Then when the data is copied over it uses the formulas and formatting as above. I do not want to have these formulas copied into every row on tracker until there is a need for them. Every time I write a vb script to copy and special paste formula only to the new row it copies cells that just have values as well. Can anyone show me code that will copy a rows formatting and formulas only to a row below it? Thanks! Tag: find a value from 1 column in another column on another sheet Tag: 802651
  - 9
    - Tool Tips for UDFs? Is there a way (involving C++) to add tool tips to UDF's? I have several formulas defined, and want to help users when they try to complete the formula in the cell. I'm looking to mimic the functionality provided for existing Excel formulas. I am using both 2003 and 2007. thanks, Harry Tag: find a value from 1 column in another column on another sheet Tag: 802649
  - 10
    - how do i stop my arrow keys from scrolling? My mouse scroll is zooming in and out and my arrow keys are scrolling. How do I reset the keys so that my arrow keys will move from cell to cell in Excel and the mouse scroll will not zoom in and out but scroll? Tag: find a value from 1 column in another column on another sheet Tag: 802638
  - 11
    - Publishing a spreadsheet online I have a spreadsheet that's a combination of data input sheets and graphs (which are created based on the data). I need to send this spreadsheet out to a large number of people, and I'm wondering whether there's a way to publish the spreadsheet online, where people can input data and see the resulting graphs, but not be able to see or change the formulae in the spreadsheet. Essentially, I'm looking for a way to publish a protected spreadsheet online. Tag: find a value from 1 column in another column on another sheet Tag: 802637
  - 12
    - How do I add a label to a macro on the quick access toolbar? I've recorded a macros, assigned a symbol to it, and placed it on the Quick Access Toolbar. When I hover over the symbol, it displays the macro name. Is there any way to have the name label display along with the symbol, or instead of the symbol? Tag: find a value from 1 column in another column on another sheet Tag: 802635
  - 13
    - Forgot Password Hi, I have an excel file with password to open and modify but now i forgot the password to open the specific file.....is there any way to open that file. Please help Thanks in advance Ravi Kumar Tag: find a value from 1 column in another column on another sheet Tag: 802630
  - 14
    - Hide/Unhide boxes I know to hide/unhide columns but I recently saw something cool that I can't recreate. There are small boxes in the rows above the columns that hold the hidden columns. When you click the box it unhides, click again it hides. Much easier for users to naviagate this way. Thanks! Tag: find a value from 1 column in another column on another sheet Tag: 802624
  - 15
    - numbers overlapping in line graph I have a line chart were the numbers are overlapping. Any suggestions? Tag: find a value from 1 column in another column on another sheet Tag: 802623
  - 16
    - Count matches within two columns On sheet 2....I have a lookup name in cell A1, and a lookup time (hour) in B1. On sheet 1, I have a list of names in A:A, and a list of (hours) in C:C. How do I count the number of matches between the two? AMY 12 AMY 15 BRUCE 12 BRUCE 22 AMY 12 AMY 12 AMY 12 BRUCE 33 AMY 23 So the formula would look up how many AMY are in the same row as 12 which equals 4. Thanx Tag: find a value from 1 column in another column on another sheet Tag: 802622
  - 17
    - Link a .DWG file? I have excel 2003. Im using charts to plot part geometry and dimension lines, based on user inputs, much like you would see in a CAD drawing (.DWG). I want to insert a link to a .DWG made in A9CAD (free version) so that I can see the drawing in excel. The .DWG would reference the user inputs in excel to draw the part, then the same excel file would reference the drawing so I can cut out dozens of huge value-tables used to plot the geometry currently. I havent explored the capabilities of A9CAD yet to see if it can reference an excel file, or a .txt output file...but I thought I would ask if this is possible first. Anyone know if this is possible??? Thanks! Tag: find a value from 1 column in another column on another sheet Tag: 802621
  - 18
    - Look Up Help Needed Again Hi, I had a similar question yesterday, related to a table of data that gets updated daily. The top row is this month's date, and the row below is the bank balance for that particular day. Then, the final column (to the right) is for totals. See below: Daily Total Dates| 7/1 7/2 7/3 etc. Value| $1 $4 $5 etc. ? Can someone help me create a formula that will add up the most recent seven days worth of bank balances? For example, after I input today's (7/24) balance I will want the formula to automatically sum July 18th - July 24th's balances. Thanks in advance for your help! Tag: find a value from 1 column in another column on another sheet Tag: 802619
  - 19
    - Constants in Excel Hi, I am using MOSS and Office 2007. I am collecting data via the SharePoint list and then exporting the list to Excell on my desktop to manipulate and aggregate the data. I have removed the table and broken the link with the SharePoint connection, unlocked the cells. When I attempt to add formulas to the excel spreadsheet, the formulas will work - when I evaluate the formula I get "The cell currently being evaluated contains a constant". I have found the cells that contain constants but need to remove the constants and can't figure out how to do that. Can you help? This is very urgent for my business. Tag: find a value from 1 column in another column on another sheet Tag: 802609
  - 20
    - Combine Text Cells Is there a formula to combine the text in multiple cells into one cell? I.E. if Cell A1 says RNA and Cell B1 says Te4 and Cell C1 says Late, is there a way to make Cell D1 combine them and be "RNA Te4 Late"? Thanks! Tag: find a value from 1 column in another column on another sheet Tag: 802607
  - 21
    - conditional formula with date I have a delivery date column with mmm-yy format. How can I write a conditional formula so that the date turns yellow when it's due in 30 days and turns red when it's over due? Please help! Tag: find a value from 1 column in another column on another sheet Tag: 802599
  - 22
    - Chart title that changes dynamically? Hello, Folks! I have created that changes on the fly based on the date range a user wants to chart. Is it possible to include a title field at the top of the chart that imports the Start and End date so we don't have to type it by hand whenever we view or print different ranges? Something like Tacoma Bulk &[=Min(A1,B1)] to &[=Max(A1,B1)] Thanks for your time. Arlen Tag: find a value from 1 column in another column on another sheet Tag: 802590
  - 23
    - protect formulas in locked cells from changing if data is moved I have locked and protected a worksheet leaving only "Select unlocked cells" checked. The problem is that if a user "moves" an entry in an unlocked cell, either by ctrl X or drag and drop the cell references in the locked formulae that referred to the original position change to the new position. Is there any way of preventing this without turning off the drag and drop functionality? Tag: find a value from 1 column in another column on another sheet Tag: 802586
  - 24
    - how do i make a cell turn red if value is less than 0? i have a list of numbers on my worksheets, inclusing some zeros. i would a formula that means whenenver a cell just has 0 in it it will show as red. Is this possible? Thanks Tag: find a value from 1 column in another column on another sheet Tag: 802579
  - 25
    - code does not work anymore Hello Excel 2002 - I have a spreadsheet that I rename every month with the currents month's name,( e.g. Sales for July 2008 ) and has a worksheet for each work day of the month and a summary tab at the end. I've been using the following code but the code does not work anymore . above the code first box is worksheet second box is Selection change Private Sub Worksheet_SelectionChange(ByVal Target As Range) With ActiveSheet newname = Application.WorksheetFunction.Text(Range("J1"), "mm-dd-yy") ActiveSheet.Name = newname End With End Sub thanks in advance Tag: find a value from 1 column in another column on another sheet Tag: 802578
- Next
  - 1
    - Is there a macro to... Is there a macro that would make it possible to lookup a certain name in a column and anytime it finds that name it will copy a range of cells and paste it into another worksheet? I.E. anytime the name Jim Smith is found in the range A5:A200 it returns a range of cells next to it...if Jim Smith was found in A5, it would copy cells C5:FN9 and paste them in a specified location on another sheet Right now I have it copying all colmns from the master sheet and if the name is not assigned to that project it will return a 0, then I have a macro to hide all rows that equal 0. Is there a better way to do this? Any hints on the best way to do this? Thanks! Tag: find a value from 1 column in another column on another sheet Tag: 802576
  - 2
    - How to combine information from multiple rows into one Hi, I have a spreadsheet with multiple (duplicate) rows which I would like to combine into one. The spreadsheet lists name/address of a customer and also anyone else who may be living with them in the same property. I would like to mail merge the file and send a letter to each customer listing who we think lives in the property. Here is an example: Current file: Mr Peter Smith, 1 Nowhere Street, Amy Smith Mr Peter Smith, 1 Nowhere Street, Tracey Smith Mr Peter Smith, 1 Nowhere Street, Tim Smith The new file should show 1 row as: Mr Peter Smith, 1 Nowhere Street, Amy Smith, Tracey Smith, Tim Smith Tag: find a value from 1 column in another column on another sheet Tag: 802572
  - 3
    - Reassign built-in shortcut keys in excel 2007 Hi there, how can I reassign (rename) built-in shortcut keys in excel 2007? For example I would like to reassign the CTRL+F (currently assigned to "bold" in the Danish version) to CTRL+B (currently assigned to "Find"). Cheers Tag: find a value from 1 column in another column on another sheet Tag: 802570
  - 4
    - dates and conditional formatting I have a spreadsheet (excel 2003) that has the month in column A2 and the days in column b1, c1 etc. Please see the example below. I would like to use conditional formatting to identify ,by fill color, weekends in the appropriate cells, for example Jan 5th and 6th. I am very perplexed. Any help would be much appreciated. Thanks. a b c d e f g h i j etc 1 2 3 4 5 6 7 8 9 Jan Feb JH Tag: find a value from 1 column in another column on another sheet Tag: 802569
  - 5
    - Excel VBA Coding for Printing Hello Could someone help on this? I like to put an object on an Excel sheet. When I click it it should print a selected range. I tried a macro but I could go up to the pre view level only. Thank You M Varnendra Tag: find a value from 1 column in another column on another sheet Tag: 802563
  - 6
    - Graph Data not displaying Hi, I have a range of 4 values in a spreadsheet and when I select them for a bar graph I am getting the graph with no bar displayed. The cell references are correct in the series value tab. I have tried re-entering the data but no joy. Anyone any ideas? All help greatfully accepted. Davie Tag: find a value from 1 column in another column on another sheet Tag: 802559
  - 7
    - print/preview problem before saving an edited excel worksheet Hi, I am the Office 2000 user. I encounter problem with printing an excel worksheet each I edit the contents without saving. Here is the comment message appeared each time I press the print / preview button: "To print, it is necessary to overwrite the content. Do you want to overwrite?" I could only print or preview if I click yes. Please advise how to fix this. Thanks. Tag: find a value from 1 column in another column on another sheet Tag: 802554
  - 8
    - Seperating numbers from text Hi, I am working on an excel file which has a column that contains product names + serial numbers. Dont know how but someone combined that data. they used to be in different columns but now they are in same column and have no spaces between them. cells have names like this, productname1345 is there any easy way to scan through the coulmn and at least seperate the data like productname 1345 after this step i think i can handle it:) thanx for help. Tag: find a value from 1 column in another column on another sheet Tag: 802552
  - 9
    - Correlations, Group By Type? I have data for several different types of land Variable1 Variable2 Variable3 TypeOfLand 30 250 300 Desert 40 670 200 Forest 10 30 400 Forest 20 500 800 Arctic 20 180 270 Desert .... I'd like to know if there's a way using PivotTables or any other function to product the following automatically CorrelationBetweenVar1and2 CorrelationBetweenVar2and3 CorrelationBetweenVar1and3 Desert Forest Arctic Swamp Lakeside ... In other words, I'd like to run a correlation between two variables, but I'd like for it to be grouped by type. Will be very grateful for any input! Regards, Sak Tag: find a value from 1 column in another column on another sheet Tag: 802551
  - 10
    - how to activate the scrolling within the worksheet? i cant able to scroll the worksheet using the mouse scroll action. now im scrolling using the vertical scroll bar only.give some ideas over this problem Tag: find a value from 1 column in another column on another sheet Tag: 802550
  - 11
    - Excel - printing specific areas Hi, my name's Gabriella. I've been working with Microsoft Excel to build a customer database. Each column is something else - address, city, zip code, phone number, job done, date the job was completed and name. I didn't put any formulas in, nor did I mess with anything that I don't know about within Excel - namely everything. This is where my question comes in. Say I want to print a list of people who've had jobs done in a specific city, or in a specific year. Is there a way to print that without having to go through the whole file and picking them out with ctrl+click? Is there some kind of formula, etc.? I know close to nothing about Excel's special features, so any help would be greatly appreciated. Thanks! :) -Gabriella Tag: find a value from 1 column in another column on another sheet Tag: 802547
  - 12
    - How do i convert a date into 3 seperate columns? I have a spreadsheet with dates shown as DD.MM.YYYY in text in a single column but I would like this to be represented as 3 seperate columns. One for day, one for month and one for year. Does anyone know a formula or command to do this? Many thanks, Tony Tag: find a value from 1 column in another column on another sheet Tag: 802544
  - 13
    - Changing column headers from alphabetical characters to numbers? I have inherited a spreadsheet that uses column headings in numbers as well as the rows. How can I reinstate the column headings to alphabetical characters? Tag: find a value from 1 column in another column on another sheet Tag: 802543
  - 14
    - fill combobox depending on selection from another combobox Hi all, I have been making a userform and so far all is great thanks to al the posts here, but, i can't find anything to help me with this problem. I have put 3 comboboxes on my userform, The first one i want to fill from a list. The second one i want to fill from a list depending on what was selected from the first combobox. And the third filled depending on selection from second. then all three selections put on a worksheet in seperate columns i hope this makes sense as i am only new to this. many thanks in advance Ad Tag: find a value from 1 column in another column on another sheet Tag: 802539
  - 15
    - Lookup unsorted list I have a very simple two column list. Col-A is "Products" col-B is "Date Purchase". Col-A is un-sorted and various products are repeated several times. I want a lookup formula that would tell me the most recent date when a particular product was purchased. If col-A was sorted ascending, I could use =LOOKUP(1E+100,INDEX(A2:B8,MATCH(A12,A2:A8,1),0)). Can someone please help. Tag: find a value from 1 column in another column on another sheet Tag: 802533
  - 16
    - Dynamic Array (multiple columns) Question I'm trying to create a dynamic array of data. I had previously dimensioned at the top of my sub the following: Public ItemArray(10,7) As Variant This gets me what I want, but only for 10 entries. I may actually have 50 entries. How do you dimension an array such that the amount of entries changes / is dynamic? The array is currently inside of a loop for items, and I want the array to update as below with each item. Hopefully this is enough information to piece together what I'm looking for. Thanks, Neil ItemCount = ItemCount + 1 ItemArray(ItemCount, 0) = ItemName ItemArray(ItemCount, 1) = AAvariable ItemArray(ItemCount, 2) = BAvariable ItemArray(ItemCount, 3) = CAvariable ItemArray(ItemCount, 4) = DAvariable ItemArray(ItemCount, 5) = EAvariable ItemArray(ItemCount, 6) = FAvariable ItemArray(ItemCount, 7) = GAvariable Tag: find a value from 1 column in another column on another sheet Tag: 802520
  - 17
    - Stop my references cancelling Hi, got a convoluted one (at least by my low standards). Using Excel 2003. I need to set up finance reports that total data using 3 criteria. To get the data, I export a report from my finance system to a spreadsheet, which I then copy into a blank (but pre-named) worksheet in the same workbook. I then "tidy up" this spreadsheet with a simple macro to delete blank rows/columns and add some columns with conditional arguments that helps sort out different types of transaction types. In the reports I'm using the following function: =SUMPRODUCT(--('Report'!V2:V30000!="S"),--('Report'!M2:M30000!="<code>"),('Report'!H2:H30000!)) Where: "S" is one of my added conditional arguments that identifies certain transaction types; <code> is the finance project code I'm reporting on; and H2:H30000 is the amounts I want to add up. Now, this formula works just fine - provided the data's already in the cells. Trouble is that I need these to stay just as they are when I then remove the original data - remember, I need to copy this in AFTER this is all set up. What I'm trying to achieve is a workbook to produce task reports (for about 30 tasks) that requires only we paste the raw data into the blank template, run my little macro and hey presto, everything else updates. I've looked at INDIRECT, but it doesn't seem to like array references. I can't seem to stop Excel automatically updating the reference any other way. Any help would be awesome. Tag: find a value from 1 column in another column on another sheet Tag: 802518
  - 18
    - Countif and Hi All, How I count then no. of occurances in following condition Status Speed Harsh Braking 5 Driving 15 Harsh Braking 12 Harsh Braking 35 Harsh Braking 40 Driving 12 Harsh Braking 4 Harsh Braking 0 Driving 15 How I can count the No. of harsh Braking if speed is more then or equal to 10. Thanks Tag: find a value from 1 column in another column on another sheet Tag: 802516
  - 19
    - Headers and Footers in Excel 2007 I have a worksheet that contains 69 pages. I need a custom footer on each of the pages. I know in Word, you could simply unselect "same as previous". However, I can't find any information anywhere. Any help is appreciated. Tag: find a value from 1 column in another column on another sheet Tag: 802509
  - 20
    - How can I open .wk4 file with Excel 2007? How can I import/open/edit .wk4 file in excel 2007? Tag: find a value from 1 column in another column on another sheet Tag: 802508
  - 21
    - REMOVE CERTAIN LINES IN A PIVIOT TABLE My worksheet is 350lines long and growing. i've created a pivoit table but want certain lines to be ignored. e.g items that have been invoiced, or lines that qualify a certain date range. Is there a way of doing this? All suggestions are greatfully welcome -- Browny Tag: find a value from 1 column in another column on another sheet Tag: 802507
  - 22
    - Meeting 12 formatted conditions Cells C2:C13 has JAN - DEC. If todays month is between the 1 - 31 of Jan I require the previous month â??DECâ?? conditionally formatted with a color that I specify from the color pallet. If todays month is between the 1-28 or 29th of Feb I require the previous cell month â??JANâ?? also conditionally color formatted. As conditional formatting only allows you to set a maximum of three conditions, how do I format all the months in C2:C13 the same way? Tag: find a value from 1 column in another column on another sheet Tag: 802505
  - 23
    - How do I Subtract 6 months off a date in excel Hi Just wanting to subtract 6 months off any date, and present in the format of a date. Cheers Tag: find a value from 1 column in another column on another sheet Tag: 802501
  - 24
    - How to calculate Average Hi I have data in my sheet as Row# Column A Column B Column C 1 20 0 20 2 Row # 2 has some other data 3 row # 3 has some other data 4 21 20 15 5 row#5 has some other data 6 20 0 15 ---------------------------------------------------------------- 8 Total- 61 20 50 ---------------------------------------------------------------- I want to take the average of c1, c4, c6 with the condition that cell c1 should be counted in average if cell a1>0 or a8>0, similarly cell c4 should be counted in average if cell a4>0 or a8>0 Thanks Tag: find a value from 1 column in another column on another sheet Tag: 802498
  - 25
    - Using VB Copy Entire Row but formulas and formats only no values I have a workbook called tracker that adds new records from a workbook called data. When I add a new record I want to copy the formulas and formatting from the row above and paste them into the empty row. Every time I try it copies the values as well. please help Tag: find a value from 1 column in another column on another sheet Tag: 802496

I need to see if the value of cell in a column on sheet1 is also in a column
on sheet2, if it is I want it to return a value of TRUE. Is there a function
I can use for this?

Top

Re: find a value from 1 column in another column on another sheet by Bob

Bob
Thu Jul 24 11:30:21 CDT 2008

=COUNTIF(Sheet2!A:A,A2)

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gluefoot" <Gluefoot@discussions.microsoft.com> wrote in message
news:4093A457-1825-4262-99C2-511D4E43AD2D@microsoft.com...
>I need to see if the value of cell in a column on sheet1 is also in a
>column
> on sheet2, if it is I want it to return a value of TRUE. Is there a
> function
> I can use for this?

Top