Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 03-13-2020, 03:25 AM
allex011 allex011 is offline Help multiple sheets in one sheet Windows 7 32bit Help multiple sheets in one sheet Office 2007
Novice
Help multiple sheets in one sheet
 
Join Date: Dec 2017
Posts: 23
allex011 is on a distinguished road
Default Help multiple sheets in one sheet


I need help, how to in one sheet place values from different sheets (30 row for each sheet). Also is it possible to have that in sheet that collect values, hide empty cell but when cells in multiple sheet are filed, hidden cell are visible again

Thanks in andvaced
Aleksandar
Reply With Quote
  #2  
Old 03-13-2020, 06:41 AM
NoSparks NoSparks is offline Help multiple sheets in one sheet Windows 10 Help multiple sheets in one sheet Office 2010
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 770
NoSparks has a spectacular aura aboutNoSparks has a spectacular aura aboutNoSparks has a spectacular aura about
Default

Quote:
how to in one sheet place values from different sheets (30 row for each sheet).
Based on that much information, copy and paste as values.


Quote:
Also is it possible to have that in sheet that collect values, hide empty cell but when cells in multiple sheet are filed, hidden cell are visible again
What do you mean by that?
To my knowledge you can hide a row (16,384 cells) or a column (1,048,576 cells) but not single cells.
Reply With Quote
  #3  
Old 03-13-2020, 07:01 AM
ArviLaanemets ArviLaanemets is offline Help multiple sheets in one sheet Windows 8 Help multiple sheets in one sheet Office 2016
Expert
 
Join Date: May 2017
Posts: 545
ArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the roughArviLaanemets is a jewel in the rough
Default

Quote:
Originally Posted by allex011 View Post
I need help, how to in one sheet place values from different sheets (30 row for each sheet).
Create an UNION query with every sub-query reading data from one sheet. The query returns data as a Table. You can set conditions for every partial query to filter out entries you don't want to be read. And you can determine a sort order for whole result table. You can set the query to be refreshed:
1. when the workbook is opened;
2. with frequency you can determine (but this option freezes the workbook for time the query is refreshed, and this may be annoying - I myself never use this option);
3. you can refresh the query manually whenever you want.

Ever better advice - keep your data entry on single sheet and add a column with allows to differ data type in row (equivalent of sheet name in your current setup). Then you can:
a) use autofilter to display one (or couple) of data type(s) only;
b) Create a report sheet, where you can select the data type (use Data Validation List for this cell), and use formulas to populate a report table with selected type of data. You have 2 sheets instead of whole bunch of them, and you have same functionality.
Reply With Quote
  #4  
Old 03-13-2020, 10:43 PM
allex011 allex011 is offline Help multiple sheets in one sheet Windows 7 32bit Help multiple sheets in one sheet Office 2007
Novice
Help multiple sheets in one sheet
 
Join Date: Dec 2017
Posts: 23
allex011 is on a distinguished road
Default

Thank you very much
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple found values from five sheets, multiple returned values in sheet six? irisha Excel Programming 26 09-30-2016 01:20 AM
Complex calculation from multiple sheets to master sheet inreality01 Excel 49 01-06-2016 03:09 PM
Need help in automating data from multiple sheets into a single sheet veedee Excel Programming 8 06-12-2014 10:04 PM
Help multiple sheets in one sheet Need help in automating data from multiple sheets into a single sheet veedee Excel 1 06-11-2014 04:01 AM
Help multiple sheets in one sheet How do I merge data from one sheet in a workbook out into multiple sheets nolesca Excel 4 06-07-2010 08:13 AM


All times are GMT -7. The time now is 10:57 AM.


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