Hello!

I find some ways on the internet for multiple look up values and multiple results but I can't understand them to apply on my table. Can you help me?
I have a file that has five sheets with same columns from A to W and different data. I need a way that will, according to statuses in columns A, return all the rest values of that row in a sixth sheet. I need only three of the statuses returned in sheet six.
There are four requirements:
1. If I change status in column A Sixth sheet is updated;
2. The returned values are in the same fill color as the original cells;
3. There are no gap rows in the returned values;
4. The returned values list is sorted by column S.


It's soooo complicated. Is it even possible?

This is similar but how can I change it to do what I need?
https://www.msofficeforums.com/excel...fic-value.html

P.S. I need only certain columns returned but the other ones I can hide if needed. Probably choosing specific columns will make it much more complicated than it already is.

Attached Files

Book01.xlsx (15.1 KB, 16 views)

Anyone has any idea?

Hi irisha
Getting excel to do what you want via a macro is easy enough but you haven't said what you actually want. Return specific columns, which ones? 3 of the statuses, which ones? Do all the cells contain the word Value? If so the cells are not numeric, they're all considered text. If you explain it clearly & show exactly what you need you'll be more likely to get the help you need. I can have a look tomorrow & if I can't finish it, it'll be next weekend before I can look at it again.
Cheers
Phil

Hello!
Thanks for your reply!
I have the following statuses in total: G, O, A, W, F. Let it return 4 instead of 3 statuses. So I need G, O, A, W. Only F I need not to be in the summary.
The return columns needed are A, B, C, D, E, F, G, H, I, J, K, R, S, T, U.
Only column I, J and K are number formatted. The rest is text.
Also, do the names of the sheets matter?

Upload an example of exactly what it's supposed to look like after the macro has run. As you have it now every cell will be Value 1, Value 2, Value 3 + the source cell colour etc etc. The macro can copy whatever is in the cell on the source sheets including the colours to the Total sheet, the problem comes with the sorting, especially if you have a mixture of text & numeric.

Hello!
I upload the file

Attached Files

Summary.xlsx (40.9 KB, 13 views)

Hi,

You supplied a workbook. But, can you tell us which sheets you look at and the sheet that result is in?

Hi!
I need the data in sheets:
Crawler, Поръч, MandM, Fragrance, 5£
summarized in sheet Summary

Hi,

See if this works for you.
You will see a "Run" button. When you click it the code will copy all of the required sheet to "Summary". As you can see I renamed 2 of the worksheet. I also removed the "Hidden" columns.
If you rerun the code the data in the summary sheet will copy to the last row. In other it will keep adding to the Summary. You will need to clear the Summary sheet before you run the code again.
You can also set a New button that when clicked to remove the existing data in the Summary sheet.

Attached Files

Summary (1).xlsm (85.5 KB, 13 views)

That's just magic! Thanks!

Will try to customize it a little bit and apply it to my file.

Thank youuuuu!

Hi,
Happy to know it works.

Now if you rename the worksheets you will also need to change the code to reflect the worksheet names.
Change "Lookup" and "Summary" in the code to your sheet names.

Will try. Thank you!

I tried to put it to my file. But it returns all the data from all other sheets. And does not return columns L, M, N and O in the summary.
And can it return the data in hidden cells also? Because I hide different columns in different sheets and in the summary columns don't fit.

HI,

I ran my file and in the "Summary" sheet I can see L,M,N and O.
As mention I removed the "Hidden" columns. Did not know if you were using them.
Attach your file so I can see what you did.

oh I have lots of names and other not public info. As I was trying to remove all of it, I just totally messed up everything