How to copy the contents of a cell if a neighbouring cell is greater than zero

[thefa11guy]

SO I am trying to create a sheet that will essentially automatically generate a Menu based on a given nutritional requirement.

So in this example, I want to automatically copy the name from “A” Column to another sheet within the same file if the associated number in “E” column is above zero.

Now I know I can use an absolutely reference to move it to another sheet, that isn’t my confusion. My confusion is that I am not aware of any formula that will do what I need. Can anyone point me to the right direction of a formula that will do this.

EDIT: I’ve just had another thought although I’ll be honest and say I have very little experience of Macros. But could a Macro be created that could be attached to a button. once all items in column “E” have been input, hit the button and it outputs all the values from Column A that match the criteria to another sheet?

I am terrible at Macros so wouldn’t even know where to start with that one.

Many thanks

[igorlius]

Does the result have to be places into seperate cells or is it fine, if the result of all “Food Items” which have a portion number larger than zero is places into one cell (with linebreak seperating them)?

[thefa11guy]

Ideally all in their own cells within a column but I could make it work if they all were in a single cell.

[erAck]

Select the data range (or in this case where there is a contiguous sequence both vertically and horizontally just place the cell cursor on A1, range will be selected automatically) and invoke menu Data → More Filters → Standard Filter…; Make sure that under Options Range contains column labels is checked. Then select Field name Number of portions, Condition >, Value 0 and under Options check Copy results to and enter Sheet2.A1 or wherever you want the output to start. Hit OK.

[thefa11guy]

Awesome that works. Would it possible to macro that or is that not possible?

[erAck]

It’s possible but I’m too lazy Maybe someone else will come up with a solution.

[igorlius]

@thefa11guy Is this sufficient for you, or do you need the “One Key” Press/Button solution?

[thefa11guy]

I’d still like to automate behind a button if possible but this does work well

[igorlius]

Hi,

i can contribute the macro, and i can tell you how to attach the macro to a custom shortcut (key)
Sorry, can’t help you with adding an actual button, but maybe sombody else can contribute that.

First add the macro, under Tools → Macros → Edit Macros (Just copy it into the text area there)
You might want (have to) edit some of the parameters (origin and destination sheet name and start and end offsets)

sub copyfood
	
	rem define origin 
	oSheet="Sheet1"
	foodcol="A"  
	portioncol="E"
	ostartrow=1
	oendrow=10
	
	rem define destionation 
	dsheet="Sheet2" 
	dcol="A"
	dstartrow=1
	
	rem get sheets
	osheet = ThisComponent.getSheets().getByName(osheet)
	dsheet = ThisComponent.getSheets().getByName(dsheet)
	
	rem remove old values 
	for i=ostartrow to oendrow
		dsheet.getCellRangeByName(dcol & i).String = ""
	next 
	
	rem add new values
	count=0
	for i=ostartrow to oendrow
		if ( oSheet.getCellRangeByName(portioncol & i).Value > 0 ) then
			dsheet.getCellRangeByName(dcol & (dstartrow+count) ).String = osheet.getCellRangeByName(foodcol & i).String
			count=count+1
		endif
	next
end sub

Now you can add the macro shortcut (this is a bit lenghty, but you’ll only have to do it once)

1. Goto Tools → Customize ,
2. Select the Keyboard Tab
3. Select or Add the Key you like to use to invoke the macro
4. In the “Category” Area Scroll and find the “LibreOffice Macros” Entry
5. Expand the Entry from “LibreOffice Macros” to “My Macros” to “Standard” and “Module1”
6. In The “Function” Area (right beside) the “copyfood” macro can now be selected
7. No press the “Modify” button
8. Finally press “OK”

Image:

Now you can just press the shortcut key and the macro will be executed.

Hope that helps.

[thefa11guy]

Thank for this. Worked a charm. just had to tweak the oendrow section to run the correct sections and the output changed to start at row3 in Column B. I managed to create the button and assign the Macro. Funnily enough, I could create the button and assign the macro no problem. It’s the writing of the Macro’s I struggle with. Much too close to coding for me.

Thanks for this.