If value is Y, return values in range and copy to another sheet.

I have a database of information, which I would like to use to populate the values in other sheets if it matches a given criteria.

My issue is, values in column G can change from No to Yes.

If value in column G = Yes, I then need it to copy the associated values from columns B:C into A:B columns in the EQA sheet. The EQA sheet also contains additional details that are not found in the Database.

Values in column G may change, therefore changes need to update automatically in the EQA page.

I tried =FILTER but this didn't move/allow for the additional information in columns C:J within the EQA sheet.

Can anyone suggest a solution?

I am currently having to manually enter details from one sheet to another which is really time consuming, especially as I need to replicate this scenario within other sheets for other criteria.

Welcome to the forum.

Please update your forum profile with the version of Excel that this neesd to work with - is that 365?

If so, then this will do what youy want:

=FILTER(Database!B2:C10,Database!G2:G10="Yes")

However, the remaining columns in the EQA table will NOT be dynamic if you fill them manually.

You need to explain how this is meant to work in context.

This is what we were both saying.

The solution is to enter ALL data on the source sheet, then filter it.

Duplicate threads are NOT allowed. You must continue here, but if you want, I can move this thread to the VBA section for you. Do you want this?

It would have to be VBA for the reasons we've stated. Moving the thread now.

Try this;

Please Login or Register  to view this content.

Run the macro 'InitialLoad' to load up sheet EQA with those that are already "Yes"
The other routine will handle those that change from No to Yes

1. Start with a Blank EQA Sheet. (Columns A and B Blank at least.)

2. Copy the code I gave you into the Database sheet. It is important you get the right sheet. (Right click on the sheet tab>View Code>Paste it in there).

3. There are TWO macros in that code. One you will see from your macros tool, the other will execute when you change form NO to YES.

4. Run the InitialLoad macro ONCE. (That is why I called it INITIAL Load. If all the ones with Yes by their name are ALREADY on the EQA sheet, you don't need to run this at all).

5. When you change from NO to YES the OTHER macro will run automatically and add the NEW name.

6. Don't run the InitialLoad macro EVER AGAIN. (Delete it if you like.).

PS. You didn't include the code with your attached example, so I can't see what sheet you put it in. It has to go in the Database sheet, because that is the sheet you will be making changes on that require certain action to take place.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank those who have helped you by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.

1. When you "edited the code", what changes did you make?
2. Did you put it in the correct sheet (ie. the Database sheet), where the change is being made?
3. In your "real spreadsheet", is the No to Yes change still being made in column G?
4. The code works with "Yes" (exactly that). Are you entering "YES"?

If you want it to work with Yes,YES,yes,yEs or any other combination, a slight change will need to be made to the code. Also make sure there are no trailing spaces.

This minor mod will take care of just about all of those issues with your input (if that is what you want). Change that one line to this;

Please Login or Register  to view this content.

They're the only things I can think of as to why it would not work. You may need to put an extract of the real sheet up, so that I can look at it for you.

OK There are a few issues here.

1. The YES/NO you have mentioned before is in column K which is dependent on column M which is in turn dependent on columns N O and P (which are not determined by a formula). The only column out of those which is NOT hidden is column N.

The Worksheet Change event is triggered by a manual change to the worksheet so you have to decide what (manual) change triggers your 'event'. As the others are hidden, I am guessing the change the user makes is in Column N?
However the worksheet change is checking for a change in column K, so it does nothing. You need to decide which column the user is going to change and check for that column or columns, instead of column K

2. Your dropdown in column N is uppercase but the code checks only for a mix of upper and lower, so you need to make the change I suggested in post #17. Note that we are now talking column N NOT K. If you really need to check the value in column K, we MAY need to change your value check from Target to Range("K" & Target.Row) or maybe something else depending on how you are going to use this sheet. (See point 4).


3. You are putting the surname in twice, so you need to change that too. (A simple typo when you modified the code I gave you)

4. This all depends on actually how you are going to use this workbook. If you are going to unhide columns and change them, you may end up with the situation where a name is added twice (or not added at all) because row K is already Yes and is still Yes (or we are not checking for a change to hidden columns).
IF that is the case you will need more checking to determine if the change you made actually CHANGED column K, but I will not complicate things if it's not needed. That will be up to you to determine how you use this.

Originally Posted by SF_2011_uk

Hi

However, the NO to Yes change didn't then copy the data to the 'EQUAL' sheet.

I explained why this happened, it is because you didn't MANUALLY change column K, which is what that macro checks for

Originally Posted by SF_2011_uk

You mentioned in the previous post that I am putting the surname in twice? Which section do you mean?

llrow = Worksheets("EQUAL").Range("H" & Rows.Count).End(xlUp).Row + 1
Worksheets("EQUAL").Range("H" & llrow) = Range("I" & Target.Row)
Worksheets("EQUAL").Range("I" & llrow) = Range("I" & Target.Row)

Yes, that section. Look at it. You are are setting both cells in the EQUAL sheet to the same thing, Range("I" & Target.Row), which is the surname.

Originally Posted by SF_2011_uk

I think I need to do a lot of reading up on VBA codes. Completely new to this side if Excel.

Yes. Good idea. I also think it's a good idea to keep your workbook as simple as you can to make it easier to change. (See my post below)

OK I've changed my mind on how to do this. There are too many sheets, too many ways (with formula and within different sheets) which can update the column you are looking at and too many columns with critical values with columns of (almost) zero width (why?) etc. etc.

The real-world example is so different to your sample workbook, I have given up on trying to get this update done effectively in real time.

This macro should do what you want. Run it every time you want to add names to the EQUAL sheet.

It goes in the OH Database sheet.

Please Login or Register  to view this content.