copying rows and cells of qualified information

[tvwhome]

Hello all : I'm trying to copy from one worksheet to another, information within one row to another worksheet only if it meets certain cryteria.
I have a block of cells (several rows & columns) that I want to copy some of the data to another worksheet only if one cells value is "1".
Exampe:
Rows 1,2,3 all have information in cells in columns A, thru E

IF information in row # 1, in column B = "1"
THEN I need to have information in row 1, column B,C,E copy over to another sheet.
IF cell B1 = #0, then I don't need to any information for that row copied.

I need to repeat this operation several times, with each acuance being based on the information in a "row by row" basis.

The purpose is to let a client select or unselect(thru the use of the #'s "0", &"1") the items they want to purchase from a list of choices, and then copy over to the new worksheet only the information needed to compile an order tally for the items selected, without copying any information from the items that were not selected.

Please help, or let me know what I can do to make it a little clearer. Hopefuly this makes sense.
H.L.

[martindwilson]

something like this adjust ranges to match your needs

[tvwhome]

something like this adjust ranges to match your needs

Hello Martin and thanks for the help !! I think (ok. so I know) part of the problem is that I'm new to Excel and I'm having some trouble sorting thru the formulas you sent me yesterday.
I'm attaching a mock up of what I'm trying to accomplish to better show you.
It looks like you had set up another worksheet called "Key". What was this for? Do we need to create a new sheet just to send wanted data to just to "stage" it before we then enter it to our desired location? Please help me with that.

Hopefuly the mock worksheets attached will better explain what I'm trying to do.

Thanks again Martin !

Harold

[martindwilson]

this is the same thing but adjusted to your cell ranges the sheet key is used to count instances of 1 on your r original data sheet .every time a 1 is found it increments
since the match function only finds the first instance of 1,2,3 and so on it can be used to get that row.

[tvwhome]

this is the same thing but adjusted to your cell ranges the sheet key is used to count instances of 1 on your r original data sheet .every time a 1 is found it increments
since the match function only finds the first instance of 1,2,3 and so on it can be used to get that row.

Thanks again Martin ! It's starting to "sink in" a little better now !

Also, Martin, is there a way to combine cells to a "IF" comand? I'd like to use a IF command if all of the cells B5,E5 & H5 have a value of "0". Sometimes some of the cells may have a value other than "0"
This is what I'm trying to get away with, but it's not working

=IF(B5:E5:H5=0,0,(H5*G5)/E5)

Thanks Martin !

Harold

[tvwhome]

Hello All: I'm trying to connect multiple cells to a single IF function. Meaning if ALL cells concerned have avalue of "0", then I get a sum of "0".
If any of the cells have a value other than "0", then I pursue a different equation. This what I'm trying but not betting anywhere: Please advise.

=IF(B5:E5:H5=0,0,(H5*G5)/E5)

[martindwilson]

if(and(B5=0,E5=0,H5=0),........ but if E5=0 youl get a div0 error
so do that first,,and if any of the others = 0 youll get 0 any way
so simply
=if(e5=0,0,(H5*G5)/E5)

[tvwhome]

this is the same thing but adjusted to your cell ranges the sheet key is used to count instances of 1 on your r original data sheet .every time a 1 is found it increments
since the match function only finds the first instance of 1,2,3 and so on it can be used to get that row.

Hello again Martin: Please forgive my continued strugles with this formula. I just can't seem to get a grip on the paths these formulas are taking me, and therefore, I'm not getting anywhere. Can you sugest a simplier formula to help me?

Thanks again Martin !

Harold

[martindwilson]

ok start again post a sample workbook showing what you have so far and what you want

[tvwhome]

ok start again post a sample workbook showing what you have so far and what you want

Hello and thank you again Martin: I've attached a workbook for your review. Please advise.

Harold

[martindwilson]

ok here it is

[tvwhome]

Martin: Thank you so much !! It works great !! I've spent so much time on this problem, I about went nuts. I still am having abit of a strugle understanding the paths and comands of your formula completly, but I'm sure it's one of those things that will "click" with me about 3:00 AM some night.
Again, thanks so much for your time and input !

Harold

[tvwhome]

Martin: Thank you so much !! It works great !! I've spent so much time on this problem, I about went nuts. I still am having abit of a strugle understanding the paths and comands of your formula completly, but I'm sure it's one of those things that will "click" with me about 3:00 AM some night.
Again, thanks so much for your time and input !

Harold

Martin: Please see attached worksheet for additional questions. Thank you again !

[martindwilson]

IF(ROW(A1)>COUNTIF('Seed products'!$B$5:$B$100,1),""
this bit says if row(a1) is greater than the count of the number of 1's in column b of 'Seed products'! return blank
as you drag down the formula row(a1) which =1 changes to row (a2) which =2 and so on
the count of 1's in column b of 'Seed products'! is 11 do when the formula gets to row(a12) it returns blank otherwise you's get #n/a
'Seed products'!$A$5:$L$100 has 12 colums the last # says which column to look at
A5:L100 is just the range your data is in it could be A5:L5000

[tvwhome]

Martin : I want to mark this thread as "solved" to give you some credit. But, I don't see how to mark it as solved. Can you help? Thanks soooooo much for your help with all of this. Harold