Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 09-02-2016, 06:08 AM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default Multiple found values from five sheets, multiple returned values in sheet six?

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
File Type: xlsx Book01.xlsx (15.1 KB, 14 views)
Reply With Quote
  #2  
Old 09-09-2016, 05:45 PM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

Anyone has any idea?
Reply With Quote
  #3  
Old 09-09-2016, 10:01 PM
Philb1 Philb1 is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 10 Multiple found values from five sheets, multiple returned values in sheet six? Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

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
Reply With Quote
  #4  
Old 09-10-2016, 01:54 AM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

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?
Reply With Quote
  #5  
Old 09-10-2016, 09:03 PM
Philb1 Philb1 is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 10 Multiple found values from five sheets, multiple returned values in sheet six? Office 2010 32bit
Advanced Beginner
 
Join Date: Feb 2016
Location: Auckland
Posts: 43
Philb1 is on a distinguished road
Default

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.
Reply With Quote
  #6  
Old 09-11-2016, 03:13 AM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

Hello!
I upload the file
Attached Files
File Type: xlsx Summary.xlsx (40.9 KB, 11 views)
Reply With Quote
  #7  
Old 09-11-2016, 05:12 PM
charlesdh charlesdh is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 7 32bit Multiple found values from five sheets, multiple returned values in sheet six? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

Hi,

You supplied a workbook. But, can you tell us which sheets you look at and the sheet that result is in?
Reply With Quote
  #8  
Old 09-12-2016, 12:48 AM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

Hi!
I need the data in sheets:
Crawler, Поръч, MandM, Fragrance, 5£
summarized in sheet Summary
Reply With Quote
  #9  
Old 09-14-2016, 01:37 PM
charlesdh charlesdh is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 7 32bit Multiple found values from five sheets, multiple returned values in sheet six? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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
File Type: xlsm Summary (1).xlsm (85.5 KB, 11 views)
Reply With Quote
  #10  
Old 09-14-2016, 02:08 PM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

That's just magic! Thanks!

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

Thank youuuuu!
Reply With Quote
  #11  
Old 09-14-2016, 02:21 PM
charlesdh charlesdh is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 7 32bit Multiple found values from five sheets, multiple returned values in sheet six? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #12  
Old 09-14-2016, 11:30 PM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

Will try. Thank you!
Reply With Quote
  #13  
Old 09-18-2016, 01:45 PM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

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.
Reply With Quote
  #14  
Old 09-18-2016, 05:01 PM
charlesdh charlesdh is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 7 32bit Multiple found values from five sheets, multiple returned values in sheet six? Office 2010 32bit
Expert
 
Join Date: Apr 2014
Location: Mississippi
Posts: 382
charlesdh is on a distinguished road
Default

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.
Reply With Quote
  #15  
Old 09-19-2016, 11:15 AM
irisha irisha is offline Multiple found values from five sheets, multiple returned values in sheet six? Windows 8 Multiple found values from five sheets, multiple returned values in sheet six? Office 2007
Novice
Multiple found values from five sheets, multiple returned values in sheet six?
 
Join Date: Oct 2015
Posts: 21
irisha is on a distinguished road
Default

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
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Display multiple values as new values based on selection from template. MvdB Excel 2 09-29-2015 08:51 PM
Multiple found values from five sheets, multiple returned values in sheet six? IF Formula - multiple values Jac_Fcb Excel 1 03-17-2015 12:41 AM
Multiple found values from five sheets, multiple returned values in sheet six? Reference another workbook and return values from the same cell across multiple sheets/tabs lwls Excel Programming 5 11-08-2014 02:11 PM
Multiple found values from five sheets, multiple returned values in sheet six? I need to add multiple values based on multiple criteria in a cell not sure what to do AUHAMM Excel 3 10-27-2014 09:11 PM
carry values of various sheets ​​to another sheet zizao Excel 2 06-20-2014 12:47 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 11:17 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft