3

Ok, bear with me here, I have a spreadsheet I'm creating that I want one sheet to only populate if there is information on the other sheet. So, sheet one would be "ORDER", sheet two "FILTER SIZES." What I am trying to do is this, if I put in a quantity on "FILTER SIZES" I want it to auto populate on the "ORDER" sheet. But, if I leave the quantity blank then nothing transfers over. And I know I can just do the IF = between the two sheets but I don't want the 0 quantity there. So, I'll try to put screen shots in of how I want it to look....new to this site so not sure how well that will work! This is before entering any information

And this is how I want it to look if I enter information in some of the cells With information entered

So basically, I want the information to populate over only if I enter a quantity and I don't want blank cells in between.

Help?

LADYSETSUKA
  • 31
  • 1
  • Take a look at this http://stackoverflow.com/questions/409434/automatically-execute-an-excel-macro-on-a-cell-change . Your best bet is to set your listening range to the Quantity column in the `FILTER SIZES` sheet, and move the row via vba then. – Tim Wilkinson Dec 01 '16 at 16:36
  • What is vba? Sorry, I'm good with excel on a more basic level I think? I can do spreadsheets but the more involved formulas like what I'm trying to do are new to me still. – LADYSETSUKA Dec 01 '16 at 16:39
  • I looked up vba......and, I think I'm more confused now than before haha. I know this must seem like such a simple way to do it, but it's gonna take me time to wrap my head around this. Is there a way to maybe help me out and show me how to do exactly what I'm trying to achieve? – LADYSETSUKA Dec 01 '16 at 16:55
  • It sounds like you are close. If you don't want your IF statement to give you a 0 when evaluated to FALSE, then just set the ELSE clause to "". As in IF(A1 = TRUE, "ok", "") – Jeff Dec 06 '16 at 18:40

1 Answers1

0

You can do this with Table and by combining the INDEX and MATCH formulas.
Let me show you the result first:

enter image description here

We enter the formulas into the FILTER table, here shown as a picture on the right, the orange one.

The formula in QTY column is: =IFERROR(INDEX(tblOrders[QTY];MATCH([@SIZE];tblOrders[SIZE];0));"")

The formula for TYPE columns is: =IFERROR(INDEX(tblOrders[TYPE];MATCH([@SIZE];tblOrders[SIZE];0));"")

The [@SIZE] part in both formulas is the SIZE column in orange table.

If you need explanation of the formulas, let me know.

kolcinx
  • 2,183
  • 1
  • 15
  • 38