Collecting data from multiple sheets

I have a spreadsheet with approx 15 sheets.  The sheets are basically a list 
of questions and the answers provided by users.  What I need to do is 
transfer the answers provided by users onto a single row on another sheet and 
then save that sheet as a csv file so that it can be imported into our 'main' 
database.

We use XL 2003.

I know that I haven't given much for you to go on but if you think you can 
help, let me know and I will send you an example file.

Thanks in advance.

Gareth
0
Utf
12/10/2009 12:16:01 PM
excel.programming 6508 articles. 2 followers. Follow

1 Replies
756 Views

Similar Articles

[PageSpeed] 26

Hi Gareth

--If the sheetnames are named after the username.. user1, user2 etc; then 
arrange the user names in ColA

Col A	Col B	Col C	Col D
Name	Ques 1	Ques 2	Ques 3
User1	=		
User2	=		

--In cell B2 apply the below formula which will look at User1!B1. Copy down 
the formula as required which will pick up the values from each user sheet.
=INDIRECT("'" & A2 & "'!B1")

--If the answer cells are arranged in an order you can modify the above 
formula so that you dont need to have a separate formula for Question 2..

-- 
Jacob


"Gareth" wrote:

> I have a spreadsheet with approx 15 sheets.  The sheets are basically a list 
> of questions and the answers provided by users.  What I need to do is 
> transfer the answers provided by users onto a single row on another sheet and 
> then save that sheet as a csv file so that it can be imported into our 'main' 
> database.
> 
> We use XL 2003.
> 
> I know that I haven't given much for you to go on but if you think you can 
> help, let me know and I will send you an example file.
> 
> Thanks in advance.
> 
> Gareth
0
Utf
12/10/2009 12:57:02 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Can the data in a chart table be right justified?
Ecxel 2003 and previous versions of the product center the data in the data columns. Can the data in the columns of a chart table be right justified? In article <DABF738B-6C0D-458B-B082-FA9BD8F126A7@microsoft.com>, =?Utf- 8?B?c2FtIGVhZ2xl?= <sam eagle@discussions.microsoft.com> says... > Ecxel 2003 and previous versions of the product center the data in the data > columns. Can the data in the columns of a chart table be right justified? > Have you tried to format the table? If yes, and you haven't been successful it is probably because XL allows very limited cust...

Data migration - Adventure Works
Hiya... I have a company where the adventure works db has been used and had a lot of data populated into the system. We have now purchased MSCRM and have obtained the company reg keys. What is the easiest way to get the data from the 1 system to the next? We will be establishing a new AD domain and users for the new system.... Data Migration Framework? Redeployment Framework? ;) redeploment tools http://www.microsoft.com/downloads/details.aspx?FamilyID=bfced393-61db-49af-9a50-4a90b311fa7d&DisplayLang=en -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "funboy...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Email Statements from Collection Module
When I attempt to email statements from the Collection Module I am getting an error message, ' Wrong number of arguments to 'RM_Statements_Update_Header' ...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Creating a chart based on the data in an embedded worksheet
Hi, I have a worksheet with several embedded worksheets. I would like to create a chart based on the data of one of the embedded worksheets without putting the chart in the embedded worksheet. I have tried unsuccessfully to do this. I just wondered if anyone knew how to do it. Thanks, JK JK - You're embedding worksheets within worksheets? Why? Why not just insert the worksheets in line with the main worksheet? To open or edit the embedded worksheet, the parent Excel has to open another instance of Excel, and the chart on the outside of this other instance will never be able to acce...

GP SP#2 hangs with sa and Collections Issue
I just installed 8.0 SP#2 (ran the script that it says to run cuz we had upgraded from previous version to remove dups in smartlist tables) and when I log into GP as sa, it hangs and CPU sits at 100%. When I was able to log in previously to SP#2, I constantly received errors about the CN_Collector_MAIN table with invalid column names etc. I've deleted the table, then re-ran the create tables, but it keeps coming back with this error. I've even gone into file > maintenance > sql and deleted and recreated the table, but then get "number of results doesn't match ...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

XML Serialization Error of Collection
// Can anyone explain the bizarre XML Serialization behavior seen in the following code. // It seems that a collection can not be XML Serialized if it contains an Add() method // with a single parameter where that parameter is of a type in the System.Data namespace // (or a class derived from something in the System.Data namespace). // Is there any way to prevent this from blowing up? using System; using System.Data; using System.Collections; using System.IO; using System.Xml.Serialization; namespace XMLStub { /// <summary>A simple class that holds a collection of ints.</summary>...

Data validation list from another worksheet?
Is it possible that the value list for data validation be populated fro another worksheet? Puneet Aror -- puneetarora_1 ----------------------------------------------------------------------- puneetarora_12's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1840 View this thread: http://www.excelforum.com/showthread.php?threadid=38572 Sure is! Use a named range as described here: http://www.officearticles.com/excel/drop-down_using_data_validation_in_microsoft_excel.htm ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "punee...

Seperate jobs for multiple worksheets
Separate jobs are sent to printers when I select multiple worksheets... If I print to PDF, multiple files are created. How can I avoid this? ...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

"external data sources" and "external data ranges"
what is the difference between these? i have run the vb macro code on http://support.microsoft.com/kb/330383 to check if i have any external data sources or data ranges but there are none. the reason i need to know is becuase i'm working with office sharepoint server 2007 and i cannot access a file thru the web access web part because it says: "The workbook that you selected cannot be loaded because it contains the following features that are not supported by Excel Services: External data ranges (also called query tables)" anyone? thanks ...

Source data, in Chart Menu, is grey and not accessible.
I set up a pivot table and designed a chart, saved and closed out of the workbook. Upon returning to work on this project, when selecting the chart menu to work with my source data, the source data is grey and not accessible. What might be causing this and can I correct it? It's a pivot chart. As soon as it's created, it's linked permanently to the pivot table, and you cannot change the source data. you cannot add more data that's not in the pivot table to the chart, nor can you remove data that is in the pivot table from the chart. - Jon ------- Jon Peltier, Microsoft...

Multiple DHCP servers
I have 2 DHCP servers setup in my domain using the 80/20 rule that Microsoft recommends. I have problems with the 2nd DHCP server however, because it continues to run out of available address leases as it would appear clients default to pulling their addresses from the DHCP server with fewer addresses. Is there a way to default clients to pull their address from a specific DHCP server first and when unavailable, from the second DHCP server? Hi JT Welcome to the Microsoft Macintosh Office newsgroup. Personally, I would like my computers, refrigerator and heating system to each have th...

Creating an Income & Expenditure Sheet
i am not sure of the best way to create an Income & Expenditure Sheet for a weekly record of transactions in my small business. Start here: http://office.microsoft.com/en-us/templates/FX100595491033.aspx -- Gary''s Student - gsnu200909 "Molly" wrote: > i am not sure of the best way to create an Income & Expenditure Sheet for a > weekly record of transactions in my small business. ...

Attachments go multiple times
Outlook 2002 SP3 Win XP DSL connection When sending attachments the status pane shows "task 1 of 1" and there is a progress bar. When the progress bar reaches 100%, the status then goes to "task 2 of 2" with the progress bar starting again. This generally repeats itself several times before the message finally moves to the Sent Items folder. In the meantime, the recipient has received several copies of the message. Anyone know what is happening here, and how to stop it? Cheers, ...

Importing multiple identities from Express to Outlook
Help! I have been able to import everything from my main identity from Outlook Express to Outlook, but cannot figure out how to import from my other identity. Please Help! Thanks! bay157 <bay157@discussions.microsoft.com> wrote: > Help! I have been able to import everything from my main identity > from Outlook Express to Outlook, but cannot figure out how to import > from my other identity. Please Help! Thanks! Make the other identity the main identity and then you'll be able to import that. Remember to export mail mesasges from Outlook Express. Do not import m...