Newbie - help needed with lookup, please.

Hi - help needed please.  I need a way to read a text string from one cell
(say Sheet1A1), find it in a list in another sheet (say Sheet2 A17), then
copy a numeric value from a cell alongside the found cell (Sheet2 B17, say)
into another cell on the first sheet (Sheet1B1, say).  If the text string
ends in a specific letter combination, the number needs to go into a
different cell (Sheet1C1) - hope that's clearer than mud :-).  The full gory
(to me) problem is posted in "Nursing Schedule workbook" from yesterday - I
guess it was too long winded... :-)

TIA
Dan E
webbie@preferredcountry.com


0
Dan
4/6/2004 2:36:36 PM
excel.newusers 15348 articles. 2 followers. Follow

7 Replies
348 Views

Similar Articles

[PageSpeed] 6

Hi Dan
enter the following formulas in sheet1:
B1:
=IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC","",VLOOKUP(A1,
'sheet2'!$A$1:$B$100,2,0))

C1:
=IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","",VLOOKUP(A1
,'sheet2'!$A$1:$B$100,2,0))

I assumed your letter combination at the end is 'ABC'. Adapt this to
your needs and copy for all rows

--
Regards
Frank Kabel
Frankfurt, Germany

"Dan E" <webbie(removethis)@preferredcountry.com> schrieb im
Newsbeitrag news:eYDQQS#GEHA.2768@tk2msftngp13.phx.gbl...
> Hi - help needed please.  I need a way to read a text string from one
cell
> (say Sheet1A1), find it in a list in another sheet (say Sheet2 A17),
then
> copy a numeric value from a cell alongside the found cell (Sheet2
B17, say)
> into another cell on the first sheet (Sheet1B1, say).  If the text
string
> ends in a specific letter combination, the number needs to go into a
> different cell (Sheet1C1) - hope that's clearer than mud :-).  The
full gory
> (to me) problem is posted in "Nursing Schedule workbook" from
yesterday - I
> guess it was too long winded... :-)
>
> TIA
> Dan E
> webbie@preferredcountry.com
>
>

0
frank.kabel (11126)
4/6/2004 2:42:56 PM
Wow - was that quick!  Many thanks, Frank - I have to go out, but will be
onto this within a few hours.  Again, many thanks.  As a final step, if
Sheet1Ax is blank or a hyphen (representing a null entry), is there a way to
have the formulae enter a null value (a hyphen) into the cells that
otherwise would contain a numeric value (Sheet1Bx and Cx)?  And place a
hyphen into whichever of Bx or Cx doesn't get the number from the two
formulae you gave?

TIA
Dan E
webbie@preferredcountry.com
"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:e2lN0V%23GEHA.1076@TK2MSFTNGP10.phx.gbl...
> Hi Dan
> enter the following formulas in sheet1:
> B1:
> =IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC","",VLOOKUP(A1,
> 'sheet2'!$A$1:$B$100,2,0))
>
> C1:
> =IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","",VLOOKUP(A1
> ,'sheet2'!$A$1:$B$100,2,0))
>
> I assumed your letter combination at the end is 'ABC'. Adapt this to
> your needs and copy for all rows
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Dan E" <webbie(removethis)@preferredcountry.com> schrieb im
> Newsbeitrag news:eYDQQS#GEHA.2768@tk2msftngp13.phx.gbl...
> > Hi - help needed please.  I need a way to read a text string from one
> cell
> > (say Sheet1A1), find it in a list in another sheet (say Sheet2 A17),
> then
> > copy a numeric value from a cell alongside the found cell (Sheet2
> B17, say)
> > into another cell on the first sheet (Sheet1B1, say).  If the text
> string
> > ends in a specific letter combination, the number needs to go into a
> > different cell (Sheet1C1) - hope that's clearer than mud :-).  The
> full gory
> > (to me) problem is posted in "Nursing Schedule workbook" from
> yesterday - I
> > guess it was too long winded... :-)
> >
> > TIA
> > Dan E
> > webbie@preferredcountry.com
> >
> >
>


0
Dan
4/6/2004 3:00:55 PM
Wow - was that quick!  Many thanks, Frank - I have to go out, but will be
onto this within a few hours.  Again, many thanks.  As a final step, if
Sheet1Ax is blank or a hyphen (representing a null entry), is there a way to
have the formulae enter a null value (a hyphen) into the cells that
otherwise would contain a numeric value (Sheet1Bx and Cx)?  And place a
hyphen into whichever of Bx or Cx doesn't get the number from the two
formulae you gave?

TIA
Dan E
webbie@preferredcountry.com

"Frank Kabel" <frank.kabel@freenet.de> wrote in message
news:e2lN0V%23GEHA.1076@TK2MSFTNGP10.phx.gbl...
> Hi Dan
> enter the following formulas in sheet1:
> B1:
> =IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC","",VLOOKUP(A1,
> 'sheet2'!$A$1:$B$100,2,0))
>
> C1:
> =IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","",VLOOKUP(A1
> ,'sheet2'!$A$1:$B$100,2,0))
>
> I assumed your letter combination at the end is 'ABC'. Adapt this to
> your needs and copy for all rows
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Dan E" <webbie(removethis)@preferredcountry.com> schrieb im
> Newsbeitrag news:eYDQQS#GEHA.2768@tk2msftngp13.phx.gbl...
> > Hi - help needed please.  I need a way to read a text string from one
> cell
> > (say Sheet1A1), find it in a list in another sheet (say Sheet2 A17),
> then
> > copy a numeric value from a cell alongside the found cell (Sheet2
> B17, say)
> > into another cell on the first sheet (Sheet1B1, say).  If the text
> string
> > ends in a specific letter combination, the number needs to go into a
> > different cell (Sheet1C1) - hope that's clearer than mud :-).  The
> full gory
> > (to me) problem is posted in "Nursing Schedule workbook" from
> yesterday - I
> > guess it was too long winded... :-)
> >
> > TIA
> > Dan E
> > webbie@preferredcountry.com
> >
> >
>


0
Dan
4/6/2004 3:31:50 PM
Hi
try
B1:
=IF(A1="","",IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC",""
,VLOOKUP(A1,
'sheet2'!$A$1:$B$100,2,0)))

and C1:
=IF(A1="","",IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","
",VLOOKUP(A1
,'sheet2'!$A$1:$B$100,2,0)))



--
Regards
Frank Kabel
Frankfurt, Germany

"Dan E" <webbie(removethis)@preferredcountry.com> schrieb im
Newsbeitrag news:eEQgT$#GEHA.2844@tk2msftngp13.phx.gbl...
> Wow - was that quick!  Many thanks, Frank - I have to go out, but
will be
> onto this within a few hours.  Again, many thanks.  As a final step,
if
> Sheet1Ax is blank or a hyphen (representing a null entry), is there a
way to
> have the formulae enter a null value (a hyphen) into the cells that
> otherwise would contain a numeric value (Sheet1Bx and Cx)?  And place
a
> hyphen into whichever of Bx or Cx doesn't get the number from the two
> formulae you gave?
>
> TIA
> Dan E
> webbie@preferredcountry.com
> "Frank Kabel" <frank.kabel@freenet.de> wrote in message
> news:e2lN0V%23GEHA.1076@TK2MSFTNGP10.phx.gbl...
> > Hi Dan
> > enter the following formulas in sheet1:
> > B1:
> >
=IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)="ABC","",VLOOKUP(A1,
> > 'sheet2'!$A$1:$B$100,2,0))
> >
> > C1:
> >
=IF(RIGHT((VLOOKUP(A1,'sheet2'!$A$1:$B$100,2,0),3)<>"ABC","",VLOOKUP(A1
> > ,'sheet2'!$A$1:$B$100,2,0))
> >
> > I assumed your letter combination at the end is 'ABC'. Adapt this
to
> > your needs and copy for all rows
> >
> > --
> > Regards
> > Frank Kabel
> > Frankfurt, Germany
> >
> > "Dan E" <webbie(removethis)@preferredcountry.com> schrieb im
> > Newsbeitrag news:eYDQQS#GEHA.2768@tk2msftngp13.phx.gbl...
> > > Hi - help needed please.  I need a way to read a text string from
one
> > cell
> > > (say Sheet1A1), find it in a list in another sheet (say Sheet2
A17),
> > then
> > > copy a numeric value from a cell alongside the found cell (Sheet2
> > B17, say)
> > > into another cell on the first sheet (Sheet1B1, say).  If the
text
> > string
> > > ends in a specific letter combination, the number needs to go
into a
> > > different cell (Sheet1C1) - hope that's clearer than mud :-).
The
> > full gory
> > > (to me) problem is posted in "Nursing Schedule workbook" from
> > yesterday - I
> > > guess it was too long winded... :-)
> > >
> > > TIA
> > > Dan E
> > > webbie@preferredcountry.com
> > >
> > >
> >
>
>

0
frank.kabel (11126)
4/6/2004 4:03:48 PM
Hi Dan,

Just a heads up, your software or something seems to be issuing repeat
identical posts.  It seems odd to that these duplicate posts are issued
about a half hour apart.  That happened in your "Nursing Schedule Workbook"
in the "public.excel" section too.  It's not a big deal, but you might want
to check to see if you can issue just one response instead of several
duplicates.

I am glad to see that you broke up your original message into bite sized
chunks and that Frank is giving you excellent support.

Good luck!

Best regards,
Kevin


0
nospam710 (114)
4/6/2004 5:33:30 PM
Hi Kevin - yes, I noticed that - haven't had that trouble before - Outlook
Express seemed to be having trouble sending messages even after I
refreshed - DSL connection was OK all the time.  Maybe I've screwed up some
setting.  Anyway - apologies.  If it happens again I'll put some work into
troubleshooting the problem.

Regards,

Dan E
webbie@preferredcountry.com

"Kevin Stecyk" <nospam@no123spam.com> wrote in message
news:u7J3C1$GEHA.2224@TK2MSFTNGP12.phx.gbl...
> Hi Dan,
>
> Just a heads up, your software or something seems to be issuing repeat
> identical posts.  It seems odd to that these duplicate posts are issued
> about a half hour apart.  That happened in your "Nursing Schedule
Workbook"
> in the "public.excel" section too.  It's not a big deal, but you might
want
> to check to see if you can issue just one response instead of several
> duplicates.
>
> I am glad to see that you broke up your original message into bite sized
> chunks and that Frank is giving you excellent support.
>
> Good luck!
>
> Best regards,
> Kevin
>
>


0
Dan
4/6/2004 8:03:25 PM
Dan,

No big deal.  I just thought you might want to keep an eye on it.

Best regards,
Kevin


"Dan E" <webbie(removethis)@preferredcountry.com> wrote in message
news:uBd9OOBHEHA.580@TK2MSFTNGP11.phx.gbl...
> Hi Kevin - yes, I noticed that - haven't had that trouble before - Outlook
> Express seemed to be having trouble sending messages even after I
> refreshed - DSL connection was OK all the time.  Maybe I've screwed up
some
> setting.  Anyway - apologies.  If it happens again I'll put some work into
> troubleshooting the problem.
>


0
nospam710 (114)
4/6/2004 9:39:23 PM
Reply:

Similar Artilces:

Help with Birth Dates
I have a list of names and birthdates. The dates are as follows. 19840822. Year Day Month. How can I sort this so it is listed in month day year order?? Hi Sillysamiam! To get dates use: =DATE(A1/10000,MID(A1,5,2),RIGHT(A1,2)) To sort use Data > Text to columns Then sort based on those three columns in Month > Day > Year order. -- Regards Norman Harker MVP (Excel) Sydney, Australia njharker@optusnet.com.au Excel and Word Function Lists (Classifications, Syntax and Arguments) available free to good homes. "Sillysamiam" <sillysamiam44@yahoo.com> wrote in messag...

Selective report printing please help
Hi Under Transactions menu > Financials > Series post Say i check mark 2 or 3 records and post them I want to selectively print eg - 1st page of report ok print , 2nd page not required. etc Rick Unfortunately, what you want to do is not possible. -- Charles Allen, MVP "Rick" wrote: > Hi > > Under Transactions menu > Financials > Series post > Say i check mark 2 or 3 records and post them > I want to selectively print eg - 1st page of report ok print , 2nd page not > required. > etc > > Rick ...

repost: 30 day evaluation key needed
Hello all, A while ago we've requested the MS POS 2.0 demo-cd 9a 30 day evaluation key was included). Now one of our customers is interested in a demo but since our 30-day demo has expired we cannot run neither install a new demo. The given key can only be used once. I would gladly like to know how i can get a new 30day evaluation key. many thanks in advance! -- please can someone from Microsoft contact me! it's rather important. I'm trying to give our customer a evaluation of MS POS. If MS POS does not comply to their needs we should look at MS RSM2.0 (and that can be a...

Help open Excel 2007
My Excel 2007 tries to open but closes immediately. I cannot open any files in excel. Please help. Thank you. Maybe you have some code in your Personal.xslb that is in the workbooks open event (Personal.xslb is the equivalent to Personal.xls found in xl2003), see if you can locate and open this. You could try holding down the shift key when opening a file and see what happens. Does it happen with every excel file? -- The Code Cage Team Regards, The Code Cage Team http://www.thecodecage.com ------------------------------------------------------------------------ The Code Cage Team...

HELP #38
i have several email accounts i need to manage. and cant figure out how to set up outlook. I dont know my incoming and outgoing servers. Lila wrote: > i have several email accounts i need to manage. and cant figure out > how to set up outlook. I dont know my incoming and outgoing servers. Ask your ISP? Lila wrote: > i have several email accounts i need to manage. and cant figure out > how to set up outlook. I dont know my incoming and outgoing servers. Hi - you need to ask your ISP for this information. Also: please post by including a concise summary of your question in t...

Cannot view picture attachment when put in folder..HELP
I received pictures as an attachment. I wanted to save them so I put them in a created folder. Now when I open the folder and click on the e mail and try to open the attchment their muted and it won't let me click on them. What should I do? Thank you for your help. JOE Joe <jfsa1219@hotmail.com> wrote: > I received pictures as an attachment. I wanted to save them so I put > them in a created folder. Now when I open the folder and click on the > e mail and try to open the attchment their muted and it won't let me > click on them. A Windows or an Outlook folder? ...

Windows7 help: associating Word 2007 for .doc files
Hey guys, Whenever I try to open .doc files, it uses "WordPad" (the word processing program that came with Windows7) to open the files & sometimes it's unreadable with weird symbols. I used to just right click on the file & do "open with.." & choose the "Windows Office Word Viewer 2003" for files that don't look right (weird symbols) in WordPad... I just bought & installed Office2007 on my computer which is using Windows 7. Now that I have Word 2007, I want to make it the default program for .doc files so I don't have...

lookup and transfer data
I have multiple spreadsheets that I want to move certain information from to get all my info into one spreadsheet. Is there anyway to search mutiple spreadsheets by a column like "Part Number" and tell it to retrive another column like "Qty" then post it into a corresponding column in another worksheet by matching "Part Number"?? Hi some questions upfront: - how are your sheets named exactly - columns IDs - Are you searching for a numeric value only - Is there only one occurence of each part number -- Regards Frank Kabel Frankfurt, Germany ExcelDummy wrote...

newbie: Dev. pluggin, want to have dialogs
Hello- I'm developing a plugin using the software vendors API. I would like to have dialogs in the plugin that will present the user with certain options. The project is a DLL(non MFC), but I have included certain headers to allow me to use CList, etc. My question(finally) is.... what files to I need to include to create a dialog? I would like to know that absolute bare minimum I need to have a simple modal dialog. I'm not even sure if I can accomplish this, but it would be so nice to use windows dialogs over the native API UI system. Also, what do I need to change in the project to...

Help with wildcard Replace syntax, please?
I tried a wildcard Find/Replace: -- Find: 5 spaces, any combination of 2 numbers, a period ( {5,})[0-9]{2,}. -- Replace: 9 spaces, then "a." ( {9})a. Find worked. Replace gave me: " {9}a." I gave up trying to code it and just typed 9 spaces! 8>/ Is there something special I needed to do in the Replace box? Ed You must enter into the Replace box exactly what it is that you want to be used as the replacement Though, you could have used ([ ]{4}) [0-9]{2} in the Find what control and then \1\1 a in the Replace with control -- Hope this...

Help! need to fit 4 - 3.75"x5.25" cards (all different) on one page
Hi, I am new to ms publisher 2003, and I am trying to create a document that does the following: - about 50 unique thankyou cards - fit 4 or so on one page Thanks! When you say 'unique' do you mean you are Merging data and/or pictures to the cards 4 to a page? A little more information please? -- The US should free all those illegally held prisoners they are torturing, abusing and denying human rights being held at Guantanamo Bay. Hi, Each card will be typed manually, and I would like to fit for of these cards on one page/sheet of paper (to save paper). I will then cut ...

Lookup record dialog box
The lookup dialog box field for different types of activities are different. For example, if we add job title in the find columns for Contact find (uder form and view). The job title is seachable in Phone activity Receipeint field, but it is not in To field of email. The two lookup foms also look different. Is there a way to make Job title searchable on Email lookup dialog box? Thanks ...

Event ID 5892 (on EXCH Server) HELP!!!
Situation: Vanilla install of CRM. Only problem was I could not send an email from CRM and received an error that was corrected by changin the permissions on my exchange server's "vsi 1" folder. But now, I get a 5892 error on each incoming and outgoing message saying it can't send to all servers. Mail sent through the web interface appear to go from the client, but never arrive. Email arriving never gets to the CRM server. Any ideas? Joseph Michaud Fixed it. Had to add the CRM server netbios name to the webpage host header. :) Whee! ...

Help req: how to conditional count with words in cells based on their formatting
I have a text based spread sheet that counts the number of words at the end of each row. I want to be able to bold selected words and have these excluded from the count at the end of the row e.g. frog cat dog (If cat is bolded then the count at the end of the row is 2 not 3). Thanks in advance. Babs That will require VBA code. Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "babs" wrote in message I have a text based spread sheet that counts the number of words at the end of each row. I wan...

lookup record
is it possible to add more tabs into lookup records dialog: eg: create a quote -> add a product -> click the find icon then it pops up the lookup record dialog it only shows product name + product ID is it possible to change one of these columns or add new columns? ...

need advice on big project
well, big for us anyway... 2 developers, 1 year. I am looking for general good approaches for handling this. Given VS 2008, a collection of some old legacy code, and 2 man years we need to build a new front end that allows users to manage a library of components that can then be built into assemblies, that can then be built into models of designs or real systems that are then analyzed by the legacy code and compared with performance requirements... the performance requirements are also user managed. conceptualy i see this as several collections of generic objects that could be handl...

Instr help
Hello, I've spent quite a bit of time in Excel using the SEARCH function to parse out names from a text field thinking I'd be able to use this same expression once I brought the data into Access. As I find out I guess I need to be using the InStr function in Access but I'm still having a little trouble. Here's an example of the text field : 00022956CTE/Smith,Harry And here's an example of the SEARCH function I used in Excel which produced "Harry": =+RIGHT(C2,LEN(C2)-SEARCH(",",C2,1)) When I try to run an Update query to a blank field with...

I need help getting started
I have Office 2007 and I am having trouble getting my e-mail address configured with Outlook. I get to the point where they want me to log into the server but when I input my information it doesn't accept it. I don't know what I am doing wrong. PLEASE HELP! "Laura" <Laura@discussions.microsoft.com> wrote in message news:2745354F-ED15-42A8-B3E5-41286A439EB2@microsoft.com... >I have Office 2007 and I am having trouble getting my e-mail address > configured with Outlook. I get to the point where they want me to log into > the server but when I input my inform...

help me with excel formula please
A B can somebody help with a formula in the B colunm so that it 1 1 give values added upwards in the A colunm 2 (1+2) 3 (1+2+3) 4 ( 1+2+3+4) advance thanks In B1 enter: =A1 In B2 enter: =B1+A2 and copy down -- Gary''s Student - gsnu200903 "shaanu" wrote: > A B can somebody help with a formula in the B colunm so > that it > 1 1 give values added upwards in the A colunm > 2 (1+2) > 3 (1+2+3) > 4 ( 1+2+3+4) > ...

PLEASE HELP ME with page numbering
I posted this earlier, waited all day for a response only to be told that I was on a Mac bulletin board that I was led to with a bad interface???? anyway here is my question. I am DESPERATE my page numbers are not showing in my document and I am at the end of my > rope. I am using MS word 2007 and Vista - I am able to go through all the > menus for header, footer, format page numbers, etc. but then no final product > shows up. > > The help desk at my school is clueless and offered no help whatsoever. > > I have spent HOURS trying to insert these page numbers today. I ha...

Convert OL 2003 PST file for use in OL2000
I need to use my Outlook 2003 pst on an older machine that has Outlook 2000. I am working to beat the clock, as this needs to be functional by morning. Any suggestions?? thanks in advance Rick Export your messages to an Outlook 97-2002 pst-file. For detailed instructions see; http://www.howto-outlook.com/howto/downgradefromoutlook2003.htm -- Robert Sparnaaij [MVP-Outlook] www.howto-outlook.com Tips of the month: -Navigation Pane Tips & Tricks -Create an Office 2003 CD slipstreamed with Service Pack 1 ----- "Rick" <anonymous@discussions.microsoft.com> wrote in me...

Help with adding a bit to an existing If Statement
I have a inherited a spreadsheet with formulae in it - this one gives m a number in column B which is great but I would like to add something t it so that it also looks at column P and if column P does *not* have th word "Unrefreshed" the formula below will return a value of zero =IF(ISBLANK(G30),"",VLOOKUP(G30,MachineData,3,FALSE)) Many thanks in advanc -- Message posted from http://www.ExcelForum.com Assuming you aret just testing P30 for the single word "Unrefreshed", then something like: =NOT(P30="Unrefreshed)*IF(ISBLANK(G30),"",VLOOKUP(G3...

desperately need help with average
This is what i have & works but need to do more AO6:AO66 = Names from week 1 =IF(AW6>0,AW6,'Week 1'!S6) AQ6:AQ66 = week 1 hours & adjusted hours =IF(AO6>"",E6+AZ6,"") AR6:AR66 =L means laidoff=IF(AY6>0,AY6,"") AS6:AS66 = Average hours {=IF(OR(AR6="L",AW6>""),AVERAGE(IF(AT$6:AT$66>=0,AT$6:AT$66)),"")} AT6:AT66 = total hours of this week =IF(AR6="L","",AU6) AU6:AU66 = Total hours of week 1 & this week=IF(AR6="L",AS6,IF(B6="","",AQ6+SUM(F6:Q6)...

Help in Subtotal
I have a list containing thousands of data like Product Name, Code Quantity, Rate, Value etc. Generally I use subtotal for productwise total and use sum function fo Quantity, value etc. through subtotal how i can get Code with Product name total ? I think i am not able to understand u so please go through m attachment file. thanks in advance. s kara +------------------------------------------------------------------- |Filename: example.doc |Download: http://www.excelforum.com/attachment.php?postid=2676 +---------------------------------...

Help needed to add commets, arrows, etc to existing graph...
I am wanting to add comments and arrows (that look proper) and fill i the area between two lines on a line graph i have created in excel. Ca someone please guide me on how this may be done... I was thinking: i. there may be a way to do it in excel that i am not aware of, ii. there may be another charting program which will draw the chart with all the frills from data exported from excel, iii. there may be another program that allows me to add stuff t already created excel charts, iv. something else. My aim is to export these graphs to word - they will form part of report i am writting. An...