Duplicate a column to another worksheet if a condition is met?

[alsmith]

Hi folks.

I have a sheet called "List" and a sheet called "Negotiations."

"List" is a big master matrix of data regarding different items, listed in List Column A. There are four major properties an item can have, specified in Column B. One of which is "Available."

So we've got:

Please Login or Register  to view this content.

What I'm looking to do is reference that "List" worksheet in the "Negotiations" worksheet, when the property is "Available." It's got to be a dynamic reference - if "List" changes, "Negotiations" changes.

So in "Negotiations" I'd have:

Please Login or Register  to view this content.

Many thanks in advance for your help.

A.

[zbor]

Add filter in first row (Data-> filter) and filter out Available

[arlu1201]

One way that you can do it, is once the changes have been made to the "list" tab, you need to run a macro that will do a vlookup in the "Negotiations" tab that will reference the "list" tab based on a common field between both the tabs.

[alsmith]

Add filter in first row (Data-> filter) and filter out Available

Hey Zbor, thanks for your help. I've already got a filter on the "List" sheet to display the "Available"s. However the "Negotiations" sheet is going to be copied periodically into an electronic progress report. As such I only want it to have the relevant data present, not extraneous data hidden behind filters.

The macro sounds like an OK solution, arlu, thanks for the suggestion! I'd have to educate myself on macros first but that shouldn't be a bother.

However what I'm really looking for is a formula. Even if it's to just retrieve the numbers into "Negotiations" Column A from the "List" Column A, when the "Available" condition is true.

Thanks again!

[arlu1201]

Ok, if the macro will take time, you can do a vlookup in the Negotiations tab to reference the data in the List tab. Ensure that you do not paste special these formulae or else your formula will not take the latest change.

[alsmith]

Righto. I've had a play with the VLOOKUP function but am at a loss about it. So my lookup_value would be "Available," my table_array would be B1:B##, I'm not sure what my col_index_num would be, and my range_lookup would be A1:A##?

[arlu1201]

Your lookup value would be "Available", your table_array would be B1:B## or just column B as a whole, col_index_num should be the number of the column which has the answer, in this case it will be 1 since there is only 1 column (however, if you had your table_array as A:B and your answer was in column B, then your col_index_num would be 2) and the range_lookup should be 0.

[alsmith]

Hmmm. That returns a lot of "Availables," but not the Ref numbers - 3, 5 & 6 in my above example.

Still appreciating your help though.

[canapone]

Hi,
in the file attached I'm using a array formula to be confirmed with control+shift+enter in order to extract "Available" rows.

=IF(ROWS(A$2:A2)>COUNTIF(List!B$2:B$100,"Available"),"",INDEX(List!A$2:A$100,SMALL(IF(List!B$2:B$100="Available",ROW(A$2:A$100)-1),ROW(A1))))

The formula has to be copied down until empty cells appear.

Then I've used a VLOOKUP to read what I 've obtained.



Hope it helps

Regards


Edit: a small -only formal- correction: no need to have "List!" inside the counters (ROWS and ROW): maybe the formula is neater.

[alsmith]

Good grief! What a formula. That is absolutely incredible. Thank you so much.

[arlu1201]

If your query is resolved, please mark the Thread as Solved. Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.

If you found any post helpful, please rate it accordingly.