Copying cells if condition is met

QuestionAsker2525 Posts 2 Registration date Wednesday May 24, 2017 Status Member Last seen May 26, 2017 - Updated on May 26, 2017 at 04:45 PM
 Blocked Profile - May 26, 2017 at 07:15 PM
Hi everybody,

I'm trying to make a spreadsheet with a "master sheet" with all the information for the people registered in our club. In this master sheet, I have one column (A) with the first names of the registered, another column (C) with the last names, and columns E, F, G, H, and I, which are labelled Weeks 1, 2, 3, 4, and 5, respectively. In each column E-I, I have input Y or N (yes or no) to indicate whether the person is registered in the club that week.

I have 5 separate sheets labelled Week 1, 2, 3, 4, and 5, and I have been trying to create a system in which I simply indicate whether a person is enrolled for a specific week on the master sheet using the text string Y or N, and it will autoupdate on the weekly sheets, and it will write the person's name (combining column A and C), age (column J), and phone number (column N) on that same sheet in separate columns.

This is what I've made so far for Weekly sheet 1:
Column 1: Name
=IF(Master!E5="Y", Master!A5&" "&Master!C5,"")

This code looks at the value for "week 1" for each row, and if that row has "Y", it takes the first name, adds a space, and adds the last name so we have the full name.
Column 2: Age
=IF(D5<>"", Master!J5,"")

This code checks if the name cell to the left (D5) is empty. If the cell is not empty, it finds the corresponding age value in the master sheet.
And I've done something similar for phone number.

I don't know macros that well, but I think I'll need to use a macro to make it autoupdate and remove spaces. My code above works, but it leaves spaces if a row doesn't meet the criteria, and isn't quite as pretty. Could someone please help me out? I'd be appreciative of any pointers.
I've found some code that is similar to what I'm doing <REMOVED BY MODERATOR>, but it doesn't explain what each line does and so I can't adjust it effectively.

1 response

Where does it leave spaces at? If you have a loop, that checks for a blank, and keep track of what row it is happening, the to highlight the row and delete it is easy (if that is what is happening)?



You code is probably a cut and paste from the other site, but you need to paste it here so we can look at what we are actually dealing with. I am not prepared to spend time to have you come back and say it doesn't work because you failed to mention that you use OLE or some stuff!

Post bask with some code, or at least let us know if we give you the code to delete a whole row, and you are keeping track of it all, would work!

Have FUN!

It's kind of fun to do the impossible! -Walter Elias Disney