#1
|
|||
|
|||
Query on another tab
Hi all,
Please could you assist in this? I thought this was going to be an easy solution but to no avail I have a spreadsheet with multiple tab's Tab one is the main tab containing all the data The second Tab is called "AGS" I want to query the Main tab and search for all records containing "AGS" and display it in the AGS tab And so forth for the remainder of the other Tab's Attached is a sample Please could you possible assist? |
#2
|
||||
|
||||
Hi and welcome
The other tabs are empty and don't even have headers? Could you please be more complete ? Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Hi,
Indeed the other tabs are empty without headers This should be imported by the query from the first tab |
#4
|
|||
|
|||
For Excel, you can use macros or formulas to perform similar tasks.
In the "AGS" tab, create a formula or macro that will filter the data from the main tab and copy only those records that contain "AGS". Once the data has been copied, it can be pasted into the appropriate cells in the AGS tab. |
#5
|
|||
|
|||
Hi,
I do know know how to do this. Could you possible assist with this in the attached spreadsheet? |
#6
|
|||
|
|||
Quote:
Do you want to abandon working and easily manageable design with one, where different data groups are on different sheets? Which will be a headache, when you need e.g. some total overview. And you have to redesign your workbook every time some new grouping is applied. Or you want simply to display data of single data group? Then design a report sheet, where at top of sheet the user can select (using Data Validation List) any data group identificator (e.g. "AGS" for your example) , and in table below all wanted data are displayed from main table. To do this, you either: a) use an ODBC query in report sheet, with cell where user selected the group identificator as WHERE clause parameter, and which will be refreshed whenever a new data group is selected; b) have the table on report sheet with formulas, which determine the row in main table to be read into report table row (e.g. by adding hidden columns into main table, where the row number of main table, and the entry number of any identificator you use, are calculated) using e.g. SUMIFS function, and then use INDEX function to get all values from this row in main table into row of report table. With either of solutions, to display certain group of data, you need only 2 sheets! And whenever you add a new data group to main table, all editing you need is to make this new group to be included into Data Validation List on report sheet! |
#7
|
|||
|
|||
The data tab's are set, if I do need to add a new group, I will make a new sheet but for now they are set
The reason I want it this way is I only need to update the main sheet and the data will be displayed accordingly on the tab's I then need to run additional functions from the individual tab's for reporting I tried the the If Index but think I might not have done it correctly |
#8
|
|||
|
|||
Hi all,
Any other suggestions please |
#9
|
||||
|
||||
This is not very clear...
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#10
|
|||
|
|||
I have my master data in tab 1
Tab 2 is named "AGS" All the data in the master data tab that equals "AGS" in the first column, must be displayed in the "AGS" tab, Column A - G |
#11
|
||||
|
||||
Attached a solution using Power Query ( it's called Get and Transform in 2019 I think).
In this file you can add/delete/modify rows to the main tab and just hit " Refresh" to update the AGS tab. For the benefit of members unable/unwilling to open the file here is the code Code:
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content], #"Filtered Rows" = Table.SelectRows(Source, each ([Type] = "AGS")) in #"Filtered Rows"
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#12
|
|||
|
|||
Thank you so much, I thought this would have been an easier solution but just could not use the standard functions
Much appreciated, not only does it work well, it looks good Thank you |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Do I need a power query for this one? | shabbaranks | Excel | 5 | 04-10-2019 03:29 AM |
IFS query (I think) | ronsar | Excel | 1 | 12-06-2018 09:04 AM |
Query and Power Query not working Excel 2016 | bl10 | Excel | 0 | 07-22-2016 06:25 PM |
Calendar Query | mwh1002 | Outlook | 0 | 07-30-2013 08:50 AM |
How to Query Text in MS-Excel 2010 or Import Data & Query outside of Excel? | mag | Excel | 0 | 10-18-2012 11:15 AM |