Results 1 to 19 of 19
  1. #1

    Join Date
    Dec 2001
    Age
    42
    Posts
    8,595
    vCash
    500
    Rep Power
    2418

    Microsoft Excel forumla help!

    Hi everyone,

    I am stuck with a formula, and I figured I would check here to see if there are any experts...

    I need to feed information from one sheet to another based on a variable. Here goes:

    If Page 2's Cell A2 equals Page 1's F2, then the cell in question will equal Page 2's cell F2

    This will be repeatable for hundreds of rows. Basically, I need to say that if a certain cell is a certain customer name, then this cell is a certain ship date.

    Make sense?

    Thanks in advance.

  2. #2

    Join Date
    May 2002
    Posts
    5,319
    vCash
    500
    Rep Power
    9947
    What version of Excel are you using?

    Is this what you're trying to do?

  3. #3
    drinky typo pbp, closet hugger
    Join Date
    Feb 2003
    Location
    c'est genifique!
    Posts
    29,978
    vCash
    325
    Rep Power
    38422
    It sounds ilke you might want to use the vLOOKUP (or hLOOKUP) formula - it searches for specific data out of a table array and plugs in an answer based on what is found. Is that what you're trying to do?
    Q: Why can't I read the competition threads?
    A: Competition forums on the board are available to those with a Season Pass or a premium membership How to View Kiss & Cry

  4. #4

    Join Date
    Dec 2001
    Age
    42
    Posts
    8,595
    vCash
    500
    Rep Power
    2418
    On Tab 1:
    A--------------------B
    1 Business Name----Date

    On Tab 2:
    A--------------------B
    1 Business Name----Date

    I need a formula that picks up B1 on Tab 2 if Tab 2 A1 is equal to Tab 1 Tab A

    I know that this sounds like a lot of work for something that I could just copy and paste, but I have all of the customers listed on Tab 1 with TONS of other information. Tab 2 will grow as we pick up more and more materials. Rather than having to match everything up, I want to be able to automate it. Does this make sense?

    I am using Excel 2003 for PC.

  5. #5

    Join Date
    Dec 2002
    Posts
    9,269
    vCash
    567
    Rep Power
    3372
    Jayar, it sounds like you need to learn about arrays. I'm not that great with those but this is a blog site I've found to be very helpful about that kind of thing.

    http://chandoo.org/wp/

    Here's the archive page where you can see past topics.
    http://chandoo.org/wp/archives/

    If you can't find what you need, he has a section for questions.
    http://chandoo.org/forums/
    "Half the harm that is done in this world is due to people who want to feel important. They don't mean to do harm -- but the harm does not interest them. Or they do not see it, or they justify it because they are absorbed in the endless struggle to think well of themselves." – T.S. Eliot

  6. #6

    Join Date
    Dec 2005
    Location
    New England, USA
    Posts
    5,960
    vCash
    470
    Rep Power
    12551
    I think you're looking for

    =IF(Sheet1!A2=Sheet2!F2,Sheet2!F2,"no match")

    type that into the receiving cell.
    Last edited by Aceon6; 03-04-2011 at 10:12 PM.
    AceOn6, the golf loving skating fan

  7. #7

    Join Date
    Dec 2001
    Age
    42
    Posts
    8,595
    vCash
    500
    Rep Power
    2418
    Quote Originally Posted by Aceon6 View Post
    I think you're looking for

    =IF(Sheet1!A2=Sheet2!F2,Sheet2!F2,"no match")

    type that into the receiving cell.
    Edited: OOOPS! No, it didn't work.
    Last edited by Jayar; 03-04-2011 at 10:19 PM.

  8. #8

    Join Date
    Apr 2001
    Location
    Downing cosmos before the free dance
    Posts
    1,701
    vCash
    500
    Rep Power
    176
    Lets see if this is what you're looking for (rw is the row number, a and b are column headings):

    sheet1
    rw.....a....b
    1......x...dog
    2......y...cat
    3......z...bird

    sheet2
    rw.....a....b
    1......x...blue
    2......x...green
    3......z...yellow

    in column c of sheet2, I put the following function:
    =IF(A1=Sheet1!A1,B1," ")
    (if A1 equals sheet1,A1, then use B1, else leave blank)

    Sheet2 ends up looking like this:

    sheet2
    rw.....a....b.......c
    1......x...blue.....blue
    2......x...green... <blank>
    3......z...yellow..yellow

    Is that what you're looking for? did that even make sense?


    ETA... Damn... in the time it took me to type this up, you already got the answer (and much more concisely, LOL).
    Erm.... I got nothin'

  9. #9

    Join Date
    Dec 2005
    Location
    New England, USA
    Posts
    5,960
    vCash
    470
    Rep Power
    12551
    Quote Originally Posted by Jayar View Post
    Edited: OOOPS! No, it didn't work.
    Hmmm. Not sure why. I'll dig out the old Excel book and see if there's something I forgot.

    You may need to play around with the $ symbol. For example, $Sheet1! means that the reference is always on Sheet 1. Don't do that with the cell, though, the expression $A$2 won't work as it won't change the formula when you do your copies.
    AceOn6, the golf loving skating fan

  10. #10

    Join Date
    Dec 2001
    Age
    42
    Posts
    8,595
    vCash
    500
    Rep Power
    2418
    Quote Originally Posted by Aceon6 View Post
    I think you're looking for

    =IF(Sheet1!A2=Sheet2!F2,Sheet2!F2,"no match")

    type that into the receiving cell.
    It pulls a value from Sheet 2, but it doesn't pull the correct one. If Sheet1 A2 is Disney and Sheet2 A2 is Disney, then I need it to pick up the date in Sheet2 F2. However, Disney won't necessary be in Sheet2 A2. It could be in Sheet2 A1222. I need the formula to only grab the date when it matches the company name... and I have hundreds of companies to do.

  11. #11

    Join Date
    May 2002
    Posts
    5,319
    vCash
    500
    Rep Power
    9947
    Does this work?

  12. #12

    Join Date
    Feb 2005
    Location
    Melbourne, Australia
    Age
    48
    Posts
    17,938
    vCash
    500
    Rep Power
    34819
    I can't really help you there. Working out forumulas can be a real challenge. But once they work it is so satisfying. Particularly if it solves a big problem (which I have had to do a work recently).
    When you are up to your arse in alligators it is difficult to remember you were only meant to be draining the swamp.

  13. #13

    Join Date
    Apr 2001
    Location
    Downing cosmos before the free dance
    Posts
    1,701
    vCash
    500
    Rep Power
    176
    Quote Originally Posted by Jayar View Post
    It pulls a value from Sheet 2, but it doesn't pull the correct one. If Sheet1 A2 is Disney and Sheet2 A2 is Disney, then I need it to pick up the date in Sheet2 F2. However, Disney won't necessary be in Sheet2 A2. It could be in Sheet2 A1222. I need the formula to only grab the date when it matches the company name... and I have hundreds of companies to do.
    So... is this saying that you have Disney in sheet 1 A1, and you know it will be somewhere in sheet 2, column A, but you dont know where. You need to find it on sheet 2, column A, and when found, you need the value in that same row, column F? Something like that?

    something like...
    where sheet1.a1 = sheet2.aN then receiving cell = sheet2.fN (where N is the row it was found in)?

    Hmmmm....
    Erm.... I got nothin'

  14. #14
    Registered User
    Join Date
    Jun 2004
    Location
    South of New York City
    Posts
    2,062
    vCash
    500
    Rep Power
    0
    You need to use a VLOOKUP function. Let me play with it - I have the same version of Excel. (I love Excel problems. Anytime someone's stuck on an Excel problem, they call me and ask for "The Excellent Excel Help Desk" Except DH, who's really annoying in describing his problem with too many words. I'm working here!)

    Here's my working example:

    PART 1:
    The tab "Sheet 2" contains the values to be "found" by the lookup function:

    Code:
    	-A-	-B-		-C-	-D-	-E-	-F-	
    1	BusName	Street1		City	State	Zip	Date	
    2	ABC   	123 Mickey Street	Anywhere	FS	12345	03/20/11	
    3	APPLE 	1 Apple Way	Anywhere	FS	12345	03/09/11	
    4	DISNEY	246 Duckie Drive	Anywhere	FS	12345	03/12/11	
    5	IBM   	One IBM Court	Anywhere	FS	12345	03/01/11
    This is called an "Array" when you read the Help info.

    The Lookup function will only work properly IF this list contains one and only one row for each BusName value.
    Otherwise, you have to change the "Range Lookup" parameter. If you need to do this, let me know and I'll write it up for you.

    Notice that the data is in order by the BusName in Column A. This is important because the lookup functions require the array to be in order by that "key" column value. If it's not in order, sort it using <alt><data><sort> and keep it in that order going forward or your lookups will go awry when you add a row that is out of sequence.

    I could refer to this range as 'S1'!A1:F5 in the vlookup formula, but since the list may grow and you don't want to recode the formulas, it's better to refer to it as 'S1'!A:F and save yourself a headache later when you forget about this step.

    The Absolute Reference talulabell suggests works, but you'll have to update all the formulas if rows are added or deleted from the lookup array.

    You can also "name" the range, but you'd also have to adjust the rows if you add/remove things from this list.

    Use these approaches if they make more sense to you, but if you start getting errors in your formulas or invalid "not founds", check your formula's lookup range or replace it with 'S1'!A:B.

    PART 2:
    The tab "S2" contains the order information, where the customer name can appear more than once, looks like this:
    Code:
    	-A-	-B-	-C-	
    1	ORDER#	BusName	Date	
    2	101	DISNEY		
    3	102	ABC   		
    4	103	IBM   		
    5	104	APPLE 		
    6	105	DISNEY		
    7	106	ABC   		
    8	107	IBM   		
    9	108	APPLE 		
    10	109	DISNEY		
    11	110	ABC   		
    12	111	IBM   		
    13	112	APPLE 		
    14	113	DISNEY		
    15	114	ABC   		
    16	115	IBM   		
    17	116	APPLE 		
    18	117	NBC
    If I type this formula into Cell C2:
    =VLOOKUP(B2,'S1'!A:F,6,0)

    ...and copy it down the column to the other cells, I end up with this:

    Code:
    	-A-	-B-	-C-
    1	ORDER#	BusName	Date
    2	101	DISNEY	3/12/2011
    3	102	ABC	3/20/2011
    4	103	IBM	3/1/2011
    5	104	APPLE	3/9/2011
    6	105	DISNEY	3/12/2011
    7	106	ABC	3/20/2011
    8	107	IBM	3/1/2011
    9	108	APPLE	3/9/2011
    10	109	DISNEY	3/12/2011
    11	110	ABC	3/20/2011
    12	111	IBM	3/1/2011
    13	112	APPLE	3/9/2011
    14	113	DISNEY	3/12/2011
    15	114	ABC	3/20/2011
    16	115	IBM	3/1/2011
    17	116	APPLE	3/9/2011
    18	117	NBC	#N/A
    Notice the "N/A" in the last entry? That's because NBC isn't on the other list to be found. Let's modify the formula to test for that error and show "Not Found" instead.

    PART 3

    Type this formula into Cell C2:

    =IF(ISNA(VLOOKUP(B2,'S1'!A:F,6,0)),"Not Found",VLOOKUP(B2,'S1'!A:F,6,0))

    ...and copy it to the other cells, like so:

    Code:
    	-A-	-B-	-C-
    1	ORDER#	BusName	Date
    2	101	DISNEY	3/12/2011
    3	102	ABC	3/20/2011
    4	103	IBM	3/1/2011
    5	104	APPLE	3/9/2011
    6	105	DISNEY	3/12/2011
    7	106	ABC	3/20/2011
    8	107	IBM	3/1/2011
    9	108	APPLE	3/9/2011
    10	109	DISNEY	3/12/2011
    11	110	ABC	3/20/2011
    12	111	IBM	3/1/2011
    13	112	APPLE	3/9/2011
    14	113	DISNEY	3/12/2011
    15	114	ABC	3/20/2011
    16	115	IBM	3/1/2011
    17	116	APPLE	3/9/2011
    18	117	NBC	Not Found
    Voila!
    Last edited by FigureSpins; 03-05-2011 at 12:46 AM. Reason: Added solution and formatted for readability; revised to match Jayar's workbook columns

  15. #15

    Join Date
    Apr 2001
    Location
    Downing cosmos before the free dance
    Posts
    1,701
    vCash
    500
    Rep Power
    176
    Yes! vlookup. It's something like this:

    =VLOOKUP(A1,Sheet2!$A$1:$B$22,2)

    I did this on sheet 1, and my receiving cell was also on sheet 1, so if there is no "Sheet!" indicator, it's because that cell is also sheet 1.

    A1 is the first cell it's looking up. Disney, in sheet 1, A1. this cell reference is relative, meaning, when you copy this for subsequent rows, it will change for each row (A1, A2, A3, etc).

    Sheet2!$A$1:$B$22 references the 2nd worksheet, cells A1 thru B22 (I put the company name in column A, random dates in B). These cell references are absolute. meaning, when you copy this formula down several rows, it should always stay A1..B22 (the dollar signs make it absolute). You dont want the row numbers increasing for each row you copy, or your range will be off.

    Forgot the third part of the statement:
    the final 2 is the column of the above range that holds the data you want. In my case it was the second column where I had the data. This is relative to what you select as your range. you count the number of columns in your range (not from column A, if that's not part of your range).

    Final edit:
    I had some issues with this working on later rows, and discovered it was because my first column of that range was not sorted. My A1-B22 range. The range you reference must (I think) be sorted, ascending, by the first column.
    Last edited by talulabell; 03-04-2011 at 11:52 PM.
    Erm.... I got nothin'

  16. #16

    Join Date
    Dec 2001
    Age
    42
    Posts
    8,595
    vCash
    500
    Rep Power
    2418
    WAHOO! IT WORKS! YOU ARE ALL ROCK STARS!!!! THANK YOU!!!
    Last edited by Jayar; 03-05-2011 at 12:54 AM. Reason: Because I wanted to...

  17. #17
    Registered User
    Join Date
    Jun 2004
    Location
    South of New York City
    Posts
    2,062
    vCash
    500
    Rep Power
    0
    Great - glad you can knock off work now! Enjoy the weekend!
    Last edited by FigureSpins; 03-05-2011 at 02:29 AM.

  18. #18

    Join Date
    Feb 2005
    Location
    Melbourne, Australia
    Age
    48
    Posts
    17,938
    vCash
    500
    Rep Power
    34819
    FigureSpins - how did get a different font on your reply?

    I will call upon you next time I need Excel help.
    When you are up to your arse in alligators it is difficult to remember you were only meant to be draining the swamp.

  19. #19
    Registered User
    Join Date
    Jun 2004
    Location
    South of New York City
    Posts
    2,062
    vCash
    500
    Rep Power
    0
    I used the font menu on the Advanced post editor. I think it's the "Fixedsys" font.
    (My User Control Panel / Options are set for the "Enhanced Interface - full WYSIWIG editor.")

    You can also type the font tags:

    [ font=Fixedsys ]This test is in Fixedsys font[ /font ] yields this : This test is in Fixedsys font

    The little boxes are created using the [ code ] [ /code ] tags.

    (Take out the extra spaces between the brackets.)
    Last edited by FigureSpins; 03-05-2011 at 03:14 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •