Insert a row based on a calculation

Microsoft Excel 2003 (full product)
August 6, 2010 at 03:33:04
Specs: Windows XP
Hi everyone.

I hope someone can help me with my problem. I have a spreadsheet with columns for product inventory (Columns go from A to R) - Date Received, ID, Current Stock, costs etc. and the rows are the specific products (approx 125 rows to date). I am trying to create a macro that will insert a new row based on the difference between the inventory in and out values along with the commission and markup formulas in columns K to P.

For example:
Current Stock (Col F) is the number of items for that product at hand, say 10,000.
Shipped stock (Col J) is the number of items sent out.
If I enter 2,000 into Shipped Stock (Col J Row 10) a new row will be inserted (Row 11) with Col J in the Row 11 blank and the formula from col K to Col P will also be applied to the new row. If I then enter another shipment out value in Col J Row 11, say, 3,000 it will insert a new row again (row 12). New rows can be continually added until the Value in Col F minus value in Col J equal zero. However, if all the stock shipped is equal to the current stock (col J = Col F) then no new row appears.

I don’t know if this can be done or not but any help would be greatly appreciated


See More: Insert a row based on a calculation

Report •

#1
August 7, 2010 at 17:51:18
I'm a tad confused.

You say that in each new row "Col J in the (new row is) blank and the formula from col K to Col P will also be applied to the new row."

Then you say "New rows can be continually added until the Value in Col F minus value in Col J equal zero.

How did the value get into Column F of the new row? I assume it's the new quantity of Current Stock, but how does it get updated if the new row only contains "formula from col K to Col P"?


Report •

#2
August 20, 2010 at 03:00:38
DerbyDad03,

Thanks for the response and great point. A formula would be required to be inserted into ColF of the new row too.

Thanks for your help in pointing out that major flaw!


Report •
Related Solutions


Ask Question