Excel 2007 : formula to copy only rows of data based on leading cells value

[flyingwithtwins]

In an attempt to make this more automated for the user, I have run into a road block.

The goal is to populate the cells in columns AA, AB, AC with data from D, E and F. However, I only want this function IF B has a value greater than 0.

If that value in B is =0, then I do not want anything copied.

I have tried to write this with the "IF" function, but can't fill in AA, AB, AC with out blank rows in the place of the skipped over (or unwanted) data.

The Values for D, E, and F are linked from another workbook and currently the user has to copy and paste the values themselves. This is error prone when doing 20 or 30 entries in a day.

I have attached a worksheet that contains what I WANT the RESULT to LOOK like.

[jamescox]

There is bound to be a better way, but in a macro, you could set a range reference to Cell D3 and then use .Offset(N,-2) in a loop on N to check if the value is "" - when it's not, you have found an entry in Col B. Then you could use the value of N and get the values from Col D, E and F (.Offset(N,0), (.Offset(N,1) and (.Offset(N,2) respectively) and put them into the first blank rows of Cols AA, AB and AC.

However, that's a lot of work. Maybe you could just use whatever method is working (except for the blank rows) and then do a Data Sort on the Col AA, AB and AC region that would consolidate the blank rows at the bottom of the sorted range...

Again, not great ideas, but maybe they will jar loose something better...

James

[JBeaucaire]

There was a LOT of hidden garbage in your sheet that had to be cleared out before the sheet was evaluating empty cells properly. I hope none of that was important since I cleared all empty cells so they are truly empty.

This macro has been installed onto a Forms Button.

Please Login or Register  to view this content.

[flyingwithtwins]

There was a LOT of hidden garbage in your sheet that had to be cleared out before the sheet was evaluating empty cells properly. I hope none of that was important since I cleared all empty cells so they are truly empty.

JBeaucaire, Thanks for the forms button. I have recently started using Excel again (previously Excel 97) and am trying to understand VBA code. Please pardon my inexperience, but what did you need to clear in order for the cells to be truely clear? I am not the original author of this sheet and am not sure what else he had in here that may have been vital as this sheet is linked to other workbooks, especially columns B - F. I did hide a number of columns (G - Y) just to make the sheet easier to read.

I do notice though that every time I hit the forms button, the same data populates columns AA, AB, and AC. As the data in B-F is updated through the linked workbook, how then would I just find and copy the newest row(s) of information?

This is a great start to what I am looking to do, I just need to make it as "idiot proof" as possible for users who are not very computer savvy.

Thanks, FWT

[DonkeyOte]

Just as a heads up you could use Formulae for this:

AA10: =IF(ISNA(MATCH($Z10,$B:$B,0)),"",INDEX(D:D,MATCH($Z10,$B:$B,0)))
copy above across entire results table (ie AA10:AC71)

As you can see if implementing the above you will be calling the ISNA(MATCH test 3 times per row (Date, Customer HC, Shipping), it would of course make more sense to store the result of the MATCH in another column and reference that column in the other three cells so as to reduce number of calcs being performed... avoiding repetitive calcs is a good habit to get into.

As I say, just an alternative.

[JBeaucaire]

Sometimes cells that look empty have a null value in them instead, equivalent of typing ="" into a cell. Your whole sheet was like that, making it VERY difficult to spot the "last row" for each section since null values flag a cell as "not empty".

Wherever that data comes from, see if it is capable of spitting out data with actual blank cells.

We could tweak this a little more if you don't want to use the worksheet formula approach. Can you verify that the number you are putting in column B represents the slot in Z this item should be placed in? So if you add a new entry to the sample data it will be 3, then 4...etc?

[JBeaucaire]

Here's the tweaked version of the code and sheet. This will watch for you entering spurious Load # entries and will warn when that has happened.

Please Login or Register  to view this content.

[flyingwithtwins]

Thank you for your help, I was able to create two options to present to the other users. In the end they decided to go with the fomula setup. JB, thank you for your help, I may still use the macro button elsewhere.

Side Note: I tried to mark this thread as solved, but the EDIT button is not available on my original post.