Copying data from one worksheet to another based on a conditional value?

**enmar** · 07-17-2012, 07:34 PM

Hi there. On a scale of 1-10, my familiarity with excel/VBA programming is a 1, but I need to figure out how to do this, and I'm hoping you can help me out. Here's the scenario:

I have an excel workbook, and in it is a worksheet entitled Internal, which is password protected and contains information that should be visible to everyone with the password when it is shared online. Some of this information -- the "External" information -- needs to be shared with another party and displayed on the worksheet entitled External, which has no password. There is a column on the Internal worksheet titled "External? (Y/N)" which indicates whether the row of information should be copied to the External worksheet or not.

Basically, I need to scan each row of a table in a specific column, looking for the string "Y" or "N". If the string is "Y", the information in the row needs to be copied to a table in the second worksheet. If the string is "N", the information will not be copied and should remain in the first worksheet. The only thing I've managed to do is use an IF function, but this is very tedious and requires me to manually enter the function into each cell AFAIK.

I've attached a table with an example of what I need to happen. The worksheet Internal contains the table with all information, and the worksheet External contains only the rows from the Internal table that have a "Y" in column F.

Thanks

**xladept** · 07-17-2012, 07:51 PM

Hi enmar,

Here's a simple little routine:

Please Login or Register  to view this content.

**Pete_UK** · 07-17-2012, 08:09 PM

If you want to stick with formulae rather than dabble with VBA, then the attached workbook shows how you can do it with 3 basic formulae.

I've put this formula in G3 if the Internal sheet:

Formula:

Please Login or Register  to view this content.

and copied this down beyond your data (the hyphens indicate how far). This gives a simple sequence for the records that you want to extract.

In A3 of the External sheet I have put this formula:

Formula:

Please Login or Register  to view this content.

which, when copied down, tells you the rows where you want to get the data from. Cell B3 contain this formula:

Formula:

Please Login or Register  to view this content.

which brings the relevant data from column B. This is then copied across into C3:E3 to get the data from all the columns, and then the formulae in row 3 can be copied down as far as you think you may need.

This is all automatic, so if you were to mark record 3, for example, with a Y then that would appear in the External sheet along with the other records - just make sure that the formulae are copied down far enough to accommodate more records.

Hope this helps.

Pete

**enmar** · 07-17-2012, 08:09 PM

Thanks a lot, xladept. I've made the necessary modifications for my particular table, and it's working perfectly.

Pete, thanks for the solution and explanations. From general knowledge of C, I can see how xladept's VBA code functions, but my knowledge of functions in Excel is very limited. Thank you for explaining what exactly the formulae you used do, twas very helpful, and it is working as intended. Definitely a plus that it will automate it even as I continue to add rows so long as I drag the formulae down.