copy data from one worksheet to another using specific criteria

[r9thomas]

Hi,
I have a need to simplify entering data where I want it posted in two worksheets, but would like to only enter once.

When I write a check, I record it on a worksheet called "all checks". that entry contains basic check info in one row: date, amount, check #, vendor, and description.

The check also will get recorded in one of two additional worksheets. One is "construction", the other is "inventory".

I would like to only enter the data into "all checks". I figure I would add a column to my basic check info row labeled "Construction or Inventory" and would enter a "C" or "I".

What would be a formula to put into the Construction & Inventory worksheets that would return the complete row (full check description) based upon the "C" or "I" criteria.

Attached is a basic set up of what I am looking for.

thanks everyone!
Rick

[alansidman]

Here is a VBA solution for you. Place this in the Worksheet Events. Open the VBE Alt + F11. Double click on sheet 1. Paste the code below in the new window that opens. Save and close.
.

Please Login or Register  to view this content.

[r9thomas]

Thanks Alan,
I think I am missing a step. I was able to open VBA and copy your code. I then saved the worksheet (had to save as a "macro-enabled worksheet". so how do I make it execute the code?

[JeteMc]

Here is a formula based proposal:
1. Paste the following into cell A2 on the 'Inventory' sheet: =IFERROR(INDEX('All Checks'!A:A,AGGREGATE(15,6,ROW($A:$A)/('All Checks'!$A:$A="I"),ROWS($A$1:$A1))),"")
2. Drag the fill handle over to cell G2
3. While cells A2:G2 are still selected drag the fill handle down as far as needed
4. Repeat steps 1-3 on the 'Construction' sheet using: =IFERROR(INDEX('All Checks'!A:A,AGGREGATE(15,6,ROW($A:$A)/('All Checks'!$A:$A="c"),ROWS($A$1:$A1))),"")
Note that columns B and C on both sheets will need to be formatted to Date and Currency respectively.
Let us know if you have any questions.

[alansidman]

Whenever you make a change (keyed in) to a cell in column G, the code will automatically run.

[r9thomas]

Thanks Alan. that did it!

[r9thomas]

one more question / tweak:
can the code be amended to have it copy to start on a specific row on the inventory sheet? in other words, i would like the copied rows to start at, say A21, then run down from there, instead of at the top of the worksheet.

thanks

[alansidman]

You are referring to where the copied rows are Pasted?

[r9thomas]

yes. on the construction sheet and the inventory sheet I would prefer the copied rows start somewhere other than A2.
I played around with the code (note my forte for sure) and did not have success.

on those worksheets, columns A-D have data. would like on one for it to start at F1, the other worksheet maybe start below the data I already have, so A21.

[r9thomas]

I attached a revised sample workbook to illustrate my need.
Alan's code worked well in the sample I provided, but couldn't make the adjustments to the code to make the tweaks I needed.
The attached workbook has 3 worksheets. The goal is the same as I stated in my opening post. what I want the code to do is copy the entire row and place it in the correct worksheets table. So, if it is a a "C" (construction check) that that row's data gets copied to the table located in the "Construction" Worksheet.
The difference between this and the original sample is where the data is copied to on the worksheet. It was going to A2, but the revised worksheet has them going to tables located at different starting points.
thanks,
Rick

[r9thomas]

hi JeteMc,
the formula worked, but it made the computer run slow. it would take 4-6 seconds before it would tab to the next cell.
Not sure what is causing that or the fix, but it did work.

rick

[JeteMc]

This may speed things up.
Since the data is being placed into a table on the 'All Checks' sheet the formulas for the Inventory and Construction sheets may be written using structured references as in:
Inventory: =IFERROR(INDEX(Table1[Copy to],AGGREGATE(15,6,(ROW(Table1[Copy to])-ROW(Table1[#Headers]))/(Table1[[Copy to]:[Copy to]]="I"),ROWS($A$1:$A1))),"")
Construction: =IFERROR(INDEX(Table1[Copy to],AGGREGATE(15,6,(ROW(Table1[Copy to])-ROW(Table1[#Headers]))/(Table1[[Copy to]:[Copy to]]="C"),ROWS($A$1:$A1))),"")
Note that the tables on both the Inventory and Construction sheets have been converted to ranges.
Let us know if you have any questions.