if cell x> 0 then copy range of cells

**spanker** · 04-25-2009, 06:10 PM

hi all
new to the forum and looking for some help on a spreadsheet

i have a sheet with a list of products,prices quantity etc

what i want to do is if a cell has a greater value than o then copy a range of cells to another sheet in the next available row

could this be done with =IF or would it need a macro to do it

any help would be great.

**Jocote46** · 04-25-2009, 06:22 PM

could you provide a sample of the spreadsheet?

**spanker** · 04-25-2009, 06:26 PM

just uploaded

**JBeaucaire** · 04-25-2009, 06:37 PM

Here's a NON-array, non-VBA technique. It uses as KEY on the main sheet to number the rows that have a QTY greater than 0.

Then on the Order Form, it uses a standard INDEX/MATCH against its own key column to bring over the matching data from matching keys. All in realtime.

Once it's all working, you could hide the key columns if desired.

**spanker** · 04-25-2009, 07:50 PM

thanks for the quick reply

it sort of does what i want but can it remove the selections if there not selected? sort of like a shopping basket/add to cart

ive uploaded the actual workbook ive been working on so you can see waht im trying todo.

thanks

**JBeaucaire** · 04-25-2009, 08:43 PM

Yeah, your results sheet leaves much to the imagination...not an actual "looks like this" example, so I'm guessing here.

**spanker** · 04-27-2009, 02:51 PM

sorry it was a bit vague
ive uploaded the sheet with the desired output on the mats order, if they are selected on sheet1 the corresponding details should be transfered to the mats order sheet and removed if deselected.

im trying to make it so i only have to edit sheet 1 which will transfer the relevant details to the other sheets.

thanks.

**JBeaucaire** · 04-27-2009, 07:32 PM

Ok, this will sort your list for you. Notice the hidden key columns in the black margins on both the Sheet1 and the mats order sheet? These are used to create unique matching row info to draw the items over.

I installed a User Defined Function called IFERROR, it's a function builtin to Excel 2007. This allows me to try and INDEX/MATCH the first set of items, and when an error occurs, the second formula kicks in and tries again on the second set of item.

Once you see how it's working, you can color the font of the key columns (currently red letters) back to black so they disappear from view.

**spanker** · 04-28-2009, 04:06 PM

Thanks JBeaucaire

thats just what im after,

but the data thats fetched from sheet1 in the first table only seems to bring the ref column data over to all the corresponding columns in the mats order, the other table works great, but the formulas look the same i cant seem to see any difference.

**JBeaucaire** · 04-28-2009, 05:15 PM

My bad, the formula for K21 is supposed to be:

=IF($B21>$B$20,"",iferror(INDEX(Sheet1!$E$21:$E$42,MATCH($B21,Sheet1!$A$21:$A$42,0)),INDEX(Sheet1!$L$21:$L$42,MATCH($B21,Sheet1!$Q$21:$Q$42,0))))

I highlighted the part that was wrong. In and INDEX/MATCH formula, the part that is "indexed" is where the answers are returning from.

**spanker** · 04-28-2009, 05:32 PM

if you select other item 3 from the second table (insect) in the order column in sheet 1, it brings the corresponding data from sheet 1 (Ref Qty Description Price Order Total) etc into mats order,

but if you select item 5 from the first table (rodent) in sheet 1 it populates the rows in mats order with the data from the ref colum in item 5

hope i explained it a bit better.

thanks.

**spanker** · 04-28-2009, 05:58 PM

that did it

you really are a rocket scientist!!

thanks for all your help

**JBeaucaire** · 04-28-2009, 07:40 PM

Last tip for you...notice how I UNMERGED all those merged cells on the mat orders sheet? Merged cells interfere with a LOT of useful functions in Excel, not the least of which is simple copy/paste functions. And it's unnecessary.

To get the same appearance as a merged set of cells, select the cells and hit CTRL-1 > Alignment > Horizontal > Center across selection.

==============
If that takes care of your need, be sure to EDIT your original post (Go Advanced) and mark the PREFIX box [SOLVED]

**spanker** · 04-29-2009, 12:19 PM

I shall use that tip in future.

thanks again.

if cell x> 0 then copy range of cells

LinkBack

Thread Tools

Rate This Thread

Display

if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Re: if cell x> 0 then copy range of cells

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions