Move data from SHEET1 to SHEET2 based on Selected criteria...like an adv filter

Hello Forum:

Please I need help with a formula based solution I have been working with for a month now. I use it as a program management tool for my department at work. I basically want to move data from SHEET1 to SHEET2 after a trigger. The data is moved based on an individuals name to their respective tabs. Then this person has to enter in hard coded info into some cells to show their progress. The problem has came in when I add new part numbers after that trigger has come up...it inserts the new part number into order numerically and over writes or dis-associates so to speak the team members hard coded data. Please take a look at my example and see if anyone has any suggestions...

HELP ME.xlsx

This post looks like a duplicate of this one:

http://www.excelforum.com/excel-prog...dv-filter.html

Pete

Yes they are the same issue...one is posted in formula help and the other in macro help...I am not sure which type solution is best. The formula solution I am using is not working for my requirements.

Well if you read the Forum Rules you will see what they say about duplicate posts. Most contributors scan through all the forums, so there is no need for duplicate posting, and it is frowned upon as it often wastes contributors' time. I see you have had no response to that other post, so I'll give you some tips on the formula approach.

The formula you have in column Q will just give you a sequential conditional count of those records where column O is set to "Y". As such, it will not be able to distinguish between records being changed to a Y after some other event (like data being added to the other sheet). Consequently, you will need to make use of another column where you could record the date that column O was set to Y, and then modify the formula in column Q to take account of this. Obviously, this would have knock-on effects to the other formulae in the other sheet.

Hope this helps.

Pete

I already keep that date anyway so I would just need to address it in the formula. What formula would I use to include the date as an index.

Suppose you kept column Q as the helper column and used column R for the date, which is input whenever column O is changed to a Y. Then I would suggest this formula in Q2:

=IF(O2<>"Y","-",M2&"_"&R2&"_"&COUNTIF(R$2:R2,R2))

which can then be copied down as far as you like - the hyphens indicate how far you have copied it. The helper value is now made up of column M plus the date (as a serial number) plus the sequential identifier, which is now dependent on the date. There might be some problems with this if you have different values in column M which might have the same dates in column R, in which case you could have another helper column (S) with a formula that conditionally joins M2 and R2 together, and then the COUNTIF expression would involve S2 rather than R2.

As I said before, this has knock-on effects on how you retrieve data in the other sheet - you will probably need to have a list of the unique dates from column R, so that you can then search through sequences for each such date for each value of M (messy !!)

Hope this helps.

Pete

Is there not an easier way to just force the value to be placed into the next available empty cell say at the bottom whenever a new one is added?

Not with the formula that you had.

Suppose you had marked rows 2 and 7 with a Y, then the formula would have identified those as x_1 and x_2, i.e. they would appear on the first and second rows of the other sheet (to which you might then add the extra data). If you now mark row 5 with a Y, your previous formula would identify the rows 2, 5 and 7 as x_1, x_2 and x_3 respectively, so the row 7 data would now be moved to row 3 in the other sheet, but the fixed data would remain where it was in row 2 - although you already know this as that is what you described.

The problem with a formula solution is that it is DYNAMIC, i.e. it will adjust automatically to suit changes in the data, so I've just been pointing out to you some of the things you will have to do in order to avoid the problems that you have already encountered.

Perhaps another approach would be to enter the extra data into the first sheet once column O has been set to Y, and then it is relatively easy to copy that data, along with the other data, into the subsidiary sheet - then it won't matter if data was once showing in the second row for row_7 record, and then subsequently gets put into the third row (i.e. dynamically).

Hope this helps.

Pete

I understand a formula based solution maybe is not the best solution but my VBA macro skills are pretty much non existent. Hopefully, I can find some help on the forum that will solve the issue completely. (and easily)

Thanks for your help.

Okay, well maybe you can mark this one as Solved, and then bump the other one.

Pete