Can I populate a column with conditional data while omitting empty rows or possibly autosort?

Question

In one spreadsheet document, file.xlsx, I have 2 sheets. Sheet1 has the editable fields. Sheet 2's sole purpose is to read the data from Sheet1. The point is to keep track of inventory and easily display items needed to be ordered.

Sheet1 'column a' is item number for ordering
Sheet1 'column b' is the number I have on hand
Sheet1 'column c' is the formula cell to find the number needed to be ordered

It looks something like this:

Document1:sheet1
    A   |   B   |    C   |
1 |txt1 |   1   | =10-b1 |
2 |txt2 |   0   | =10-b2 |
3 |txt3 |   13  | =10-b3 |
4 |txt4 |   5   | =10-b3 |
5 |txt5 |   2   | =10-b4 |

There is some if statement conditional formatting in the "C" column to check if the "B" column has a value HIGHER than 10 and if it hits true then the corresponding C cell is blank but for space, i'm summarizing here

Document1:sheet2
                 A               |              B               |
1 |=if(sheet1!C1>0,Sheet1!A1,"") |=if(sheet1!C1>0,Sheet1!C1,"") |
2 |=if(sheet1!C2>0,Sheet1!A2,"") |=if(sheet1!C1>0,Sheet1!C2,"") |
3 |=if(sheet1!C3>0,Sheet1!A3,"") |=if(sheet1!C1>0,Sheet1!C3,"") |
4 |=if(sheet1!C4>0,Sheet1!A4,"") |=if(sheet1!C1>0,Sheet1!C4,"") |
5 |=if(sheet1!C5>0,Sheet1!A5,"") |=if(sheet1!C1>0,Sheet1!C5,"") |

If an item isn't to be ordered because stock is high, it doesn't show up in the list. This is ok. It looks like this:

Document1:sheet2
      A    |  B  |
1 |  txt1  |  9  |
2 |  txt2  |  10 |
3 |        |     |
4 |  txt4  |  9  |
5 |        |     |

What I would like to see is: Document1:sheet2

      A    |  B  |
1 |  txt1  |  9  |
2 |  txt2  |  10 |
3 |  txt4  |  9  |
4 |        |     |
5 |        |     |

For 5 Items in the example, it doesn't seem that annoying, but in actuality I have 1200 potential rows and it varies between 200 to 800 actually populated each time i conduct inventory. The whole point is to have one continuous section of rows that I can select and print. Maybe some way of autosorting and placing the empty ones on the bottom?

I've taught myself excel. I don't know the advanced tricks and things.
I'm a typically a programmer but I've been tasked with this project and the guys upstairs want me to do it in excel.

hmm, i guess i don't know about filtering. I'll look and see if that works — user214877
This sounds like a simple task to do using a VBA macro and "calculate inventory" button. Do you want to / are you allowed to use macros? — laylarenee
I could probably use VBA macros. I really don't know anything about using macros in excel. I'll check around for some macro info and include them here if I figure out how to make it work. If you have any suggestions, i would much appreciate it. Thanks — user214877

user214877 user214877 · Accepted Answer · 2013-12-27T02:42:23

i've overcomplicated this, to the max.

a simple sort putting the empty's at the bottom is the resolution.
not quite as automated as i'd like
but
it works.

Can I populate a column with conditional data while omitting empty rows or possibly autosort?

1 Answers