Question About IF/THEN with Data Validation AND multiple cell data paste

[erynatpitt]

Hi,

This is most active forum I've found and the answers your members give seem to help lots of users. I'm hoping you can help me.

I have a workbook in Excel 2003 with multiple sheets.
On sheet 1, I have 20 columns of data, and have a Data List Validation in Column B, listing multiple options.
I want to create a formula that will look at Sheet 1, Column B, Cell 2 (for example) and IF it sees "Paid and Complete" in the cell (which I selected from the list), THEN I want it to copy and paste text for 4 other cells in that same row, into a row on Sheet 2.

So, IF Sheet 1, Cell B2= Paid and Complete, THEN I want Sheet 2, Row 2, Column B to copy/paste Name, Column C to copy/paste Title, Column D to copy/paste company, and Column E to copy/paste ad type from the Row in question on Sheet 1 into Row 2 on Sheet 2. I hope I explained this clearly. I essentially want to generate a list of paid advertisers on Sheet 2 pulled from a huge list in progress on Sheet 1.

Any help would be really appreciated. Thanks so much!

Eryn

[DGagnon]

Welcome to the Forum, im glad you enjoy being here, and im sure all of the experts appreciate your words.

As for your specific problem, could you attach a sample workbook with some data and desired results? im sure we can help you out.

[erynatpitt]

Wow! Thanks! This is fantastic. I've created a mock up sample of what I hope to do. I hope it's clear. Thanks so much!Sample_For_Forum.xls

[tigeravatar]

erynatpitt,

Attached is a modified version of your example workbook. Is something like that what you're looking for?

[erynatpitt]

Thanks Tigeravatar! That works exactly the way I wanted it to! Thank you so much for taking the time to figure this out!

[DGagnon]

this can be done 2 different ways, the 'simpler' way would be to use a VBA macro to generate the report, or we could use a series of Array formulas to try to pull it in. which method would you prefer?

(I vote VBA but it is your project)

[DGagnon]

The solution provided by Tigeravatar will work great on one line, but if you have multiple lines and only some are completed, there will be blank lines in the middle, this could be a very acceptable solution for you, just looking at all options.

[erynatpitt]

I wish I understood the difference between VBA macro and Array. I certainly hear what you're saying about something being more complex. I do have exactly as you've described. Multiple lines and only #11 is currently "complete." So the spreadsheet has 10 empty rows above row 11. So I suppose if there was something I could do to make them fill the spreadsheet in starting with the first row, that would be good. But certainly not required. It is more not having to copy/paste all the data myself, which tigeravatar's method certainly eliminates. If you feel like doing more with it to make it even better, I certainly wouldn't be opposed to this, but I don't want to misuse your time. Thanks for you help, DGagnon!

[DGagnon]

i put together a quick macro using the following code, see the attached sheet

note the button to the right to run the macro

Please Login or Register  to view this content.

[erynatpitt]

I wasn't able to get it to work. I see the text box, but there isn't anywhere to click. And I tried to install the macro toolbar button, but couldn't find anything to connect it to within the worksheet.