Microsoft Office Forums

Go Back   Microsoft Office Forums > Microsoft Excel > Excel

Reply
 
LinkBack Thread Tools Display Modes
  #1  
Old 10-02-2017, 09:05 PM
MJSOZ MJSOZ is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Oct 2017
Posts: 2
MJSOZ is on a distinguished road
Default 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
Attached Files
File Type: xlsx Excel Question Book1.xlsx (18.5 KB, 2 views)
Reply With Quote
  #2  
Old 10-02-2017, 10:55 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 88
ArviLaanemets is on a distinguished road
Default

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).
Attached Files
File Type: xlsx QuestionBook.xlsx (22.1 KB, 9 views)
Reply With Quote
  #3  
Old 10-03-2017, 09:35 PM
MJSOZ MJSOZ is offline Windows 10 Office 2010 32bit
Novice
 
Join Date: Oct 2017
Posts: 2
MJSOZ is on a distinguished road
Default 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
Reply With Quote
  #4  
Old 10-03-2017, 10:51 PM
ArviLaanemets ArviLaanemets is offline Windows 8 Office 2016
Advanced Beginner
 
Join Date: May 2017
Posts: 88
ArviLaanemets is on a distinguished road
Default

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

Thread Tools
Display Modes


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


All times are GMT -7. The time now is 04:30 PM.


Powered by vBulletin® Version 3.8.1
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2011, Crawlability, Inc.
MSOfficeForums.com is not affiliated with Microsoft