Find first available cell > 0 in a row and using next cell >0 thereafter for a calculation

**jaygee123** · 09-29-2016, 12:40 PM

Hello,

I am trying to figure out what function/ VBA I need to solve my problem.
I am linking a cell from one worksheet that contains a list of products with multiple chemicals. I am however only interested in one chemical, so sometimes for that row data will be zero. I want to find a function that will find the first cell >0 (and there after) to be used in a calculation that will populate in a different worksheet.

This is an example of data I have to work. I included row numbers for reference. (This will vary when I distribute to users and they will be able to put in their respective data based on products they use) In my spreadsheet this column is in AC and I want to use this data into another worksheet in column O, row 2. Currently I have this formula in O2 =IFERROR(APPLICATION_DATA!$AC6*N2," "), where value from column AC is multiplied by N2 in other worksheet. currently it is showing up as zero because AC6 is also zero. As you can see I am only interested in Row 12 and Row 15, but I need a function to automate this when other data is inserted and then populate in my other worksheet starting in row 2. *FYI column AC range begins in row 6 and ends in row 2000. Help please!

1,3-d Column(AC)
Row 6- 0
Row 7- 0
Row 8- 0
Row 9- 0
Row 10- 0
Row 11- 0
Row 12- 1542.3408
Row 13- 0
Row 14- 0
Row 15- 953.44704

**Glenn Kennedy** · 09-29-2016, 12:53 PM

If this array formula is not what you need, please attach a sample sheet.

=IFERROR($N$2*INDEX(Sheet1!AC:AC,SMALL(IF(Sheet1!$AC$2:$AC$14>0,ROW(Sheet1!$AC$2:$AC$14)),ROWS(Sheet2!$A$2:A2))),"")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

if you do need to attach a sheet...

1. Make sure that your sample data are REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

4. Try to avoid using merged cells. They cause lots of problems!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

**jaygee123** · 09-30-2016, 01:12 PM

Hi, thanks for trying to help me out. I tried your code and it wasn't working. But I have attached a sample spreadsheet of the data.

**jaygee123** · 09-30-2016, 01:16 PM

Also, on the application factors worksheet, the conditional formatting in the O column can be cleared if you select a different drop down for fumigation method. I forgot to clear that.

**Glenn Kennedy** · 10-01-2016, 02:54 AM

This array formula does what you asked for, but I'm still not sure if it's what you want. Now that I can see your sheet, please explain what you want to see, where you want to see it and where the data should come from. Especially ensure that point 2, Post 2 is addressed.

=IFERROR(N2*INDEX(APPLICATION_DATA!AC:AC,SMALL(IF(APPLICATION_DATA!$AC$6:$AC$19>0,ROW(APPLICATION_DATA!$AC$6:$AC$19)),ROWS(Application_Factors!$O$2:O2))),"Invalid Fumigation Method")

Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly braces yourself - it won't work...

**jaygee123** · 10-03-2016, 03:06 PM

Thank you, that worked!

I also want to be able to reference a row number from the main application data worksheet of all the data>0 in a new column in the application factors worksheet, would I use an if statement using part of this formula?

(INDEX(APPLICATION_DATA!AC:AC,SMALL(IF(APPLICATION_DATA!$AC$6:$AC$2000>0,ROW(APPLICATION_DATA!$AC$6:$AC$2000)),ROWS(Application_Factors!$O$2:O2))),"")

*I removed the iserror function and increased the AC column range and I no longer need to multiply it by N2.

In other words, if data>0, what is the row number associated with it from the application data sheet?

Find first available cell > 0 in a row and using next cell >0 thereafter for a calculation

LinkBack

Thread Tools

Rate This Thread

Display

Find first available cell > 0 in a row and using next cell >0 thereafter for a calculation

Re: Find first available cell > 0 in a row and using next cell >0 thereafter for a calcula

Re: Find first available cell > 0 in a row and using next cell >0 thereafter for a calcula

Re: Find first available cell > 0 in a row and using next cell >0 thereafter for a calcula

Re: Find first available cell > 0 in a row and using next cell >0 thereafter for a calcula

Re: Find first available cell > 0 in a row and using next cell >0 thereafter for a calcula

Thread Information

Users Browsing this Thread

Similar Threads

Convert Find Parent Cell Macro to Find Dependent Cell

How to clear an input cell once calculation has been made in another cell.

Find blank cell, insert formula to find median of above cell range

Clear cell content based on another cell calculation

Add an amount from a calculation to a cell with a calculation (Sample attached)

Excel 2007 : Check cell value and change another cell based on precentage calculation

[SOLVED] Row Manipulation / duplicate dependant on cell value... and perform calculation on another cell...

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions