If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Formula help
Take a look at Chip's site.
Mike wrote: I have tried several COUNTIF's in the formula =IF(ISNA(VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE),"",VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE)) but it keeps returning the the same information. "Mike" wrote: The Auto filter doesnt do it. I think because the data is in another sheet. "Dave Peterson" wrote: =vlookup() returns a single value. Can you drop the formula and just autofilter the data -- starts with "SA 60"? You could use multiple formulas in multiple cells. Chip Pearson explains how: http://www.cpearson.com/Excel/TablesAndLookups.aspx (Look for Arbitrary Lookups) Since you're looking at the first 5 characters, you'll have to incorporate that into the formula, too. Mike wrote: Thanks Dave and Squeaky. Is there a way to continue looking for more people who are working the assignment in A8 and list it under in the same column? "Dave Peterson" wrote: =vlookup() supports wild cards, so you could use: =IF(ISNA(VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE),"", VLOOKUP($A8&"*",'SHIFT D'!C$10:J$33),8,FALSE)) Mike wrote: here is the formula as of now after applying it to my application. =IF(ISNA(VLOOKUP($A8,('SHIFT D'!C$10:J$33),8,FALSE)),"",VLOOKUP($A8,('SHIFT D'!C$10:J$33),8,FALSE)) It is written in sheet A, cell B8. A8 reads "SA 60". In sheet "Shift D" the cells that have "SA 60" also contain more data, i.e. "SA 60 8A-4P". How would you have it search for only the "SA 60" part? Also, I need it to contiune looking for more than one person an return that in the cell beneth cell B9 and so on, if there is more than no person working that assignment. "Mike" wrote: Yes, that worked, except I inserted rows in between rows 6 and 7 for more than one name to add if more than one person is working that assignment like on MONDAY it should return JONES and DOE for PT 61/1. "Squeaky" wrote: Hi Mike, If your table is pretty much set up as shown, you can use a vlookup formula to work this out, however you will need to make some adjustments. (There are other ways to do this too.) In a vlookup table the data you are looking for (in the first case PT60) must be in the first column of the lookup table with the answer you are looking for (Smith, Jones, etc.) somewhere after that. Yours are in front. Therefore using your table I put the headers starting in A1 and spanning across to column G just like you did, as follows: A B C D E F G NAME SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY Smith W T DO DO W PT60 Doe T PT61/1 PT60 PT61/1 DO DO Jones PT60 PT61/1 W T PT60 PT In cell H2 I put =$A2 and dragged it down the length of the name list to duplicate the names that are in A2-A4, THEN drag those across to column M and down, so you have as many "smiths" etc. as you have days of the week. This will give the lookup formula the names to "find". You can hide these columns later if you don't want them to be seen. (You could just refernece the names in cloumn H, but then you would need to adjust every formula in the VLOOKUP. This is easier.) In cells A6-A7 place the items you want to look up. PT 60 PT61/1 In cell B6, directly under Sunday place the formula: =IF(ISNA(VLOOKUP($A6,(B$2:H$4),7,FALSE)),"",VLOOKU P($A6,(B$2:H$4),7,FALSE)) Drag/copy it over to cell G6, then down to row 7. You can drag it down more if you add items to find in column A. In row 6 you should see the correct names in the correct cells. I suggest recreating this to see if it works for you, then adjust it to your needs. Let me know if it works. Squeaky. "Mike" wrote: I am trying to find a formula to search for text in a range of cells that if found it would return data in another cell. For instance, Retun Jones in the Sunday cell in the XXXX cell and so on. Also Tuesday it would return Doe and Jones in the SA 61/1 XXXX cell on Monday. Name Sunday Monday Tuesday Wednesday Thursday Friday Smith W T DO DO W PT 60 Doe T PT 61/1 PT 60 PT 61/1 DO DO Jones PT 60 PT 61/1 W T PT 60 PT PT 60 XXXX XXXX XXXX XXXX XXXX XXXX PT61/1 XXXX XXXX XXXX XXXX XXXX XXXX -- Dave Peterson . -- Dave Peterson . -- Dave Peterson |
|
Thread Tools | |
Display Modes | |
|
|