Copy rows into a new sheet as per set conditions

[walchase]

Hi,

I've been trying to streamline my pipeline at work, and I keep coming to suggestions about using macros. I don't even know where to begin when it comes to that....

I have a spreadsheet that is organized by the stage (or process step) that files are in, and I want to have information sent to different sheets once they reach specific steps (ie stage 4, stage 7 and stage 20....). I was hoping there was an easy way to do this with a formula, and I'm just not sure how to use macros or set that up. Any help/suggestions?

thanks,
wal

[JBeaucaire]

I can accomplish this common task using both formulas (with the addition of helper columns) or with macros.

Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook. Make sure the workbook demonstrates your desired results if possible, or just highlight the cells you're trying to fix. Use BEFORE/AFTER sheets if that helps make it clearer.

[walchase]

I'd be glad to send you an document (i hope an quick example doc works), and hopefully i'll be able to paste the real information in afterwards....

Also, How do i attach the file?

[walchase]

example walchase.xlsx

there are instructions inside the document as well...hope this works!

[JBeaucaire]

The key column I creates a unique numbered index of each row.

On the other sheets in cell J1 is a formula that causes the sheet name to appear. That is used to pull in the rows that match.

The formula in A2 is copied down and across the first 20 rows of each sheet, the formula is the same, but notice the rows that appear are different for each sheet?

[walchase]

revised walchase.xlsxthanks for the help! I may have done a poor job explaining, and I'll send over another example doc if thats ok.

I'd like to have all entries input into the first sheet and then as the stage changes (changes made on any sheet) the row would move from sheet to sheet depending on the value of the "stage" column.

Thanks again!

[JBeaucaire]

The file in post #5 is causing rows to appear on sheets based on the "column I" key formula and the names of the other sheets. Of course, if you change the value in the "Desired Sheet" column then that row would move automatically. You have tried it haven't you?

[walchase]

we can definately work with this I think. I'll play around with it and let you know if I cut and past the "desired sheet location" column, and make it my first column...if so we've found a winner

how will the Key in column 'I' help us?

thanks for your help...youre awesome!

[JBeaucaire]

how will the Key in column 'I' help us?

Have you looked at the formula in "column I" and the results it creates? See how it numbers the first sheet1 event as 1.1, and the second as 1.2, then 1.3....etc?

Now think of that key "1.1" as "sheet.datarow"

The formulas on the other sheets are all referring to that column on the Entry Sheet and displaying the data one row at a time until it runs out of data. There are formulas on the other sheets through row20, you can copy them down further if needed.

[walchase]

gotcha...makes sense

[walchase]

I've tried to drag the cell format/formulas throughout the sheets for sheets 1,2,3,4....and although the information works for the first 9 rows on the "entry sheet to be sent to other sheets, everything after row 10 on the "entry sheet" comes up as "#N/A". any solutions/ideas?

[walchase]

**it comes up as "#n/a" on the alternate sheets (1,2,3,4)

[JBeaucaire]

You'll have to show me what you've done. Post the workbook.