Numeric calculation

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Flying_Dutcman - 19 Jun 2006 14:01 GMT

I have created an 'IF' statement which reads as follows

=IF('General Stock'!C2=1,'General Stock'!B2,"=if('General
Stock'!C3=1,'General Stock'!B3,=if('General Stock'!C4=1,'General
Stock'!B4,=if('General Stock'!C5=1,'General Stock'!B5,=if('General
Stock'!C6,'General Stock'!B6,=if('General Stock'!C7=1,'General
Stock'!B7,=if('General Stock'!C8=1,""))))))")

The problem is that if the number 1 appears in the C collum of the General
Stock sheet it pulls it through correctley but I need the formula to continue
searching for the next correct value on the General Stock sheet. At the
moment the correct answer is found in cell C3 of the General Stock sheet so
it is repeated twice instead of searching for the next correct answer and
pulling through that value to the first sheet.

Reply to this Message

Ardus Petus - 19 Jun 2006 14:19 GMT

Try:
=INDEX('General Stock'!B2:B8,MATCH(1,'General Stock'!C2:C8))

(I've not tried it myself!)

HTH
--
AP

>I have created an 'IF' statement which reads as follows
>
[quoted text clipped - 12 lines]
> it is repeated twice instead of searching for the next correct answer and
> pulling through that value to the first sheet.

Reply to this Message

Flying_Dutcman - 19 Jun 2006 14:41 GMT

Doesn't work yet. I need the following info to go to different sheets
according to the number in the type of item colum.
B C
ITEM DESCRIPTION    TYPE OF ITEM
Pen, Ball Point, Black Ink 1
Pen, Ball Point, Red Ink    1
Pen, Ball Point, Blue Ink    3
Paper, A4, 80 gsm, ream    4
Paper, A4, 80 gsm    5
Paper, A3, 90 gsm    1
pen    1

It needs to look something like this:

B C
Pen, Ball Point, Black Ink 1
Pen, Ball Point, Red Ink    1
Paper, A3, 90 gsm    1
pen    1

If you can please help. It's very urgent for a stocktake at work?

> Try:
> =INDEX('General Stock'!B2:B8,MATCH(1,'General Stock'!C2:C8))
[quoted text clipped - 21 lines]
> > it is repeated twice instead of searching for the next correct answer and
> > pulling through that value to the first sheet.

Reply to this Message

Bob Phillips - 19 Jun 2006 16:06 GMT

Add a heading to the number column of say NUM

In H1 add NUM
H2, add 1

Goto data>Filter>Advanced Filter and check the copy to another location
button, add H1:H2 in the Criteria range, and add say M1 to the Copy To
range.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Doesn't work yet. I need the following info to go to different sheets
> according to the number in the type of item colum.
[quoted text clipped - 43 lines]
> > > it is repeated twice instead of searching for the next correct answer and
> > > pulling through that value to the first sheet.

Reply to this Message

Flying_Dutcman - 20 Jun 2006 07:39 GMT

Don't know if I'm doing something wrong but it's still not pulling through
like it should. Do you have a mailing adress so that I can send you the
workbook? Please? I really appreciate the help.

> Add a heading to the number column of say NUM
>
[quoted text clipped - 4 lines]
> button, add H1:H2 in the Criteria range, and add say M1 to the Copy To
> range.

Reply to this Message

Bob Phillips - 20 Jun 2006 11:21 GMT

Yeah in the post heading, but note my signature.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Don't know if I'm doing something wrong but it's still not pulling through
> like it should. Do you have a mailing adress so that I can send you the
[quoted text clipped - 8 lines]
> > button, add H1:H2 in the Criteria range, and add say M1 to the Copy To
> > range.

Reply to this Message

Flying_Dutcman - 20 Jun 2006 12:06 GMT

Thanks. Sent it through to your mail this morning.

> Yeah in the post heading, but note my signature.
>
[quoted text clipped - 10 lines]
> > > button, add H1:H2 in the Criteria range, and add say M1 to the Copy To
> > > range.

Reply to this Message

Bob Phillips - 21 Jun 2006 10:56 GMT

I have responded.

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> Thanks. Sent it through to your mail this morning.
>
[quoted text clipped - 12 lines]
> > > > button, add H1:H2 in the Criteria range, and add say M1 to the Copy To
> > > > range.

Reply to this Message