Create a Unique List from other workBook

[dlow]

Greetingz...

I have a Workbook currently with 8 Sheets. I want to use the last Column of each of these Worksheets as a data source for a list that I want to create. All of the cells in the last Column have formulas, but the result is blank. In fact only a few out of thousands will have a value. The list would be in another Workbook the range where I want to start my list is A13 or whatever is the next empty cell.

Now to my question. Is there a way to make this a dynamic list that will be updated from the list & also the sheets.

Meaning that if I and a value to on of the sheets it will update mylist, & if I then delete it from the list it would delete from the range in the worksheet as well.

If thats not enough is there a way to make sure that the list doesn't have duplicate values.

So any help would be very much appreciated.

Thanks,

d

[FDibbins]

You have asked 2 completely different questions in the same breath...create a dynamic list...and prevent duplicates.

I suggest that you attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[dlow]

Ok thanks. I made some samples & uploaded them. Thanks again Ford for your help.

Please Login or Register  to view this content.

I dont know that they need that much detail. Just if there is a value in CS* put in the list range.
If dups is a different question I can always try to work it out, & if I cant I guess I could make a new thread for that.

[FDibbins]

Perhaps you missed this part of my request?

Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

[dlow]

Ok, I updated the workbooks with more info, but as far as before & after there isn't much to it there is either nothing below the list & that's where we want to update or there are some values & we want to add to them.

Sorry Buddy, I know your just trying to help me out. You did see that there are values in Column CS in the Data workbook.

Please Login or Register  to view this content.

[dlow]

Come on anybody anything. How about some links so that I can sort this out myself.

[dlow]

I found this thread which seems similar to what I am trying to do.

[dlow]

Ok I found this code which will work if I manually change the values & just run it once. If I try to loop through my sheets it doesn't work right for me.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[nilem]

Hi dlow,
try it

Please Login or Register  to view this content.

[dlow]

Wooow. Nilem U-Rock!....

Yup that the stuff right there.
One question. How can I get the Macro to paste back to the "List" workBook. The code runs from List.xlsm & gets the data from Data.
When I run the code I get a type mismatch.
- BTW - thanks a lot, for your hard-work.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Me Crying ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
I have been trying to get this done for days now.
I don't ask questions at all unless I am absolutely stuck. Unfortunately for me it seems I am getting worse @ VBA not better. lol...
Maybe I should make some time to go over the basics again.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Done ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Okay so if its not to much Nilem do you know how I could do this from the List.xlsm to the Data.xlsm & paste back to the List.xlsm

[nilem]

Ok, try so (it is assumed that files "List.xlsm" and "Data.xlsx" are in the same folder)

Please Login or Register  to view this content.

[dlow]

Wow, Perfect. OK so I know that Arrays are cool. & I think that is what your using. How could I learn them like you. I have lots of tutorials & still every-time we get to arrays my head starts spinning.

Hey Thanks a Million.

All the best,

dirty

[dlow]

As far as I am concerned nilems code does, so much of what I wanted done I consider this Solved.
The only other thing that I had in mind was away to link back the values on mylist. In a way that if the value was deleted on the List.xlsm it would clear it from the Data.xlsm. From what I dug-up yesterday looks like that could be done in a formula. So, I am going to open a new thread to get that done. I wont be ready to do that for awhile. I am just so happy to get this part running. Yeehaa!...... [Could Mod change the title to "Create a Unique List" from other workBook or something like that. You guys know better than me, but that seems like what we are doing here. Doesnt seem as though we have gotten to the dynamic part yet.] IMHO

[arlu1201]

Actually you can edit it yourself as well - just go to edit post on your 1st post - then go advanced and edit the title.

This time i have done it for you.