#1
|
|||
|
|||
Formulas - data from one worsheet to another
Hi all,
Thank you in advance......I'm attaching a mock up of a simple staff training register that I'm trying to do (the formatting/drop downs etc i'll worry about later!!). I'm needing the info that is entered into the Register (green tab) in the highlighted columns, to be transferred onto the summary sheet (yellow tab), under the corresponding staff number and category. I'm sure a combination of IF and LOOKUP would be the way to go, but I just can't work out the correct function strings Appreciate any help.......thanx |
#2
|
|||
|
|||
The number of rows for every category is undetermined, so making it using worksheet formulas assumes, you predesign maximal possible number of rows into every category. Doesn't it sound as horror story for you?
Your summary setup is too complex for Pivot Table, so an ODBC Query may be the best solution. In attached file, you have to edit query's Data Connection string and Command text (until then you get an error whenever you open the workbook) so the right workbook in right location is connected (Data > Connections > Properties. Activate tab Definition and there are 2 fields where you have to edit file path - and file name in case you change it too). |
#3
|
|||
|
|||
Thank you
Thank you so much for your help and I'm so sorry but this is way above my head are you able to explain your third paragraph again in layman terms (so sorry!!) Really appreciate your help, thank you
|
#4
|
|||
|
|||
When you are opening the linked file, you'll get an Excel message like "[Microsoft] [ODBC Excel Driver] The Microsoft ...", because ODBC Query points to same workbook in location, which doesn't exist for you. To get the workbook to work properly:
1. Save the attached workbook to some location (in your computer/in your network resource); 2. Open the workbook (for Excel message, click OK); 3. Select from Excel Menu Data > Connections. The Workbook Connections window opens. You see one connection - qSummary; 4. As there is a single connection, you don't need to select the connection - simply click the Properties button at right of Workbook Connections window. The Connection Properties window opens; 5. Activate Definition tab; 6. In Connection string field is current Connection string displayed (DSN=Excel Files;DBQ=C:\USERS\ALS\DOCUMENTS\QuestionBook.xlsx;DefaultDir=C:\USERS\ALS\DOCUMENTS;DriverId=1046;MaxBufferSize=2048;PageTimeout=5. Edit part of Connection string colored red according workbook location and name in your computer/network; 7. In Command text field the Query string is displayed (SELECT ... FROM `C:\USERS\ALS\DOCUMENTS\QuestionBook.xlsx`.nRegister nRegister ...). Edit part of Command text colored red according workbook location and name in your computer/network; 8. OK. Refresh (when all was done properly, the query is refreshed and you get no error messages). Close. The ODBC query is set to be refreshed when you open the workbook. When you edit or add data to Register sheet, and you want the summary to be recalculated, select from menu Data > Refresh All, or right-click anywhere on summary table, and select Refresh from drop-down menu. Whenever you move the workbook to new location, or rename the workbook, you have to edit the Connection string and Command text again (a drawback of ODBC queries). Or you have to write an Open event in VBA which rewrites these automatically (NB! Then you have to save the workbook in .xlsm format). |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Pasting new data and formulas stop working | ehmo | Excel | 4 | 07-10-2017 01:58 AM |
Need Multiple Formulas Pulled from a data chart | rccolb | Excel | 1 | 03-20-2017 07:42 AM |
Formulas transferring to other pages in workbooks, but data isn't. | Melissa Ames | Excel | 4 | 03-02-2016 01:58 PM |
Formulas/Scripts That Cross Reference & Import Data | ekeithjohnson | Excel Programming | 3 | 08-23-2014 11:26 PM |
If/then formulas with data validation | shira47 | Excel | 1 | 04-09-2012 05:14 AM |