checking input in a cell and return by copying info from a other c

What I want to do is the following. I have a worksheet in which I have 
created the following; Name-Column , select-Column. I now want to created a 
formula in a new sheet which check if the are "x" in the select column and 
copy the names of the name-columns to cells in that new sheet. (which is in 
the same workbook) is that possible? If so please let me know.

Thanks in advance. 
0
Utf
2/19/2010 3:30:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

4 Replies
768 Views

Similar Articles

[PageSpeed] 34

To find a single "x" you would use VLOOKUP().  For all the "x"'s, see:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx
-- 
Gary''s Student - gsnu201001


"Golf-Iron7" wrote:

> What I want to do is the following. I have a worksheet in which I have 
> created the following; Name-Column , select-Column. I now want to created a 
> formula in a new sheet which check if the are "x" in the select column and 
> copy the names of the name-columns to cells in that new sheet. (which is in 
> the same workbook) is that possible? If so please let me know.
> 
> Thanks in advance. 
0
Utf
2/19/2010 4:00:01 PM
"Gary"s Student;

Thanks for your answer but that is not quit what I was looking for. Maybe I 
need to clearly explain what I want to do. 

Column B contains "x" values which have been filled in in more than one 
cell. (B1;B3;B5 etc) Column A contains Names. (A1;A3;A5 etc.) And now I want 
in a different worksheet in the same workbook all the valves of Column A only 
if Column B is "x"

The return value needs to be one per cell. So if there are let's say 3 names 
each name needs to be returned in a different cell. 

I think this can't be done with formules but maybe someone has got an idea. 

 

 




"Gary''s Student" wrote:

> To find a single "x" you would use VLOOKUP().  For all the "x"'s, see:
> 
> http://office.microsoft.com/en-us/excel/HA012260381033.aspx
> -- 
> Gary''s Student - gsnu201001
> 
> 
> "Golf-Iron7" wrote:
> 
> > What I want to do is the following. I have a worksheet in which I have 
> > created the following; Name-Column , select-Column. I now want to created a 
> > formula in a new sheet which check if the are "x" in the select column and 
> > copy the names of the name-columns to cells in that new sheet. (which is in 
> > the same workbook) is that possible? If so please let me know.
> > 
> > Thanks in advance. 
0
Utf
2/19/2010 6:45:01 PM
Ok guys the following is the problem I really hope there is someone who can 
help.

Sheet 1 contains the following. 

Column A      Column B
piet               x
kees
jan                x
joost             x
etc

Sheet 2

Column A needs to show all the names in seperate cells which are marked with 
a x in sheet 1. 


Any idea !?!?!?!?!?!





"Golf-Iron7" wrote:

> "Gary"s Student;
> 
> Thanks for your answer but that is not quit what I was looking for. Maybe I 
> need to clearly explain what I want to do. 
> 
> Column B contains "x" values which have been filled in in more than one 
> cell. (B1;B3;B5 etc) Column A contains Names. (A1;A3;A5 etc.) And now I want 
> in a different worksheet in the same workbook all the valves of Column A only 
> if Column B is "x"
> 
> The return value needs to be one per cell. So if there are let's say 3 names 
> each name needs to be returned in a different cell. 
> 
> I think this can't be done with formules but maybe someone has got an idea. 
> 
>  
> 
>  
> 
> 
> 
> 
> "Gary''s Student" wrote:
> 
> > To find a single "x" you would use VLOOKUP().  For all the "x"'s, see:
> > 
> > http://office.microsoft.com/en-us/excel/HA012260381033.aspx
> > -- 
> > Gary''s Student - gsnu201001
> > 
> > 
> > "Golf-Iron7" wrote:
> > 
> > > What I want to do is the following. I have a worksheet in which I have 
> > > created the following; Name-Column , select-Column. I now want to created a 
> > > formula in a new sheet which check if the are "x" in the select column and 
> > > copy the names of the name-columns to cells in that new sheet. (which is in 
> > > the same workbook) is that possible? If so please let me know.
> > > 
> > > Thanks in advance. 
0
Utf
2/24/2010 6:38:06 PM
Hi,

Try this.

Suppose your data is in range A2:B5 on sheet1.  On sheet2, type * in cell 
C3.  In cell D3, enter the following array formula (Ctrl+Shift+Enter) and 
copy down

=IF(ISERROR(INDEX(Sheet1!$A$1:$B$5,SMALL(IF(Sheet1!$B$1:$B$7=$C$3,ROW(Sheet1!$A$1:$A$7)),ROW(Sheet1!1:1)),1)),"",INDEX(Sheet1!$A$1:$B$5,SMALL(IF(Sheet1!$B$1:$B$7=$C$3,ROW(Sheet1!$A$1:$A$7)),ROW(Sheet1!1:1)),1))

Hope this helps.

-- 
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Golf-Iron7" <GolfIron7@discussions.microsoft.com> wrote in message 
news:F3E3681C-C065-4746-A557-0A6FBCF88284@microsoft.com...
> Ok guys the following is the problem I really hope there is someone who 
> can
> help.
>
> Sheet 1 contains the following.
>
> Column A      Column B
> piet               x
> kees
> jan                x
> joost             x
> etc
>
> Sheet 2
>
> Column A needs to show all the names in seperate cells which are marked 
> with
> a x in sheet 1.
>
>
> Any idea !?!?!?!?!?!
>
>
>
>
>
> "Golf-Iron7" wrote:
>
>> "Gary"s Student;
>>
>> Thanks for your answer but that is not quit what I was looking for. Maybe 
>> I
>> need to clearly explain what I want to do.
>>
>> Column B contains "x" values which have been filled in in more than one
>> cell. (B1;B3;B5 etc) Column A contains Names. (A1;A3;A5 etc.) And now I 
>> want
>> in a different worksheet in the same workbook all the valves of Column A 
>> only
>> if Column B is "x"
>>
>> The return value needs to be one per cell. So if there are let's say 3 
>> names
>> each name needs to be returned in a different cell.
>>
>> I think this can't be done with formules but maybe someone has got an 
>> idea.
>>
>>
>>
>>
>>
>>
>>
>>
>> "Gary''s Student" wrote:
>>
>> > To find a single "x" you would use VLOOKUP().  For all the "x"'s, see:
>> >
>> > http://office.microsoft.com/en-us/excel/HA012260381033.aspx
>> > -- 
>> > Gary''s Student - gsnu201001
>> >
>> >
>> > "Golf-Iron7" wrote:
>> >
>> > > What I want to do is the following. I have a worksheet in which I 
>> > > have
>> > > created the following; Name-Column , select-Column. I now want to 
>> > > created a
>> > > formula in a new sheet which check if the are "x" in the select 
>> > > column and
>> > > copy the names of the name-columns to cells in that new sheet. (which 
>> > > is in
>> > > the same workbook) is that possible? If so please let me know.
>> > >
>> > > Thanks in advance. 

0
Ashish
2/27/2010 4:07:35 AM
Reply:

Similar Artilces:

ERROR: Too many cell formats
I keep getting this error message when I try to change the font size of a cell. Sometimes it even crashes the program. Why is it doing this and how can I fix it? Thanks Have a read at this KB article on this problem and fixes. http://support.microsoft.com/default.aspx?scid=kb;en-us;213904 Gord Dibben Excel MVP On Fri, 20 May 2005 08:31:03 -0700, ChrisA <ChrisA@discussions.microsoft.com> wrote: >I keep getting this error message when I try to change the font size of a >cell. Sometimes it even crashes the program. Why is it doing this and how >can I fix it? > >...

Macro to copy last worksheet & insert new sheet
Hi, I have an excel file that is supposed to have the various months. I did a macro that copies the last worksheet (ie, April) and inserts a new worksheet (ie, April (2)) with all the formulas & format of worksheet "April". My question is: 1) how can I amend the VB Code such that the macro will copy the last worksheet showing the last month instead of keep copying from "April'"? 2) is it possible to have the new duplicated worksheet to automatic detect what is the last month and automatically name the new worksheet as the new month? 3) I have a cell i...

Find Copy and Paste
I have yet to write any code purtaining on how to accomplish this so that's why I am asking for help. I have a list of data that is like this.... A B C "Item # 2 "Pressure 9.33E-30" ER 678, Row1 4094.78 'K, 6.543 mm/Min, 1232 mm Row2 "Item # 3 "Pressure 9.33E-30" ER 678, Row3 4094.78 'K, 6.543 mm/Min, 1232 mm Row4 " Item # 4 "Pressure 9.33E-30" ER 678, Row5 4094.78 'K, 6.543 mm/Min, 1232 mm Row6 "Item # 3 "Pressure...

Excel should let me do absolute copies of cell formulas
There are two types of cell copying that MS Excel does not currently provide for: 1) Copying cell formulae from one region into another of the same size without changing them 2) Copying cell formulae from one region into another of the same size without changing them if they reference cells outside that region, but treating them as relative if they are inside the copied region. These would be very useful and would help to cut down on errors made by copying cells. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To...

How do I set up a drop down validation to ignore any blank cells?
I have a drop-down list that refers to list of information that is not always going to have values in every cell in the range/list. I need to know how to set up the drop down so that it only returns values and ignores the blank cells in the range. MLP, I am not really sure if you can do what you are asking or not, but one option might be to make another list that refers to your first list but ignores the blank cells or places them at the end of the list. Use something like the "small" or "large" worksheet function for this (see the help file for more info). Th...

How to do cells(x,y) for merged field
I have huge problem I cant get data from merged cell it is 4-5,9 in excell when i try to point CELLS(4,9) it returns null cells(5,9) return null as well how can i get this data I cant change the .xls file I cannot reproduce your problem. You do understand that the order of arguments in Cells() is row, column? I merged cells I4:I5 and filled the meged cell with "Eureka!" and ran the following macro Sub tryit() For i = 4 To 5 Cells(i, 1) = Range("I" & i).Value Cells(i, 2) = Cells(i, 9).Value Next i End Sub which put "Eureka!" ...

Export report info into Excel?
I have a report that I created and I am wondering if there is a way to export this easily into Excel? Thanks, CEV On Mon, 18 Jun 2007 12:20:57 -0500, CEV wrote: > I have a report that I created and I am wondering if there is a way to > export this easily into Excel? > > Thanks, > > CEV With your report displayed in Preview, (or simply select the report on the Main Database folder) click on the Office Links tool button (it's icon is a W) drop-down arrow and select Analyze it with Microsoft Excel. The Excel file will be placed in the My Documents folder. -- Fr...

Returning top 5 records of a large table
I have a very large table called Service: CREATE TABLE [Service] (ServiceID int, ClientID int, TimeIn datetime) I want to return the last 5 records entered in the table. Basically: SELECT TOP 5 ServiceID, ClientID, TimeIn FROM [Service] ORDER BY TimeIn DESC But this is very inefficient with high IO. The problem is ServiceID is not necessarily contiguous and I don't know the date range of the last 5 services, so I don't have a good candidate for a WHERE clause. Any ideas how to improve this query? Thanks, Dave David, An index on TimeIn would hel...

In a given cell, how can I count how many carragereturn/line feeds are imbedded?
If I have a cell (A3) and want to see how many line feeds are in it, how do I caunt that? How abount any other charater if it is not the same? Thanks Phil I figured it out! It is =(LEN(A3)-LEN(SUBSTITUTE(UPPER(A3),CHAR(10),"")))/LEN(CHAR(10)) Thanks though! Phil "Phillips" <Phillips*NOSPAM*@RecycledReefs.com> wrote in message news:ek8ub.174775$ao4.578644@attbi_s51... > If I have a cell (A3) and want to see how many line feeds are in it, how do > I caunt that? How abount any other charater if it is not the same? > > Thanks > Phil > > See...

Check Box and Button Macros
I have a quick question that I haven�t seen answered here before (although it most certainly has been at some point). But I would appreciate any help! I need the ability to select certain check boxes each representing a certain category and then upon pushing a separate button (Perhaps titled �GENERATE DATA�) the stats for the selected categories appear in new sheets as a graph. I have desperately tried using Macros to do this but can�t figure it out. Here is an image of something my boss gave me depicting how he wants it to work... http://i187.photobucket.com/albums/x55/SweetTea023/BizH...

can conditional formatting varry the area it highlights based on c
I have a template type spreadsheet and i would like to add conditional formatting to it, but i don't know if conditional formatting can do this or not. I want conditional formatting to highlight rows based upon a value in a cell. Example: in cell A1 there is a formula that generates a value of 3 ... i want conditional formatting to then highlight 3 rows ... if the formula generated a 15 it would then highlight 15 rows. Any suggestions? thanks! -- Eric Yes, use a formula like this over all potential rows. =ROW()-ROW($A$10)<=$A$10 --- HTH Bob Phillips ...

Copy to Folder Equivalent in Outlook 2007 #2
In Outlook 2003 when I received a message with an attachment and I opened the attachment, I could select File, Copy to Folder from the menu to save the attached message to a folder as a message. In Outlook 2007 the Move to Folder command is not available while in the attachment. It is available from the original message, but I don't want to save both, just the attachment. Does any know how I can accomplish this in 2007 and why Move to Folder isn't available from the attachment? Thanks, Tanya ...

Updating user info
With 5.5 there was the galmod tool, is there a tool or method to allow users to update their information in the global address list? The galmod tool works somewhat, there is other fields i would like the user to modify, it is easy in 5.5, but what would I change in 2003? ...

Placing a Return in a Function
I am trying to link two cells together with an return between them. Everytime that I do this I end up with a little square in between th two values. An example of the formula that I am using is as follows: =A1&" "&A2 I want my single cell A3 for example to look like this: 23 50 And not like this 23[]50. Please Help. Thanks -- jdurrms ----------------------------------------------------------------------- jdurrmsu's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2712 View this thread: http://www.excelforum.com/showthread.php?threadid=46756...

SEO and usability
Dear News Readers, I have a question about seo and usability for web site: 1. What should I prefered, to use blogger.com or my own blog? blogger.com is preformated, scalirbar - is a question, but most of contra is that- all user are not in my db, and I should import them 2. wenn I use my own blog, then I have all user in my db, but, the question is Rating(PR) of my blog is better or not vs. blogger.com I must subscribe my blog into blogger comunity and submission pages Please, tell me your opinion(pro and contra) about this thema. Best Ragards ===============...

Conditionally format another cell?
Is there a way to change the background color of a cell based on the contents of a different cell? thanks!!! Just use a formula that refers to the other cell. If I want X99 to change depending on what's in A1. I select X99 and then format|conditional formatting Formula is: =$A$1="anythingyouwant" Robin wrote: > > Is there a way to change the background color of a cell based on the > contents of a different cell? > > thanks!!! -- Dave Peterson ...

use a cell to reference a range in a vlookup
I have a spreadsheet that has the same line on it, and would like to automate a vlookup so that i do not have to change the tab name. I have the tab names in a column already, before that cell I was making the range with a formula so that I coudl reference that cell to define my range. That does not seem to be working, does anyone know how to work around that? Example below Formula that works =VLOOKUP("a",Sheet2!1:65536,2,FALSE) Formula that does not work A1 = Sheet2!1:65536 =VLOOKUP("a",A1,2,FALSE) try =VLOOKUP("a",indirect(A1),2,FALSE) "Dan"...

Using COUNTIF (specific cells over multiple sheets) � can anyone help?
Hi I�d be really grateful if someone could assist me in how to �formula the below: I have twelve identically formatted worksheets (�A� through to �J�) In each of the cells B6:F6, B10:F10, B14:F14, B18:F18 & B22:F22 on eac sheet there will be a different one of 5 words (each word can and wil occur more than once within the above range). For the purpose of thi exercise I need to ignore the text in the other rows (eg 7-9, 11-1 etc). What I need to tally on a separate sheet within the workbook is ho many times each of the five words occur in the group above (eg B6:F6 B10:F10, B14:F14, B1...

Email returned as #5.5.0 smtp;553 Recipient rejected
When we send email to certain domains it gets bounced back with the following message: Your message did not reach some or all of the intended recipients. Subject: Subject Sent: 3/14/2005 4:16 PM The following recipient(s) could not be reached: name@recipientdomain.com on 3/14/2005 4:16 PM There was a SMTP communication problem with the recipient's email server. Please contact your system administrator. <mail.ourdomain.com #5.5.0 smtp;553 <name@recipientdomain.com> - Recipient Rejected! SMTP server has delivery restrictions in place...

Extracting Text from a cell #2
Windows XP Office XP I would like a formula that will extract all but the last 10 characters from a text string located in cell C2. For example: My text string is an address line and the last 10 characters will always be zipcode+4 like the following "Cheasapeake Bay, NC 85236-1452" What I want the formula to do is return only the "Chesapeake Bay, NC" portion and leave the 10 digit zipcode string out. Hi use =LEFT(A1,LEN(A1)-10) -- Regards Frank Kabel Frankfurt, Germany "Wendy L" <puddytat_99@hotmail.com> schrieb im Newsbeitrag news:emvVK2UgEHA.140@TK...

EXCEL, VSTO: Fastest way to access multiple cells
Hi all, Can anybody help clarifying my issues? In one part of my Excel/VSTO application, I have to compare values of cells and assign a background color according to the result of the comparison. The code looks something like this: .... rng = wkSheet.Range("A1:Z1000") vals = Array(1000 * 26 ) ' same size as 'rng' Do ( for the whole range ) If vals(i, j) > XXX Then rng(i, j).Interior.ColorIndex = 1 Else If vals(i, j) = XXX Then rng(i, j).Interior.ColorIndex = 2 Else ' vals(i, j) < XXX Then rng(i, j).Interior.ColorIndex = 3 End ...

Check these security pack
--gyyppazzufdjw Content-Type: multipart/related; boundary="fdzsopea"; type="multipart/alternative" --fdzsopea Content-Type: multipart/alternative; boundary="gzpljgxrtvmg" --gzpljgxrtvmg Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Consumer this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to maintain the security of y...

Excel 2007 'Copy as Picture' doesn't paste at 100%
I use Excel 2007 to produce graphs and tables for use in reports prepared in PowerPoint 2007 (the tables contain a lot of numbers and calculations hence why they're not done directly in PowerPoint). I'm trying to use 'Copy as Picture' -> 'As shown when printed' to paste the tables from Excel into PowerPoint so I don't get the grid lines or red comment flags. However when I do the table doesn't paste in at the same size, it is always a bit wider. This happens regardless of whether I'm pasting into PowerPoint or Excel and only happens when...

Converting MFC application into a C++ application with MFC support.
I need to convert an MFC Windows application into a simple C++ application (with MFC support). By the way, the reason is because I want to convert it into a dll later. Can someone advise how to do it? Regards Sam If you have a MFC app, its already a C++ app with MFC support. What does it mean that you wantt to "convert" it? ------------ Ajay Kalra ajaykalra@yahoo.com I need to convert it into adll. I know how to do it to C program. In this case WinMain is converted to DllMain and there are some other simple steps and thats it. What do I do in this case of MFC application? ...

Copying emails to CDROM
I need to copy all emails in my INBOX & SENT folders to CDROM so I can access them via another computer. Please advise how this is done. I am usint Office 2000. Create a personal address book (PAB) which will create a .pst file. Then you can copy the .pst file to another location. You do this by going to tools, email accounts, add a new directory or address book. Click Address Book Click personal address book note the path so you can find it later. Apply, OK You will see "personal folders" in your list of folders. Create a sent and inbox folder, etc. Copy what you ...