Microsoft Excel forumla help!

Discussion in 'Off The Beaten Track' started by Jayar, Mar 4, 2011.

  1. Jayar

    Jayar Well-Known Member

    Joined:
    Dec 22, 2001
    Messages:
    8,614
    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. Gazpacho

    Gazpacho Well-Known Member

    Joined:
    May 25, 2002
    Messages:
    5,339
    What version of Excel are you using?

    Is this what you're trying to do?
     
  3. genevieve

    genevieve drinky typo pbp, closet hugger Staff Member

    Joined:
    Feb 26, 2003
    Messages:
    30,365
    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?
     
  4. Jayar

    Jayar Well-Known Member

    Joined:
    Dec 22, 2001
    Messages:
    8,614
    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. mpal2

    mpal2 Well-Known Member

    Joined:
    Dec 29, 2002
    Messages:
    9,276
  6. Aceon6

    Aceon6 Get off my lawn

    Joined:
    Dec 22, 2005
    Messages:
    6,288
    I think you're looking for

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

    type that into the receiving cell.
     
    Last edited: Mar 4, 2011
    Jayar and (deleted member) like this.
  7. Jayar

    Jayar Well-Known Member

    Joined:
    Dec 22, 2001
    Messages:
    8,614
    Edited: OOOPS! No, it didn't work.
     
    Last edited: Mar 4, 2011
  8. talulabell

    talulabell New Member

    Joined:
    Apr 18, 2001
    Messages:
    1,701
    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).
     
    Jayar and (deleted member) like this.
  9. Aceon6

    Aceon6 Get off my lawn

    Joined:
    Dec 22, 2005
    Messages:
    6,288
    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.
     
  10. Jayar

    Jayar Well-Known Member

    Joined:
    Dec 22, 2001
    Messages:
    8,614
    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. Gazpacho

    Gazpacho Well-Known Member

    Joined:
    May 25, 2002
    Messages:
    5,339
    Does this work?
     
  12. Aussie Willy

    Aussie Willy Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    18,089
    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).
     
  13. talulabell

    talulabell New Member

    Joined:
    Apr 18, 2001
    Messages:
    1,701
    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....
     
  14. FigureSpins

    FigureSpins New Member

    Joined:
    Jun 18, 2004
    Messages:
    2,062
    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: Mar 5, 2011
    Gazpacho, Aussie Willy, Jayar and 3 others like this.
  15. talulabell

    talulabell New Member

    Joined:
    Apr 18, 2001
    Messages:
    1,701
    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: Mar 4, 2011
  16. Jayar

    Jayar Well-Known Member

    Joined:
    Dec 22, 2001
    Messages:
    8,614
    WAHOO! IT WORKS! YOU ARE ALL ROCK STARS!!!! THANK YOU!!!
     
    Last edited: Mar 5, 2011
    Gazpacho and (deleted member) like this.
  17. FigureSpins

    FigureSpins New Member

    Joined:
    Jun 18, 2004
    Messages:
    2,062
    Great - glad you can knock off work now! Enjoy the weekend!
     
    Last edited: Mar 5, 2011
  18. Aussie Willy

    Aussie Willy Well-Known Member

    Joined:
    Feb 18, 2005
    Messages:
    18,089
    FigureSpins - how did get a different font on your reply?

    I will call upon you next time I need Excel help.
     
  19. FigureSpins

    FigureSpins New Member

    Joined:
    Jun 18, 2004
    Messages:
    2,062
    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: Mar 5, 2011