need formula to search column for a word and return another word

I have a multi-worksheet workbook where the 1st worksheet is an assets 
summary page and the other worksheets are for tracking individual assets.  
Each row of the individual asset worksheet represent an asset request.  A 
column on the assets worksheet titled "order status" will have one of three 
inputs for each request: "Open", "Pending", or "Complete".  On the summary 
worksheet, each row represents an individual asset.  There is a column on the 
summary worksheet titled "Item Status".  I need a formula to look at the 
asset worksheet "Order Status" column.  If it finds "Open", it returns "Out" 
in cell.  If it does not find "Open", but finds "Pending", it returns 
"Reserved".  If it do not find "Open" or "Pending", it return "Available".  
If it doesn't find anything in the first cell of the row it returns "".
0
Skyline (4)
11/10/2005 12:27:33 AM
excel.misc 78881 articles. 5 followers. Follow

5 Replies
473 Views

Similar Articles

[PageSpeed] 46

In the summary worksheet, in the "Item Status" column, enter this formula

=IF(Sheet1!A1="open","out",IF(Sheet1!A1="pending","reserved","available"))

Replace Sheet1 with the name of the assets worksheet (tab)
Replace A1 with the cell that you want it to look at on the assets worksheet

Pamela :)

"Skyline" wrote:

> I have a multi-worksheet workbook where the 1st worksheet is an assets 
> summary page and the other worksheets are for tracking individual assets.  
> Each row of the individual asset worksheet represent an asset request.  A 
> column on the assets worksheet titled "order status" will have one of three 
> inputs for each request: "Open", "Pending", or "Complete".  On the summary 
> worksheet, each row represents an individual asset.  There is a column on the 
> summary worksheet titled "Item Status".  I need a formula to look at the 
> asset worksheet "Order Status" column.  If it finds "Open", it returns "Out" 
> in cell.  If it does not find "Open", but finds "Pending", it returns 
> "Reserved".  If it do not find "Open" or "Pending", it return "Available".  
> If it doesn't find anything in the first cell of the row it returns "".
0
pameluh (38)
11/10/2005 2:29:01 AM
Ok so that doesn't solve your problem EXACTLY, but it's damn close.  You'll 
have to figure out the "".  I wouldn't want to do ALL the work for you.  :)

"pameluh" wrote:

> In the summary worksheet, in the "Item Status" column, enter this formula
> 
> =IF(Sheet1!A1="open","out",IF(Sheet1!A1="pending","reserved","available"))
> 
> Replace Sheet1 with the name of the assets worksheet (tab)
> Replace A1 with the cell that you want it to look at on the assets worksheet
> 
> Pamela :)
> 
> "Skyline" wrote:
> 
> > I have a multi-worksheet workbook where the 1st worksheet is an assets 
> > summary page and the other worksheets are for tracking individual assets.  
> > Each row of the individual asset worksheet represent an asset request.  A 
> > column on the assets worksheet titled "order status" will have one of three 
> > inputs for each request: "Open", "Pending", or "Complete".  On the summary 
> > worksheet, each row represents an individual asset.  There is a column on the 
> > summary worksheet titled "Item Status".  I need a formula to look at the 
> > asset worksheet "Order Status" column.  If it finds "Open", it returns "Out" 
> > in cell.  If it does not find "Open", but finds "Pending", it returns 
> > "Reserved".  If it do not find "Open" or "Pending", it return "Available".  
> > If it doesn't find anything in the first cell of the row it returns "".
0
pameluh (38)
11/10/2005 2:41:04 AM
I can figure out the "", but I really need to be able to look at the entire 
"order status" column, not just one cell.

"pameluh" wrote:

> Ok so that doesn't solve your problem EXACTLY, but it's damn close.  You'll 
> have to figure out the "".  I wouldn't want to do ALL the work for you.  :)
> 
> "pameluh" wrote:
> 
> > In the summary worksheet, in the "Item Status" column, enter this formula
> > 
> > =IF(Sheet1!A1="open","out",IF(Sheet1!A1="pending","reserved","available"))
> > 
> > Replace Sheet1 with the name of the assets worksheet (tab)
> > Replace A1 with the cell that you want it to look at on the assets worksheet
> > 
> > Pamela :)
> > 
> > "Skyline" wrote:
> > 
> > > I have a multi-worksheet workbook where the 1st worksheet is an assets 
> > > summary page and the other worksheets are for tracking individual assets.  
> > > Each row of the individual asset worksheet represent an asset request.  A 
> > > column on the assets worksheet titled "order status" will have one of three 
> > > inputs for each request: "Open", "Pending", or "Complete".  On the summary 
> > > worksheet, each row represents an individual asset.  There is a column on the 
> > > summary worksheet titled "Item Status".  I need a formula to look at the 
> > > asset worksheet "Order Status" column.  If it finds "Open", it returns "Out" 
> > > in cell.  If it does not find "Open", but finds "Pending", it returns 
> > > "Reserved".  If it do not find "Open" or "Pending", it return "Available".  
> > > If it doesn't find anything in the first cell of the row it returns "".
0
Skyline (4)
11/10/2005 1:00:22 PM
I tried using your formula in the Asset Summary worksheet in the cell that 
cooresponds to the individual asset worksheet.  Instead of entering a single 
cell, I entered the range T4:T8.  This is my formula: 

=IF('B-1A'!T4:T8="Open","Out",IF('B-1A'!T4:T8="Pending","Reserved","Available"))

It returned a #VALUE!

"pameluh" wrote:

> Ok so that doesn't solve your problem EXACTLY, but it's damn close.  You'll 
> have to figure out the "".  I wouldn't want to do ALL the work for you.  :)
> 
> "pameluh" wrote:
> 
> > In the summary worksheet, in the "Item Status" column, enter this formula
> > 
> > =IF(Sheet1!A1="open","out",IF(Sheet1!A1="pending","reserved","available"))
> > 
> > Replace Sheet1 with the name of the assets worksheet (tab)
> > Replace A1 with the cell that you want it to look at on the assets worksheet
> > 
> > Pamela :)
> > 
> > "Skyline" wrote:
> > 
> > > I have a multi-worksheet workbook where the 1st worksheet is an assets 
> > > summary page and the other worksheets are for tracking individual assets.  
> > > Each row of the individual asset worksheet represent an asset request.  A 
> > > column on the assets worksheet titled "order status" will have one of three 
> > > inputs for each request: "Open", "Pending", or "Complete".  On the summary 
> > > worksheet, each row represents an individual asset.  There is a column on the 
> > > summary worksheet titled "Item Status".  I need a formula to look at the 
> > > asset worksheet "Order Status" column.  If it finds "Open", it returns "Out" 
> > > in cell.  If it does not find "Open", but finds "Pending", it returns 
> > > "Reserved".  If it do not find "Open" or "Pending", it return "Available".  
> > > If it doesn't find anything in the first cell of the row it returns "".
0
Skyline (4)
11/10/2005 1:39:13 PM
Help,
I'm looking to develope a spreadsheet that will show when to trade or sale 
assets.

"pameluh" wrote:

> In the summary worksheet, in the "Item Status" column, enter this formula
> 
> =IF(Sheet1!A1="open","out",IF(Sheet1!A1="pending","reserved","available"))
> 
> Replace Sheet1 with the name of the assets worksheet (tab)
> Replace A1 with the cell that you want it to look at on the assets worksheet
> 
> Pamela :)
> 
> "Skyline" wrote:
> 
> > I have a multi-worksheet workbook where the 1st worksheet is an assets 
> > summary page and the other worksheets are for tracking individual assets.  
> > Each row of the individual asset worksheet represent an asset request.  A 
> > column on the assets worksheet titled "order status" will have one of three 
> > inputs for each request: "Open", "Pending", or "Complete".  On the summary 
> > worksheet, each row represents an individual asset.  There is a column on the 
> > summary worksheet titled "Item Status".  I need a formula to look at the 
> > asset worksheet "Order Status" column.  If it finds "Open", it returns "Out" 
> > in cell.  If it does not find "Open", but finds "Pending", it returns 
> > "Reserved".  If it do not find "Open" or "Pending", it return "Available".  
> > If it doesn't find anything in the first cell of the row it returns "".
0
otrain (2)
11/18/2005 10:00:03 PM
Reply:

Similar Artilces:

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

if cell is text move left one column
ColB is a long list with sections names followed by category codes I need to move the text into colA leaving colB with codes only (all numbers) ColB. Doors 940590 555998 447006 447008 810697 810705 810706 810707 Windows 619435 525691 525692 Try Sub Macro1() Dim lngRow As Long For lngRow = 1 To Cells(Rows.Count, "B").End(xlUp).Row If Not IsNumeric(Range("B" & lngRow)) Then Range("A" & lngRow).Value = Range("B" & lngRow).Text Range("B" & lngRow).Value = "" End If Next End Sub -- Jacob ...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

punblisher and word prcoessing software
can publisher be used as word processing sofware? Not very successfully, it's a desktop publishing program. If you don't have the $, download and install OpenOffice. OpenOffice Writer is an excellent word processor — very similar to MS Word. "shelagh" <shelagh@discussions.microsoft.com> wrote in message news:834CE68F-4492-466A-91DC-694F807C578C@microsoft.com... > can publisher be used as word processing sofware? It *could* but it's not the best tool for that job. Specifically what do you want to do? -- JoAnn Paules Microsoft MVP - Publisher How to ask...

100% stacked column chart shows wrong information
I wonder if someone can help me. I have created a table that tracks the profitability of a particular project and I’m using the table as a source for a 100% Stacked Column graph. The source data for the graph is in three rows: • B21:M21 - contain the names of the months from Nov – Oct • B22:M22 - money taken in • B24:M24 - profit B23:M23 contains the money paid to supplier each month. I need this in the table but I don’t need to show it in the graph. I just want to show profit as a proportion of what we bill to customers on that project. My problem is that the graph seems to show in...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

crm consultant needed asap
i am looking for a crm consultant who has a lot of experience with form customization, crm implimentation, heavy work flow, and activities. ideally someone in arizona but not required. telecommute will be considered for the right person. looking for someone for a possible 2 month (i am guessing here....) project. if you are available and have this experience please email me at j-e-f-f@mag-en-ta-tech.c-o-m (remove the -). we are looking for someone to start like next week at the latest (this message was posted 08-08-2004). This message was posted 8/4, not 8/8. And I have some swamp land in f...

Return Receipt
Hi - I received a message with a "return Receipt" and said yes not remebering that I was not at my home computer. My ISP does not allow remote sending - so now everytime I hit send/receive it tries to send the receipt. There is nothing in the outbox or send box. Any ideas on how to get rid of this? Thanks ...

Starting an Office Program (word, excel) in Outlook to send email
I'm taking an Outlook 2002 class. The latest assignment requires I open a Microsoft app in Outlook to send an email. Troubleshooting so far: Have tried to install some addl features from CD, restarted the pc (O/S WINXP) but still get error "Cannot complete action". Get no reason why Outlook cannot perform this action and the HELP hasn't only points me to inserting an object or some variation thereof. My Outlook is configured to use with AOL 9.0. Steps (abbreviated) given in textbook to perform: Actions New mail using Microsoft Office Also tried to perform from Wo...

How do I copy the result of a check box into another check box?
I'm trying to copy the result of a "check box form field" into another check box. For example: if I check (or uncheck) one box in a form, another box later in the protected form will also be checked (or unchecked) , much like the ref + F9 command for the text form field. Is this possible? Thanks. This cannot be done without macros. If your project will allow macros - see http://word.mvps.org/faqs/tblsfldsfms/ExclusiveFmFldChbxs.htm -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> G...

Sorting on Column headers
I have a spreedsheet that someone else created and I need to take the Column headers, which are in date form, and rearrange them in chronological order. Can this be done without cutting and pasting? I really need to save time with this project if possible. Thanks!:) --- Message posted from http://www.ExcelForum.com/ > I have a spreedsheet that someone else created and I need to take the > Column headers, which are in date form, and rearrange them in > chronological order. Can this be done without cutting and pasting? I > really need to save time with this project if possib...

Accommodating for empty cells in this formula?
I have a formula in cell H21, for example, reads like this: =IF($G21<>"",($H20-$G21),"") is there a way to adjust the formula so that an empty cell in G21 doesn't give the #VALUE! in subsequent cells in column H? Just to give a similar example, this formula =SUMIF(A1:A9,"<>0") adjusts for any and all empty cells in A2 to A9. It no longer matters if any of the cells are empty, the formula correctly gives the correct addition of A1 plust a sum of everything between A2 to A10 without any #VALUE! results. Was hoping to have the formula above als...

how can i edit the positioning of the balloon comment in a word fi
how can i edit the positioning of the balloon comment in a microsoft word file ? please reply on my email What you can do is adjust the space reserved for the balloons in the margin. In Word 2007, on the Review tab, click Track Changes, and then click Change Tracking Options. Change the "Preferred width" setting. -- Stefan Blom Microsoft Word MVP "melikelmalik" <melikelmalik@discussions.microsoft.com> wrote in message news:80E5F3D3-04A0-4E81-B154-FA8459B25F00@microsoft.com... > how can i edit the positioning of the balloon comment in a mi...

creating a spredsheet and log the info into another spredsheet
hello, I have a excel sheet that is printed out and a cashier manual enters information, invoice number, invoice amount, cash amount, check amount, amex amount.... I would like to have the cashier input this information on her PC and print a copy to go along with the deposit and at the same time log the information into a google excel document I created. Any ideas how this can be done? ...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...

Installed Font does not display in Word 2007
Only 6 of of 8 recently installed TTF fonts will display in Word 2007 Are the missing ones variants like Italic or Bold? On Dec 25, 1:28=A0pm, GWHA <G...@discussions.microsoft.com> wrote: > Only 6 of of 8 recently installed TTF fonts will display in Word 2007 ...

Word 2004 and kyocera printer issue
Hi, I am running a fully patched MAC OSX.4 machine with Office 2004 fully patched. I have installed the latest Kyocera OS X English 10.2 drivers from the kyocera website, for KM7530 model. The driver installs a job accounting feature in the printing presets for that particular printer. The function will work fine for every program other than word, word will not allow you the option to enter a pin code. all the features seem to be greyed out. If i reinstall and go immediately to anotherr program such as Eudora or Firefox or even Excel I can enter and lock the pin number in and it will print...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

Character Length in a column
I have a column where every cell must have 14 characters. Lets say have the name Angel. I need 9 more charcters. Next cell down have the word cake. I need 10 more charcters.How do I automatciall insert the spaces I need to have 14 charcters. HELP PLEASE!!!!! GOIN NUTS ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements One way: Do you care about what characters are added? I'l...

Duplicating one Field from One table to Another
Hi - I have two tables - one position, one personnel - which has a 1-to-many relationship (1 position record to many personnel records). The department had a new requirement which made it necessary to change some coding (I inherited this). I'm using tab forms so that when a position is pulled up, you can click on the tab that has the personnel information (if there is any). There is a button on the Personnel form that allows the user to add a new Personnel record. Since I am using an Auto-number field in the Position table (which doubles as the PK) the functionality is fine. Wh...

Freeze the side column/top row & scroll others
what is the function to set (lock in or freeze) the first column and / or top row of a spreadsheet, so the words and numbers remain in the same place as you scroll the other columns and rows. (so you can add more columns..yet keep the main information in the first column/row) Freeze Panes..... In older versions of Excel, it is under Window. In 2007 version of Excel, it is under View. You first select a cell, then activate the command. Excel uses the selected cell's upper left corner to define the freeze point. Play with it. You can also Unfreeze panes that were fro...