Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Flying_Dutcman
 
Posts: n/a
Default Numeric calculation

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.
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Flying_Dutcman
 
Posts: n/a
Default Numeric calculation


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?

"Ardus Petus" wrote:

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

(I've not tried it myself!)

HTH
--
AP

"Flying_Dutcman" a écrit dans le
message de news: ...
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.




  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Numeric calculation

Add a heading to the number column of say NUM

In H1 add NUM
H2, add 1

Goto dataFilterAdvanced 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.

--
HTH

Bob Phillips

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

"Flying_Dutcman" wrote in message
...

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?

"Ardus Petus" wrote:

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

(I've not tried it myself!)

HTH
--
AP

"Flying_Dutcman" a écrit dans

le
message de news: ...
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.






  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Flying_Dutcman
 
Posts: n/a
Default Numeric calculation

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.

"Bob Phillips" wrote:

Add a heading to the number column of say NUM

In H1 add NUM
H2, add 1

Goto dataFilterAdvanced 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.

--
HTH

Bob Phillips

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




  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Numeric calculation

Yeah in the post heading, but note my signature.

--
HTH

Bob Phillips

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

"Flying_Dutcman" wrote in message
...
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.

"Bob Phillips" wrote:

Add a heading to the number column of say NUM

In H1 add NUM
H2, add 1

Goto dataFilterAdvanced 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.

--
HTH

Bob Phillips

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




  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Flying_Dutcman
 
Posts: n/a
Default Numeric calculation

Thanks. Sent it through to your mail this morning.

"Bob Phillips" wrote:

Yeah in the post heading, but note my signature.

--
HTH

Bob Phillips

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

"Flying_Dutcman" wrote in message
...
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.

"Bob Phillips" wrote:

Add a heading to the number column of say NUM

In H1 add NUM
H2, add 1

Goto dataFilterAdvanced 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.

--
HTH

Bob Phillips

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





  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default Numeric calculation

I have responded.

--
HTH

Bob Phillips

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

"Flying_Dutcman" wrote in message
...
Thanks. Sent it through to your mail this morning.

"Bob Phillips" wrote:

Yeah in the post heading, but note my signature.

--
HTH

Bob Phillips

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

"Flying_Dutcman" wrote in

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

"Bob Phillips" wrote:

Add a heading to the number column of say NUM

In H1 add NUM
H2, add 1

Goto dataFilterAdvanced 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.

--
HTH

Bob Phillips

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







  #9   Report Post  
Junior Member
 
Posts: 3
Default

As pen my knowledge if you want to set these data in the columns and it is automatic then you can do the programming on that, when the comma is catch you should change the data field in the DBMS programming. This is the right way to set them in the table.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Find Numeric Criterion in Column & Return the Numeric Value from Row above Sam via OfficeKB.com Excel Worksheet Functions 6 April 27th 06 02:50 PM
Return Summed Count of Multiple Consecutive Numeric Values Sam via OfficeKB.com Excel Worksheet Functions 4 April 10th 06 10:35 PM
Match 3 Criteria and Return Lowest Numeric Value Sam via OfficeKB.com Excel Worksheet Functions 16 April 4th 06 12:19 AM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 07:16 PM
Positioning Numeric Values Resulting from 6 Column Array Formula Sam via OfficeKB.com Excel Worksheet Functions 2 January 5th 06 03:03 AM


All times are GMT +1. The time now is 10:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"