If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple worksheet queries
Hi _Problem_ - 3 bosses that know how to send/receive email and open an attachment but need to do the minimum to get the data they want to view. _Answer_, create a file that contains hyperlinks and layouts that make input and viewing easy, automatic and clear!! Firstly, I'm teaching myself basic formulas & functions (with help from you guys and your posts) but I'm a newbie to anything above basics (addins & VBA included). However, I need to put together a file which holds a sheet listing all clients of a branch on sheet one (upto a 1000) with max 5 data cells across for each. Each client has a unique number that I can then hyperlink to the clients individual sheet (yes I need to make 1000 sheets) The details held within each individual client sheet are both text and numerical as in names, products, dates, amounts of money (not invoices), contact details, salesperson, multiple currencies, paperwork completion tick off, outstanding paperwork reqd, commissions, late payments etc (a lot of info). The existing worksheet (that I am now expanding and redesigning) used headings across the top on a single sheet and of course looks too messy so that the data isn't clear. The headings are now all down column A and subgrouped with the input data going accross the sheet. The end result of this is to break up a single sheet of all the info into unique client sheets from the list or index sheet. Before I continue, if anyone thinks Excel cannot achieve the above to automatically update & produce a sales/comm multi currency worksheet from the input data - pls tell me an alternative (though pls remember the bosses don't use/know how to use Access!!) Since much of the work is repetetive is there a way to: a) add 1000 sheets in one go b) paste the layout & format of the client data sheet to all 1000 sheets (without needing to resize each sheets columns each time) c) name the sheets automatically from the client list as a client is added (ie name/unique number) d) create the hyperlink from the client lists unique number to the clients data sheet (cell position with unique number is different on client list sheet each time new client is added eg D2,D3,D4 but the same position each time on the client data sheet) e) add the date of the new client in the client list to the clients data sheet (cell ref different each time add new client - see d) above. f) add the clients name from the client list to the client data sheet (cell ref different each time add new client - see d) above. Then if the above can be done (or not): a) how do I create an auto updating running total on a seperate sheet as new client details are added (or each time do I have to renew the sum formula to include the new client data sheet with the existing ones). Sorry if this is basic stuff and I've been searching the forum but cannot find anything specific to my project (though I've found lots of things that will help me so those questions have been answered) Thks in anticipation, Liam -- liam ------------------------------------------------------------------------ liam's Profile: http://www.excelforum.com/member.php...o&userid=19989 View this thread: http://www.excelforum.com/showthread...hreadid=345785 |
#2
|
|||
|
|||
Hi
I consider this as bad idea. More input sheets means much more possibilities for user errors. And 1000 sheets - it will use a lot of memory. My advice is to keep the number of sheets as low as possible. Put all unique client info into a single table - a row for every client. When you have some info, which need several rows for client, p.e. payments along with payment dates, create a separate sheet for every kind of such info, with one column containing client identifier. To view client info, you design report sheet(s), where you select client (from data validation list drop-down is a good idea), and all wanted info for this client is retrieved through formulas from main clients table (and from additional tables, when such were created). To get info from main table (single row for client) you can use VLOOKUP function. For this, the client identifier must be in leftmost column of lookup range in main table. To get detailed info from additional table(s), where for selected client may be several rows of info, you must have an additional column as leftmost (can be hidden) in additonal table(s), with formula in it. The formula returns an empty string, when record doesn't match the condition selected on report sheet (wrong client), and a number for records matching the report condition - 1 for first such record in table, 2 for second, etc. (you can have additional conditions on report sheet, p.e. time interval, or month, or some other info available in additional table, or calculable) Now you can have such info into report sheet as table, using VLOOKUP to look for 1 in additional table, to get data into first row, etc. You have to estimate some reasonable number of rows in such table - when the number of rows in additional table is less, empty rows are displayed on report. When you have several additional tables, a table for every one must be created on report. When you don't need detailed info from additional tables, but only count of records for which conditions are filled, or sum of some value for such rows, then no additional tables are needed - you can calculate such values directly using SUMIF or SUMPRODUCT functions. A step further is to separate reoprt(s) and data input. You create special report file(s). All data from input workbook is read into report workbook, using links, or through ODBC queries - you mirror source info into report workbook. I myself prefer to hide such mirror sheets from user. (When you have additional tables, then the count of matching records is now done on mirrored table - input workbook doesn't know nothing about report conditions) Report(s) is(/are) created using mirrored sheet(s) as source table(s). Bonuses by such design: 1. When report is viewed, it doesn't interfere with data input on input workbook. No need for shared use too. 2. People which use report, see only data meant for them to be seen. 3. You can have several report workbooks, based on same input workbook - and of same or different design. And they can view reports all at same time when needed. As follows, you can for every user design his individual report workbook. 4. Both input and reoprt workbooks usually work faster, especially when ODBC query(es) is(/are) used to mirror source data (in input workbook, there is no additional sheets with a lot of cumbersome formulas, in report workbook(s), source data are mirrored as values. And you can get from source workbook only needed info, depending on your report design) -- When sending mail, use address arvilattarkon.ee Arvi Laanemets "liam" wrote in message ... Hi _Problem_ - 3 bosses that know how to send/receive email and open an attachment but need to do the minimum to get the data they want to view. _Answer_, create a file that contains hyperlinks and layouts that make input and viewing easy, automatic and clear!! Firstly, I'm teaching myself basic formulas & functions (with help from you guys and your posts) but I'm a newbie to anything above basics (addins & VBA included). However, I need to put together a file which holds a sheet listing all clients of a branch on sheet one (upto a 1000) with max 5 data cells across for each. Each client has a unique number that I can then hyperlink to the clients individual sheet (yes I need to make 1000 sheets) The details held within each individual client sheet are both text and numerical as in names, products, dates, amounts of money (not invoices), contact details, salesperson, multiple currencies, paperwork completion tick off, outstanding paperwork reqd, commissions, late payments etc (a lot of info). The existing worksheet (that I am now expanding and redesigning) used headings across the top on a single sheet and of course looks too messy so that the data isn't clear. The headings are now all down column A and subgrouped with the input data going accross the sheet. The end result of this is to break up a single sheet of all the info into unique client sheets from the list or index sheet. Before I continue, if anyone thinks Excel cannot achieve the above to automatically update & produce a sales/comm multi currency worksheet from the input data - pls tell me an alternative (though pls remember the bosses don't use/know how to use Access!!) Since much of the work is repetetive is there a way to: a) add 1000 sheets in one go b) paste the layout & format of the client data sheet to all 1000 sheets (without needing to resize each sheets columns each time) c) name the sheets automatically from the client list as a client is added (ie name/unique number) d) create the hyperlink from the client lists unique number to the clients data sheet (cell position with unique number is different on client list sheet each time new client is added eg D2,D3,D4 but the same position each time on the client data sheet) e) add the date of the new client in the client list to the clients data sheet (cell ref different each time add new client - see d) above. f) add the clients name from the client list to the client data sheet (cell ref different each time add new client - see d) above. Then if the above can be done (or not): a) how do I create an auto updating running total on a seperate sheet as new client details are added (or each time do I have to renew the sum formula to include the new client data sheet with the existing ones). Sorry if this is basic stuff and I've been searching the forum but cannot find anything specific to my project (though I've found lots of things that will help me so those questions have been answered) Thks in anticipation, Liam -- liam ------------------------------------------------------------------------ liam's Profile: http://www.excelforum.com/member.php...o&userid=19989 View this thread: http://www.excelforum.com/showthread...hreadid=345785 |
#3
|
|||
|
|||
Thks Arvi for your comments. I wish I could keep the info to a few sheets but I know the bosses and their limitations and the amount of info they want to see (placed on a single sheet) just isn't feasable for clarity and ease of use. I'm already into 30 columns on a std spreadsheet layout and I can see that rising to 50. Sure I could break the info into a few sheets but I'm still left with a long list on each sheet that they'd find incomplete and they wouldn't want to keep having to change sheets and doing 'look ups" each time. I'll do a few tests on what you've said & see if I can't combine what I need. Thks again, Liam -- liam ------------------------------------------------------------------------ liam's Profile: http://www.excelforum.com/member.php...o&userid=19989 View this thread: http://www.excelforum.com/showthread...hreadid=345785 |
#4
|
|||
|
|||
Hi
"liam" wrote in message ... Thks Arvi for your comments. I wish I could keep the info to a few sheets but I know the bosses and their limitations and the amount of info they want to see (placed on a single sheet) just isn't feasable for clarity and ease of use. I'm already into 30 columns on a std spreadsheet layout and I can see that rising to 50. You can break the main sheet into several ones - on one of them you enter the client identifier, on other sheets this identifier is displayed through links. Example: You have sheets Clients0 and Clients1. The column A on both sheets is ClientID. On sheet Clients0 entries into column ClientID are limited to be unique (you can use data validation for it). On sheet Clients1 in column ClientID is linking formula, like A2=IF(Clients0!A2="","",Clients0!A2) The formula is copyed into column A at least for same number of rows, as has clients table on sheet Clients0, but preferably you have some reasonable amount of spare rows prepared. On report(s), you use the same VLOOKUP to get the information, but depending on searched data, the source table will be on sheet Client0 or Client1. Sure I could break the info into a few sheets but I'm still left with a long list on each sheet that they'd find incomplete and they wouldn't want to keep having to change sheets and doing 'look ups" each time. It looks like you didn't fully understand what I adviced. Boss don't need to change anything except he selects client, whose info he wants to display. All info he needs about selected client from selected report, is gathered into single report from (various) source (input) table(s) (and usually is designed to fit some printout sheet format, like A4 or Letter, Landscape/Portrait). On report sheet, you absolutely don't have to follow same layout, as on source sheets - you can group various data, use different fonts and colors, insert charts based on client data etc. Nothing like some dull Xteen-column table. I myself use table format on report sheets only for multiple-row data - data from main client table are placed on report sheet in a way, most appropriate to visualize the information. Arvi Laanemets |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I auto-filter multiple ranges on one worksheet? | AREV | Worksheet Functions | 3 | February 14th, 2005 09:50 PM |
Print multiple worksheet in one layout | Jimbo | General Discussion | 3 | August 21st, 2004 12:44 PM |
Inserted Excel worksheet on multiple slides | Val | Powerpoint | 4 | August 15th, 2004 07:58 PM |
Exporting Multiple Queries to 1 Excel Workbook with VBA | Anthony | Running & Setting Up Queries | 2 | June 3rd, 2004 07:59 PM |
Date Range in Multiple Queries | James Stephens | Running & Setting Up Queries | 1 | June 2nd, 2004 12:10 AM |