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 "".

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 "".

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 "".

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 "".

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 "".

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 "".