Convert One Column Data to Multiple Columns Data
I have a Query I am trying to Build that has the Following data: Name WO Days Old AD K-12345 10 AD K-12346 23 AD K-12368 02 AD K-12351 05 AD K-12386 20 What I am needing to is move the Days old Data to three Colums based off of the Days old Name WO 1-5 days 6-12 days 13+ Days AD K-12345 10 AD K-12346 23 AD K-12368 02 Something like that if it is possible. I have searched for this and could not find any other topics like this, Any help will be appreciated. Thanks for your time.
|
0 |
|
Utf
2/8/2010 2:44:01 PM
access.queries
6343 articles. 
1 followers.
5 Replies
1604 Views
Two questions: 1. Where does Days Old come from? Is it a value in a table or something computed from a date field. 2. Could you ever have something like this, and, if so, how should it look? AD K-12345 10 AD K-12345 09 -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Rob K" wrote: > I have a Query I am trying to Build that has the Following data: > > Name WO Days Old > AD K-12345 10 > AD K-12346 23 > AD K-12368 02 > AD K-12351 05 > AD K-12386 20 > > What I am needing to is move the Days old Data to three Colums based off of > the Days old > > Name WO 1-5 days 6-12 days 13+ Days > AD K-12345 10 > AD K-12346 23 > AD K-12368 02 > > Something like that if it is possible. > > I have searched for this and could not find any other topics like this, Any > help will be appreciated. Thanks for your time. >
|
|
0 |
|
Utf
2/8/2010 2:54:01 PM
Create a query something like below. The 01 and 06 are not mistakes. If you leave them 1 and 6, you will have a sort problem across the columns. Put in the proper table name. SELECT RobK.Name, RobK.WO, RobK.[Days Old], IIf([Days Old] Between 1 And 5,"01-05 days",IIf([Days Old] Between 6 And 10,"06-10 days","13+ days")) AS OldDays FROM RobK; After getting the above query to run, create a crosstab query based upon it. Make sure to change the "qryRobK" to the name of the query above: TRANSFORM Max(qryRobK.[Days Old]) AS [MaxOfDays Old] SELECT qryRobK.[Name], qryRobK.[WO] FROM qryRobK GROUP BY qryRobK.[Name], qryRobK.[WO] ORDER BY qryRobK.[Name], qryRobK.[WO] PIVOT qryRobK.[OldDays]; Note: If the Days Old field is left null, zero, or a negative number, it will show up in the 13+ days column. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Rob K" wrote: > I have a Query I am trying to Build that has the Following data: > > Name WO Days Old > AD K-12345 10 > AD K-12346 23 > AD K-12368 02 > AD K-12351 05 > AD K-12386 20 > > What I am needing to is move the Days old Data to three Colums based off of > the Days old > > Name WO 1-5 days 6-12 days 13+ Days > AD K-12345 10 > AD K-12346 23 > AD K-12368 02 > > Something like that if it is possible. > > I have searched for this and could not find any other topics like this, Any > help will be appreciated. Thanks for your time. >
|
|
0 |
|
Utf
2/8/2010 3:10:01 PM
1. It is a Value in a table. 2. No there will not be any Duplcate WO as you have listed "Jerry Whittle" wrote: > Two questions: > > 1. Where does Days Old come from? Is it a value in a table or something > computed from a date field. > > 2. Could you ever have something like this, and, if so, how should it look? > > AD K-12345 10 > AD K-12345 09 > -- > Jerry Whittle, Microsoft Access MVP > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > "Rob K" wrote: > > > I have a Query I am trying to Build that has the Following data: > > > > Name WO Days Old > > AD K-12345 10 > > AD K-12346 23 > > AD K-12368 02 > > AD K-12351 05 > > AD K-12386 20 > > > > What I am needing to is move the Days old Data to three Colums based off of > > the Days old > > > > Name WO 1-5 days 6-12 days 13+ Days > > AD K-12345 10 > > AD K-12346 23 > > AD K-12368 02 > > > > Something like that if it is possible. > > > > I have searched for this and could not find any other topics like this, Any > > help will be appreciated. Thanks for your time. > >
|
|
0 |
|
Utf
2/8/2010 4:14:01 PM
Thank You for your help Jerry. I used your First Query you showed and was able to get the info I needed. Here is what I used. SELECT WIPCLSIF.RESPAR, WIPCLSIF.WONO, WIPCLSIF.DAYS, IIf([WIPCLSIF.DAYS] Between 0 And 5,[Days]) AS [0-5 Days], IIf([WIPCLSIF.DAYS] Between 6 And 12,[Days]) AS [6-12 days], IIf([WIPCLSIF.DAYS] Between 13 And 999,[days]) AS [13+ Days] What does the Crosstab query you have listed Do? "Jerry Whittle" wrote: > Create a query something like below. The 01 and 06 are not mistakes. If you > leave them 1 and 6, you will have a sort problem across the columns. Put in > the proper table name. > > SELECT RobK.Name, > RobK.WO, > RobK.[Days Old], > IIf([Days Old] Between 1 And 5,"01-05 days",IIf([Days Old] Between 6 And > 10,"06-10 days","13+ days")) AS OldDays > FROM RobK; > > After getting the above query to run, create a crosstab query based upon it. > Make sure to change the "qryRobK" to the name of the query above: > > TRANSFORM Max(qryRobK.[Days Old]) AS [MaxOfDays Old] > SELECT qryRobK.[Name], qryRobK.[WO] > FROM qryRobK > GROUP BY qryRobK.[Name], qryRobK.[WO] > ORDER BY qryRobK.[Name], qryRobK.[WO] > PIVOT qryRobK.[OldDays]; > > Note: If the Days Old field is left null, zero, or a negative number, it > will show up in the 13+ days column. > -- > Jerry Whittle, Microsoft Access MVP > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > "Rob K" wrote: > > > I have a Query I am trying to Build that has the Following data: > > > > Name WO Days Old > > AD K-12345 10 > > AD K-12346 23 > > AD K-12368 02 > > AD K-12351 05 > > AD K-12386 20 > > > > What I am needing to is move the Days old Data to three Colums based off of > > the Days old > > > > Name WO 1-5 days 6-12 days 13+ Days > > AD K-12345 10 > > AD K-12346 23 > > AD K-12368 02 > > > > Something like that if it is possible. > > > > I have searched for this and could not find any other topics like this, Any > > help will be appreciated. Thanks for your time. > >
|
|
0 |
|
Utf
2/8/2010 4:33:01 PM
It does what you came up with, except dynamically. It could also count and sum the values as necessary. It could also handle values, such as negative numbers or > 1000 days cleanly. If what you have works, I'd stay with that. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "Rob K" wrote: > Thank You for your help Jerry. > > I used your First Query you showed and was able to get the info I needed. > Here is what I used. > > SELECT WIPCLSIF.RESPAR, WIPCLSIF.WONO, WIPCLSIF.DAYS, > IIf([WIPCLSIF.DAYS] Between 0 And 5,[Days]) AS [0-5 Days], > IIf([WIPCLSIF.DAYS] Between 6 And 12,[Days]) AS [6-12 days], > IIf([WIPCLSIF.DAYS] Between 13 And 999,[days]) AS [13+ Days] > > What does the Crosstab query you have listed Do? > > > "Jerry Whittle" wrote: > > > Create a query something like below. The 01 and 06 are not mistakes. If you > > leave them 1 and 6, you will have a sort problem across the columns. Put in > > the proper table name. > > > > SELECT RobK.Name, > > RobK.WO, > > RobK.[Days Old], > > IIf([Days Old] Between 1 And 5,"01-05 days",IIf([Days Old] Between 6 And > > 10,"06-10 days","13+ days")) AS OldDays > > FROM RobK; > > > > After getting the above query to run, create a crosstab query based upon it. > > Make sure to change the "qryRobK" to the name of the query above: > > > > TRANSFORM Max(qryRobK.[Days Old]) AS [MaxOfDays Old] > > SELECT qryRobK.[Name], qryRobK.[WO] > > FROM qryRobK > > GROUP BY qryRobK.[Name], qryRobK.[WO] > > ORDER BY qryRobK.[Name], qryRobK.[WO] > > PIVOT qryRobK.[OldDays]; > > > > Note: If the Days Old field is left null, zero, or a negative number, it > > will show up in the 13+ days column. > > -- > > Jerry Whittle, Microsoft Access MVP > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. > > > > > > "Rob K" wrote: > > > > > I have a Query I am trying to Build that has the Following data: > > > > > > Name WO Days Old > > > AD K-12345 10 > > > AD K-12346 23 > > > AD K-12368 02 > > > AD K-12351 05 > > > AD K-12386 20 > > > > > > What I am needing to is move the Days old Data to three Colums based off of > > > the Days old > > > > > > Name WO 1-5 days 6-12 days 13+ Days > > > AD K-12345 10 > > > AD K-12346 23 > > > AD K-12368 02 > > > > > > Something like that if it is possible. > > > > > > I have searched for this and could not find any other topics like this, Any > > > help will be appreciated. Thanks for your time. > > >
|
|
0 |
|
Utf
2/8/2010 4:41:01 PM
Similar Artilces:
change source data in multiple charts
Hi I have over 30 charts where I need to change the source data and it's taking ages doing it manually, especially as each chart has 3 or 4 series. Is there an easy way of doing this, please? I've tried find and replace, but that doesn;t work Thanks We need a little more detail. 1. Are you modifying all the charts to use the same new range or does each chart use a different new range? Is the data compact, that is, if you choose the chart wizard and look at the Data Range tab does the Data Range box contain a simple reference like =Sheet1!$B$2:$E$7? Thanks, Shane "Tuxla&...
automatically import data from a cell in one workbook to another workbook
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do arrange for workbook A to import the data from a cell in workbook B? In other words, what formula (address?) should I enter in a cell in workbook A so that it will import the data in a cell from workbook B? This would be just another formula; start with =+ and then just use the mouse/trackpad to click on the referenced cell (assuming you have both wordbooks open. Be careful of relative/absolute references. Thanks, Mungo <br><br>Cheers <br><br>Karshish 1) Make sure both workbooks ...
How do I 'spindle' or collect multiple documents in a single file
I used to be able to create chapters, so that discrete documents could be combined for global changes and unbundled for separate editing/use. Can this be done? Word has a master document function but it is notoriously unreliable and can result in data loss, so is best avoided. Word can work with very large single documents. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<>...
can I sort text data into bins?
Hi, Here's my problem - I hope you can understand it & help: Let's say I've got a column, A (or row 1, it doesn't matter) tha contains cells, each with a single word/category that I'm interested i [example: dog, cat, bird, fish, gerbil] - we can assume that this is m list of category headers/titles. Plus I've also got various other columns, A-D (or rows) next to m column A titles that each contain a list of items, one per cell: [eg B1=dog + B2=cat, C1=bird + C2=cat + C3=gerbil]. Perhaps it would be clearer if I drew it out (ignore the dashes they're just t...
Finding sheet one
A worksheet I need has dissapeared - no longer does 'Sheet one' show, only 2 & 3; how do I retrieve sheet one? try format>sheet>unhide -- Don Guillett SalesAid Software donaldb@281.com "Old Red One" <oldred1@charter.net> wrote in message news:OrZ6zHLzEHA.2200@TK2MSFTNGP09.phx.gbl... > A worksheet I need has dissapeared - no longer does 'Sheet one' show, only 2 > & 3; how do I retrieve sheet one? > > Format>Sheet>Unhide? Or maybe you deleted it, in which case it is gone if you have saved the workbook since. Gord Dibben ...
Format all sheets in one Workbook
I would like to add a little something to all the help everyone has offered on the formatting issue in Excel. If you would like to format all sheets including (Header, Footer, etc..) you need to make sure and use the "page setup" under "file" menu, because using the formatting from the print preview option will only make changes to the sheet you are currently viewing. I'm guessing but I think MS wanted to make sure you don't accidentally format all sheets thru print preview so they make you choose it from the file menu. It is the same thing as the repeat columns ...
Convert works database to excel
Is there a way to copy information from a Microsoft Works database to Microsoft Excel worksheet? ...
need to transpose 3 or 4 columns to one column
The matrix below represents what I need to do in Excel. I need to transpose the data from row 1 of C1, C2, C3 and C4 to rows 1-4 of C5 then repeat for each additional row of C1-C4. I think I might need a macro but I have no idea how to write it. Any help would be appreciated. My data set is too large to utilize cut and paste special. Thank you for your responses. Rufus C1 C2 C3 C4 C5 5 4 3 5 5 4 3 4 5 4 5 4 3 2 3 5 4 3 4 5 5 4 3 2 Rufus, If your example table is entered in cells A1:D4, enter this into cell E2, and copy down until it returns #REF ...
Data Validation #10
Hello, I am using Excel 2003 and am doing a drop down list using Data|Validation. However, I must have the data for my drop down list in the same sheet as my drop down list, otherwise the drop down lsit doesn't work. Am I doing something wrong? I used to be able to put the data in a different sheet. What happened? Valmont Valmont It's OK to have the list in another sheet, but then you must name it, e.g. "List1" (without quotes), and use that name in the validation box (Allow: List and enter =List1) -- Best Regards Leo Heuser Followup to newsgroup only please. &quo...
Counting how many records have writing in two columns.
I am trying to finish a report that is to include a current calculation of: # of total employees, # of hourly employees, # of salary employees, # of employees on leave, # of current employees (not on leave), and finall (the part I am having trouble with), # of salary employees on leave and current salary employees as well as current hourly employees and hourly employees on leave. I have four columns in my table and query I am linking from (hourly, salary, LOA (means they on on leave), and schedule (means they are current). For the first few calculations I just had it count the number of...
Displaying all data when using a combo box
Okay I have a form, based on one table. My form has two combo boxes on it, one searches for an employee by building, the other by department. The results are displayed in a subform beneath the combo boxes. This seems to work fine. However until you enter a selection into each combo box no records are displayed. How can I set each combo box to work independently and/or together to get the desired results? Is there a way I could add something like an "all" field to both? I would also like the subform to display all records when the form is open until a selection is made. ...
Multiple email accounts #14
How do I add additional email accounts so they go into their own folders with individual access to outlook 2003 on the same computer? If they're all POP3 accounts you've got to setup rules to move incoming mail messages based on the account they came in via "llbarnhill" <llbarnhill@discussions.microsoft.com> wrote in message news:CBC587E5-C70F-4658-9077-7CCA0BF17AC9@microsoft.com... > How do I add additional email accounts so they go into their own folders > with > individual access to outlook 2003 on the same computer? If you mean each individual sho...
View row and column headings
I have a sheet in a workbook that does not display row and column headings and the select all button. How do I re- display? Tools>Options>View tab>check the Row and Column Headers box>OK. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Rob" <anonymous@discussions.microsoft.com> wrote in message news:072b01c39939$984acb50$a501280a@phx.gbl... > I have a sheet in a workbook that does not display row and > column headings and the select all button. How do I re- > display? ...
Two sheets merged to one changing colour?
Hi If you had 2 or more spreadsheets giving the same info but in different areas, is it possible to merge all the info into one sheet? Week ending dates across the top, left hand side operation bein carried out, cells are coloured in a colour according to area, if it is possible, if an operation being carried out coincided wit another operation would the cells then divide in colour? Hope ive explained the question well tried to keep it simple. I have two small examples here which show in more detail what I mean if anyone is having trouble getting it. Frank...where are you my saviour : -...
How do I change font size in multiple text boxes?
I am working in Publisher, and I have dozens of text boxes that I want to change the font size, from say 10 to 8. Is there a way that I can select a number of them and change them in a batch-style? Use the Painter tool. Change the first text box, while your cursor is in the box, click the Painter, click the next text box and so on. Or open the font scheme dialogue, use or create a font scheme, click each text box with the text scheme highlighted. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JustinB" <JustinB@disc...
data turned into embedded picture
I somehow turned my data in a worksheet into a picture or embedded object that I cannot edit the cells. want to retrieve my data that has become a picture object ?? ...
enable a combo box in one form based on input in another form
I'm a novice programmer and was hoping that someone could help me. I have a combo box in form 1, and another combo box in form 2. Currently the combo box in form 2 is NOT enabled. This is what I'm trying to do: when the user selects "yes" in combo_box1 in form 1, I want the combo_box2 in form 2 to be enabled. I tried using a public variable to enable combo_box2 but as I move from one record to another, the public variable is not updated. If anyone has an answer or better method of how to activate combo_box2 (in form 2) based on the selection of combo_box1 (...
extact data
Dear All, I have 30 sheets, which contains the count of SMS of everyday, I need summary on last sheet that every sim consumed how many SMS. The count is in accending order so Sim no. will not be in order, Thanks & Regards TFMR Hassan;948620 Wrote: > Dear All, > > I have 30 sheets, which contains the count of SMS of everyday, I need > summary on last sheet that every sim consumed how many SMS. The count > is in > accending order so Sim no. will not be in order, > > Thanks & Regards > > TFMR Hi Hassan, There are two opt...
outlook just can start one time
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C360E9.B54C9F90 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable I am using win98 and outlook2002. I create a new profile for Outlook2002 = to connect to our Echange 2000 server. First time I can start = Outlook2002 and work fine. But after I close Outlook2002 and start it = again. I receive the following error message: Your logon information was incorrect. Check your username and domain, = then type your password again. If your account is new or if your = administrator r...
Excact row and column size
This is a multi-part message in MIME format. ------=_NextPart_000_00BC_01C42BA3.B7323100 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In Publisher 2002, using calendar templates, it is easy to resize with = the mouse. However, this is a course change. There are instances when = I need to change a row or column size to an exact amount. . The Format = Table>Size seems like it would be the place to make an exact setting, = but it is inoperative and I do not see any way of making an exact size = in the Table drop down menu. =20 Is ...
Subquery for column names
I have a database table with a large amount of analytical data in it - all floats but for a timestamp column. Instead of returning the entire set, I want to be able to return specific columns based on a "system" number that is specified as a parameter (in this case it is written in as "S02" for testing purposes) and compare the provided system number with the column names within the table - which is what the subquery currently does correctly. Aside from creating a stored procedure to handle the result and reformat it into a string to then use as a parameter of a ...
Convert Excel to Word
How can I convert an Excel Workbook with multiple sheets into a .doc format document? John, To my knowledge, you cannot perform a straight conversion from Excel to Word. However, there are two things that you can do. 1. Open Word, then within word, click File > Open > Under "files of type" click All. Find the Excel file and click. An option box will apear to ask if you want to open the entire workbook, or just one page. The result is messy at best 2. Open Word to a blank document, and copy the excel portion that you need and paste special it into word as an excel worksheet...
Changing data format
I have a program that sends a report to excel, however, instead of putting the information in coulmns, it places it all in one cell with spaces. Is the only way to get all this information into columns, cut & paste or is there an easier way? Try Data>Text to columns in the menu bar -- Regards Ron de Bruin http://www.rondebruin.nl "frustratedwthis" <frustratedwthis@discussions.microsoft.com> wrote in message news:4B77786C-ABB9-43AF-8841-BA9D43902B83@microsoft.com... >I have a program that sends a report to excel, however, instead of putting > the informat...
Column Number as parameter in Query to select data
Hi is it possible that we can give Column Number as a parameter to select Records at Runtime in access table . eg, Table having structure like this, Where Qtr1..Qtr4 Stores Amt Paid by customer, If runtime i provide Column name to quaery it should return Cust with specified quarter like "Select Cust,<Column No> from tbl" , Where Column No is the parameter Cust,Qtr1,Qtr2,Qtr3,Qtr4 X-10000-20000-30000-4000 I know if the data table is normalized like this Cust,Qtr-Sales X-Q1-1000 X-Q2-2000 X-Q3-3000 X-Q4-4000 it is quite easy to select , but my proble is , presently i am...
have a subreport get data related to a date on main report
I have a subreport that I want to pull up data 180 days before the date on the report. Any ideas on if and how this can be accomplished? Please and thank you. Pat wrote: >I have a subreport that I want to pull up data 180 days before the >date on the report. Any ideas on if and how this can be accomplished? Try using a filter/criteria for your date field like: <= DateAdd("d", -180, Date()) -- Marsh MVP [MS Access] I tried that previously but my boss didn't want 180 days from the day they run the report he wants 180 days from the closing date on the main repor...
Hi I have over 30 charts where I need to change the source data and it's taking ages doing it manually, especially as each chart has 3 or 4 series. Is there an easy way of doing this, please? I've tried find and replace, but that doesn;t work Thanks We need a little more detail. 1. Are you modifying all the charts to use the same new range or does each chart use a different new range? Is the data compact, that is, if you choose the chart wizard and look at the Data Range tab does the Data Range box contain a simple reference like =Sheet1!$B$2:$E$7? Thanks, Shane "Tuxla&...
automatically import data from a cell in one workbook to another workbook
Version: 2008 Operating System: Mac OS X 10.5 (Leopard) Processor: Intel How do arrange for workbook A to import the data from a cell in workbook B? In other words, what formula (address?) should I enter in a cell in workbook A so that it will import the data in a cell from workbook B? This would be just another formula; start with =+ and then just use the mouse/trackpad to click on the referenced cell (assuming you have both wordbooks open. Be careful of relative/absolute references. Thanks, Mungo <br><br>Cheers <br><br>Karshish 1) Make sure both workbooks ...
How do I 'spindle' or collect multiple documents in a single file
I used to be able to create chapters, so that discrete documents could be combined for global changes and unbundled for separate editing/use. Can this be done? Word has a master document function but it is notoriously unreliable and can result in data loss, so is best avoided. Word can work with very large single documents. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<>...
can I sort text data into bins?
Hi, Here's my problem - I hope you can understand it & help: Let's say I've got a column, A (or row 1, it doesn't matter) tha contains cells, each with a single word/category that I'm interested i [example: dog, cat, bird, fish, gerbil] - we can assume that this is m list of category headers/titles. Plus I've also got various other columns, A-D (or rows) next to m column A titles that each contain a list of items, one per cell: [eg B1=dog + B2=cat, C1=bird + C2=cat + C3=gerbil]. Perhaps it would be clearer if I drew it out (ignore the dashes they're just t...
Finding sheet one
A worksheet I need has dissapeared - no longer does 'Sheet one' show, only 2 & 3; how do I retrieve sheet one? try format>sheet>unhide -- Don Guillett SalesAid Software donaldb@281.com "Old Red One" <oldred1@charter.net> wrote in message news:OrZ6zHLzEHA.2200@TK2MSFTNGP09.phx.gbl... > A worksheet I need has dissapeared - no longer does 'Sheet one' show, only 2 > & 3; how do I retrieve sheet one? > > Format>Sheet>Unhide? Or maybe you deleted it, in which case it is gone if you have saved the workbook since. Gord Dibben ...
Format all sheets in one Workbook
I would like to add a little something to all the help everyone has offered on the formatting issue in Excel. If you would like to format all sheets including (Header, Footer, etc..) you need to make sure and use the "page setup" under "file" menu, because using the formatting from the print preview option will only make changes to the sheet you are currently viewing. I'm guessing but I think MS wanted to make sure you don't accidentally format all sheets thru print preview so they make you choose it from the file menu. It is the same thing as the repeat columns ...
Convert works database to excel
Is there a way to copy information from a Microsoft Works database to Microsoft Excel worksheet? ...
need to transpose 3 or 4 columns to one column
The matrix below represents what I need to do in Excel. I need to transpose the data from row 1 of C1, C2, C3 and C4 to rows 1-4 of C5 then repeat for each additional row of C1-C4. I think I might need a macro but I have no idea how to write it. Any help would be appreciated. My data set is too large to utilize cut and paste special. Thank you for your responses. Rufus C1 C2 C3 C4 C5 5 4 3 5 5 4 3 4 5 4 5 4 3 2 3 5 4 3 4 5 5 4 3 2 Rufus, If your example table is entered in cells A1:D4, enter this into cell E2, and copy down until it returns #REF ...
Data Validation #10
Hello, I am using Excel 2003 and am doing a drop down list using Data|Validation. However, I must have the data for my drop down list in the same sheet as my drop down list, otherwise the drop down lsit doesn't work. Am I doing something wrong? I used to be able to put the data in a different sheet. What happened? Valmont Valmont It's OK to have the list in another sheet, but then you must name it, e.g. "List1" (without quotes), and use that name in the validation box (Allow: List and enter =List1) -- Best Regards Leo Heuser Followup to newsgroup only please. &quo...
Counting how many records have writing in two columns.
I am trying to finish a report that is to include a current calculation of: # of total employees, # of hourly employees, # of salary employees, # of employees on leave, # of current employees (not on leave), and finall (the part I am having trouble with), # of salary employees on leave and current salary employees as well as current hourly employees and hourly employees on leave. I have four columns in my table and query I am linking from (hourly, salary, LOA (means they on on leave), and schedule (means they are current). For the first few calculations I just had it count the number of...
Displaying all data when using a combo box
Okay I have a form, based on one table. My form has two combo boxes on it, one searches for an employee by building, the other by department. The results are displayed in a subform beneath the combo boxes. This seems to work fine. However until you enter a selection into each combo box no records are displayed. How can I set each combo box to work independently and/or together to get the desired results? Is there a way I could add something like an "all" field to both? I would also like the subform to display all records when the form is open until a selection is made. ...
Multiple email accounts #14
How do I add additional email accounts so they go into their own folders with individual access to outlook 2003 on the same computer? If they're all POP3 accounts you've got to setup rules to move incoming mail messages based on the account they came in via "llbarnhill" <llbarnhill@discussions.microsoft.com> wrote in message news:CBC587E5-C70F-4658-9077-7CCA0BF17AC9@microsoft.com... > How do I add additional email accounts so they go into their own folders > with > individual access to outlook 2003 on the same computer? If you mean each individual sho...
View row and column headings
I have a sheet in a workbook that does not display row and column headings and the select all button. How do I re- display? Tools>Options>View tab>check the Row and Column Headers box>OK. -- Greeting from the Gulf Coast! http://myweb.cableone.net/twodays "Rob" <anonymous@discussions.microsoft.com> wrote in message news:072b01c39939$984acb50$a501280a@phx.gbl... > I have a sheet in a workbook that does not display row and > column headings and the select all button. How do I re- > display? ...
Two sheets merged to one changing colour?
Hi If you had 2 or more spreadsheets giving the same info but in different areas, is it possible to merge all the info into one sheet? Week ending dates across the top, left hand side operation bein carried out, cells are coloured in a colour according to area, if it is possible, if an operation being carried out coincided wit another operation would the cells then divide in colour? Hope ive explained the question well tried to keep it simple. I have two small examples here which show in more detail what I mean if anyone is having trouble getting it. Frank...where are you my saviour : -...
How do I change font size in multiple text boxes?
I am working in Publisher, and I have dozens of text boxes that I want to change the font size, from say 10 to 8. Is there a way that I can select a number of them and change them in a batch-style? Use the Painter tool. Change the first text box, while your cursor is in the box, click the Painter, click the next text box and so on. Or open the font scheme dialogue, use or create a font scheme, click each text box with the text scheme highlighted. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "JustinB" <JustinB@disc...
data turned into embedded picture
I somehow turned my data in a worksheet into a picture or embedded object that I cannot edit the cells. want to retrieve my data that has become a picture object ?? ...
enable a combo box in one form based on input in another form
I'm a novice programmer and was hoping that someone could help me. I have a combo box in form 1, and another combo box in form 2. Currently the combo box in form 2 is NOT enabled. This is what I'm trying to do: when the user selects "yes" in combo_box1 in form 1, I want the combo_box2 in form 2 to be enabled. I tried using a public variable to enable combo_box2 but as I move from one record to another, the public variable is not updated. If anyone has an answer or better method of how to activate combo_box2 (in form 2) based on the selection of combo_box1 (...
extact data
Dear All, I have 30 sheets, which contains the count of SMS of everyday, I need summary on last sheet that every sim consumed how many SMS. The count is in accending order so Sim no. will not be in order, Thanks & Regards TFMR Hassan;948620 Wrote: > Dear All, > > I have 30 sheets, which contains the count of SMS of everyday, I need > summary on last sheet that every sim consumed how many SMS. The count > is in > accending order so Sim no. will not be in order, > > Thanks & Regards > > TFMR Hi Hassan, There are two opt...
outlook just can start one time
This is a multi-part message in MIME format. ------=_NextPart_000_0008_01C360E9.B54C9F90 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable I am using win98 and outlook2002. I create a new profile for Outlook2002 = to connect to our Echange 2000 server. First time I can start = Outlook2002 and work fine. But after I close Outlook2002 and start it = again. I receive the following error message: Your logon information was incorrect. Check your username and domain, = then type your password again. If your account is new or if your = administrator r...
Excact row and column size
This is a multi-part message in MIME format. ------=_NextPart_000_00BC_01C42BA3.B7323100 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable In Publisher 2002, using calendar templates, it is easy to resize with = the mouse. However, this is a course change. There are instances when = I need to change a row or column size to an exact amount. . The Format = Table>Size seems like it would be the place to make an exact setting, = but it is inoperative and I do not see any way of making an exact size = in the Table drop down menu. =20 Is ...
Subquery for column names
I have a database table with a large amount of analytical data in it - all floats but for a timestamp column. Instead of returning the entire set, I want to be able to return specific columns based on a "system" number that is specified as a parameter (in this case it is written in as "S02" for testing purposes) and compare the provided system number with the column names within the table - which is what the subquery currently does correctly. Aside from creating a stored procedure to handle the result and reformat it into a string to then use as a parameter of a ...
Convert Excel to Word
How can I convert an Excel Workbook with multiple sheets into a .doc format document? John, To my knowledge, you cannot perform a straight conversion from Excel to Word. However, there are two things that you can do. 1. Open Word, then within word, click File > Open > Under "files of type" click All. Find the Excel file and click. An option box will apear to ask if you want to open the entire workbook, or just one page. The result is messy at best 2. Open Word to a blank document, and copy the excel portion that you need and paste special it into word as an excel worksheet...
Changing data format
I have a program that sends a report to excel, however, instead of putting the information in coulmns, it places it all in one cell with spaces. Is the only way to get all this information into columns, cut & paste or is there an easier way? Try Data>Text to columns in the menu bar -- Regards Ron de Bruin http://www.rondebruin.nl "frustratedwthis" <frustratedwthis@discussions.microsoft.com> wrote in message news:4B77786C-ABB9-43AF-8841-BA9D43902B83@microsoft.com... >I have a program that sends a report to excel, however, instead of putting > the informat...
Column Number as parameter in Query to select data
Hi is it possible that we can give Column Number as a parameter to select Records at Runtime in access table . eg, Table having structure like this, Where Qtr1..Qtr4 Stores Amt Paid by customer, If runtime i provide Column name to quaery it should return Cust with specified quarter like "Select Cust,<Column No> from tbl" , Where Column No is the parameter Cust,Qtr1,Qtr2,Qtr3,Qtr4 X-10000-20000-30000-4000 I know if the data table is normalized like this Cust,Qtr-Sales X-Q1-1000 X-Q2-2000 X-Q3-3000 X-Q4-4000 it is quite easy to select , but my proble is , presently i am...
have a subreport get data related to a date on main report
I have a subreport that I want to pull up data 180 days before the date on the report. Any ideas on if and how this can be accomplished? Please and thank you. Pat wrote: >I have a subreport that I want to pull up data 180 days before the >date on the report. Any ideas on if and how this can be accomplished? Try using a filter/criteria for your date field like: <= DateAdd("d", -180, Date()) -- Marsh MVP [MS Access] I tried that previously but my boss didn't want 180 days from the day they run the report he wants 180 days from the closing date on the main repor...