Sorting Data in a column
I am trying to extract data that is in a single column to split it into 3 columns. The data is cyclic and has 3 variables, a time, a data point and a random number. These simply run abcabcabcabc down the column and i would like to get all of the a's into a column, all of the b's into another column and all of the c's into a third column. any ideas?
BigAndy
excel.misc
78881 articles. 
5 followers.
1 Replies
313 Views
VBA?
Sub Test()
Dim iLastRow As Long
Dim i As Long
iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
If iLastRow Mod 3 <> 1 Then iLastRow = iLastRow - 1
For i = iLastRow To 1 Step -3
Cells(i, "C").Value = Cells(i + 2, "A").Value
Cells(i, "B").Value = Cells(i + 1, "A").Value
Rows(i + 1).Resize(2).Delete
Next i
End Sub
--
HTH
Bob Phillips
"BigAndy" <BigAndy@discussions.microsoft.com> wrote in message
news:BB5B5F23-8305-41A9-BA5E-3AE4C7130F5F@microsoft.com...
> I am trying to extract data that is in a single column to split it into 3
> columns. The data is cyclic and has 3 variables, a time, a data point and
a
> random number. These simply run abcabcabcabc down the column and i would
> like to get all of the a's into a column, all of the b's into another
column
> and all of the c's into a third column.
>
> any ideas?
bob.phillips1
Similar Artilces:
splitting one column into two columns ... not what you think
i have fixed column widths that can't be changed; nor can any other columns be added to the worksheet ... i've got data in one column that represents "results" ... within the results column though, i need two columns (starting directly below the "results" cell, one that reads "in range" and that other that reads "out of range" ... so: if i'm on [column a] [cell 1] i want: "results" ... in [column a] [cell 2] i want: "in range" AND "out of range" with a line down the middle. "text to column" is ...
Finding duplicates in column
Hi, I have Excel 2002 and have 6000 emails addresses in a column. How can I find if there are any duplicates in that column? Thanks rock Assuming the data is in column A, add a formula in column B of =IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","") Cpy that down, then you can filter column B for Duplicate Sorry to be so newby Bob, but when you say 'copy' down, what exactly do you mean? I have entered the formula in B1 but.. Thanks rock Bob Phillips wrote: > Assuming the data is in column A, add a formula in column B of > > =IF(COUNTI...
Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...
data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...
Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...
How can I choose alternate rows in a column?
My requirement is to be able to create a column whose elements consist of difference between adjacent elements in a column, say column A. If I can choose alternate elements and create 2 new columns then I can just subtract the 2 columns easily. Huh? "pnair" <pnair@discussions.microsoft.com> wrote in message news:D47AD012-084B-49C6-8672-5067E8455D9E@microsoft.com... > My requirement is to be able to create a column whose elements consist of > difference between adjacent elements in a column, say column A. If I can > choose alternate elements and create 2 new colum...
Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...
how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...
Column styles doesn't appear on the worksheet
Hello, one of my customers has changed the windows xp designs to its own. From this moment excel doesn't show the font color and the background color of a cell in the worksheet. Only in the print preview you can see the color settings of the cells. I'm not shure if this problem belongs to the changing of the windows xp designs, but from this moment it did occur. This occurs on new excel-documents and on existing ones. With another user account on the same machine the problem doesn't occur. I have reinstalled Office 2003 and even deleted user registry entries for Office...
How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...
how do i recover data in publisher
i have been entering addresses to set up a mail merge. i cllicked the "ok" button in the window and lost all data . can i recover it Look in a folder in My Documents named "My Data Sources". Publisher data is saved as .mdb(Access) file. Did you try selecting "Edit Address List" in the Mailings and Catalog menu (Tools)? -- Mary Sauer http://msauer.mvps.org/ "dee" <dee@discussions.microsoft.com> wrote in message news:690430F1-36DE-47EE-8B7D-DD12A096C075@microsoft.com... >i have been entering addresses to set up a mail merge. i cllicked ...
Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...
Hide columns if there are no entry's in column
Hi everyone, I have a workbook with multiple sheets. One sheet is a overview from all the sheets and had all dates in it. Is there a VBA to hide columns when there are no entry's in it? The code has to work when I open the sheet "overview" Hope someone can help me with it! Thanks in advanced! Regards Berry Berry, If you have a row that when blank would indicate which columns to hide, you could use On Error Resume Next Rows("1:1").SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True HTH, Bernie MS Excel MVP <blommerse@saz.nl> wrote in message news:118...
How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...
Missing column letters
Hello there, I have lost the letters at the top of my columns and the numbers to the left. I don't know what I have clicked! Any help is appreciated! Julia Hi Julia tools / options / view tab ensure row & column headers are ticked cheers JulieD "Julia" <anonymous@discussions.microsoft.com> wrote in message news:2f4601c51f29$af4e6d80$a401280a@phx.gbl... > Hello there, > > I have lost the letters at the top of my columns and the > numbers to the left. > > I don't know what I have clicked! Any help is appreciated! > > Julia Julia, ...
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...
Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...
Import directory data into Excel 2003
I have over 1000 media files that I would like to extract information from and put into an Excel spreadsheet. Using Explorer, I have defined the fields I would like to see, such as title, duration, comment etc. Now, I need to import this data into Excel. So far, I've not been able to find a way to do this. Can someone offer some suggestions please? Thanks, Nigel -- www.myoldcontacts.com - Tell your friends to tell their friends www.sysadmininc.com - Consultancy, Service, Sales, Networking... www.british-expats.com - Connect with British Expats World Wide www.kxez.com/shows_britishinv...
show last data point in chart
Hello, I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? Thank you. Nathan - > I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? < Click the charted data once to select the entire data series. Pause. Click the single point to select it. Then use the Format menu. - Mike www.mikemiddleton.com Thanks for your reply. Well, that would work if I knew which point on the chart ...
Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...
Dead space at end of column
I am working on a multi-paged document that is legal-sized, landscape orientation with three columns on each page. At the end of page two, I have a recurring problem with the cursor jumping to page 3 about two-thirds of the way down the column. I cannot type in that area. Displaying formatting characters reveals nothing to show that there is a problem there. This does not happen in any other location on the document. Check the jumping paragraphs to see if many of them are formatted as "Keep with next" and "Keep lines together." Or perhaps one as "Page b...
Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...
Import excel data to outlook calendar
I have found lots of tips to import excel data to the address book, etc, but can't find how to "custom map" or how to import data from an excel spreadsheet into the outlook calendar. Could anyone make any suggestions? Hi Tracy, normally you are in the wrong newsgoup, but I try to help you. - First export the dates from your OL calender to an excel file. - In this file, you can find all the headlines for importing. - If you try to import date, be sure that the headline matches as described before. - Then to the normal job for import in Outlook -- Ich hoffe, das hilft / ...
How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...
Registration Entry for External Data Refresh Prompt
Hello, I have several Excel Workbooks with external queries, pivots, etc. I have "ASK TO UPDATE AUTOMATIC LINKS" checked in TOOLS - OPTIONS. But it seems like I still stometimes get asked whether or not I want to update. Particularily I notice when I close the workbook I may get prompted if I want it to automatically update. Is there something I can do so I do not get prompted? Something in the registry perhaps? Thanks for any assistance! ...
i have fixed column widths that can't be changed; nor can any other columns be added to the worksheet ... i've got data in one column that represents "results" ... within the results column though, i need two columns (starting directly below the "results" cell, one that reads "in range" and that other that reads "out of range" ... so: if i'm on [column a] [cell 1] i want: "results" ... in [column a] [cell 2] i want: "in range" AND "out of range" with a line down the middle. "text to column" is ...
Finding duplicates in column
Hi, I have Excel 2002 and have 6000 emails addresses in a column. How can I find if there are any duplicates in that column? Thanks rock Assuming the data is in column A, add a formula in column B of =IF(COUNTIF($A$1:$A1,$A1)>1,"Duplicate","") Cpy that down, then you can filter column B for Duplicate Sorry to be so newby Bob, but when you say 'copy' down, what exactly do you mean? I have entered the formula in B1 but.. Thanks rock Bob Phillips wrote: > Assuming the data is in column A, add a formula in column B of > > =IF(COUNTI...
Passing data from one form to another
Hello I have a form called frmMaindB and it has 5 text boxes on it (txtEmployeeTime, txtDTRegular, txtDTReason1, txtDTReason2, txtDTMaintenance) when I double click on the text box it opens up a pop up form named frm_DecimalConversion. On this form I have two text boxes one box I enter data into and the other calculates or converts the data to a decimal. The box that converts the data is called txtDecimal. Then I have a close button which I want to use to close the pop up form and insert the data into the text box I double clicked in to get the pop up or (frm_DecimalConversion). I have r...
data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...
Help ! formatting data to text
I am creating data in an Excel spreadsheet. I then want to get that data into a simple text email. I have some problems and questions... 1) how do I get the columns of data to line up evenly when I copy the data to email text ? Keep in mind I need to be in simple text format, not HTML or rich text. Every time I do this, all columns become chaos and are unreadable. 2) Is there a simple way to automate the creation of an email from an excel file ? this is less important to me. Thanks in advance WxMachine #1. I think it may have to do with what email client you use, too. I copy and ...
How can I choose alternate rows in a column?
My requirement is to be able to create a column whose elements consist of difference between adjacent elements in a column, say column A. If I can choose alternate elements and create 2 new columns then I can just subtract the 2 columns easily. Huh? "pnair" <pnair@discussions.microsoft.com> wrote in message news:D47AD012-084B-49C6-8672-5067E8455D9E@microsoft.com... > My requirement is to be able to create a column whose elements consist of > difference between adjacent elements in a column, say column A. If I can > choose alternate elements and create 2 new colum...
Assign values for one column to another.
Hi I have in column T certain numbers and texts that that I require to assign a value to as below, in the adjacent column. Again any pointers would be much appreciated. Kind Regards Celticshadow T U 1 1 2 2 3 3 4 4 5 5 6 6 7 7 8 8 9 9 0 10 F 10 UR 10 U 10 R 10 S 10 L 10 P 10 PU 10 BD 10 D 10 Well, imagine that two-column table occupies cells Y1:Z20. Put this formula in U1: =3DVLOOKUP(T1,Y$1,Z$20,2,0) and copy down. Hope this helps. Pete On Oct 14, 4:26=A0pm, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > >...
how do I remove fx from the function line, can't enter data
I have the fx displayed just under my toolbar, and I can't enter or change data in any of the cells in the file. I can't get the red X, the Green check mark, or the black = sign to appear. There are very few areas that are not "greyed out" under the headings at the top. This situation applies to all of the excel files on this computer. I have Excel 2000. Please help. Can you move the cursor around anywhere in the spreadsheet? "dmdranch" wrote: > I have the fx displayed just under my toolbar, and I can't enter or change > data in any of the c...
Column styles doesn't appear on the worksheet
Hello, one of my customers has changed the windows xp designs to its own. From this moment excel doesn't show the font color and the background color of a cell in the worksheet. Only in the print preview you can see the color settings of the cells. I'm not shure if this problem belongs to the changing of the windows xp designs, but from this moment it did occur. This occurs on new excel-documents and on existing ones. With another user account on the same machine the problem doesn't occur. I have reinstalled Office 2003 and even deleted user registry entries for Office...
How can I stop charts from refreshing when changing source data?
My problem is, that I am working with a lot of data and when I change some of the ranges all charts in my view refreshes and it takes much time. My pc is aP4 3GHz, 2GB RAM so that should not be the bottleneck. Is there any way to force the charts not to update all the time? ...
how do i recover data in publisher
i have been entering addresses to set up a mail merge. i cllicked the "ok" button in the window and lost all data . can i recover it Look in a folder in My Documents named "My Data Sources". Publisher data is saved as .mdb(Access) file. Did you try selecting "Edit Address List" in the Mailings and Catalog menu (Tools)? -- Mary Sauer http://msauer.mvps.org/ "dee" <dee@discussions.microsoft.com> wrote in message news:690430F1-36DE-47EE-8B7D-DD12A096C075@microsoft.com... >i have been entering addresses to set up a mail merge. i cllicked ...
Using subtotals as single data entries
Sorry about the subject--I couldn't figure out how to describe it simply. I have a large file (16,000 records) of amounts billed by roughly 10,000 service providers. A number of these providers have multiple office locations, so each record is unique to a specific office location. In other words, a provider who billed from 3 different office locations will have 3 entries. Each provider has a unique provider ID number, which stays the same regardless of which office location he is billing from. I want to be able to subtotal the amount billed by each provider for all their office locations...
Hide columns if there are no entry's in column
Hi everyone, I have a workbook with multiple sheets. One sheet is a overview from all the sheets and had all dates in it. Is there a VBA to hide columns when there are no entry's in it? The code has to work when I open the sheet "overview" Hope someone can help me with it! Thanks in advanced! Regards Berry Berry, If you have a row that when blank would indicate which columns to hide, you could use On Error Resume Next Rows("1:1").SpecialCells(xlCellTypeBlanks).EntireColumn.Hidden = True HTH, Bernie MS Excel MVP <blommerse@saz.nl> wrote in message news:118...
How can I keep track of when (date and time) data is entered into.
I am trying to create a spreadsheet for a high school class. I need to be able to track when a student has entered data into specific cells of the spreadsheet. Any ideas? In the code behind the worksheet, enter (eg) Private Sub Worksheet_Change(ByVal Target As Range) Cells(1, 1).Value = Now() End Sub This will enter in Cell A1 the date and time at which any entry is made in that worksheet. If you need the location of the time-stamp to vary according to which cell is changed then you can test the value of Target and vary the destination cell accordingly. -- Return email address is n...
Missing column letters
Hello there, I have lost the letters at the top of my columns and the numbers to the left. I don't know what I have clicked! Any help is appreciated! Julia Hi Julia tools / options / view tab ensure row & column headers are ticked cheers JulieD "Julia" <anonymous@discussions.microsoft.com> wrote in message news:2f4601c51f29$af4e6d80$a401280a@phx.gbl... > Hello there, > > I have lost the letters at the top of my columns and the > numbers to the left. > > I don't know what I have clicked! Any help is appreciated! > > Julia Julia, ...
How can I cut data out of HTML table, into msExcel and just take the data & columns? (but NOT the formatting & URLs!)
Hi This is driving me ABSOLUTELY NUTS! How can I keep the rows & columns of data that I am copying and pasting off a website (my own in this case!), into a spreadsheet... WITHOUT taking all the data formatting? If I paste out of Ms IE v6 into Ms Excel (2003), it does at least keep the columns (something that doesnt happen if I paste out of FireFox, fwiw). But it pastes with all the formatting & URLs etc - which I DONT WANT! OK, I can save as .CSV, close, 2 warnings, and re-open but when done REPEATEDLY this is a damned nuicance! Any suggestions? Ship Shiperton Henethe ship w...
Retrieving sorted data from same table.
Hi All, I am working on a table (mentioned below) I am looking for a query which can get me the data according to the =93id=94 column with respect to speed. The condition is that I have to get three consecutive entries which have speed > 60 Below is the sample table with data on which I have to retrieve the data on above condition. The output i need can be as given below DVXC002 12/10/09 0:12 96 DVXC002 12/10/09 18:40 89 DVXC002 12/10/09 19:43 65 DVXC005 12/10/09 11:56 69 DVXC005 12/10/09 15:26 62 DVXC005 12/10/09 17:35 85 Need your help urgently....Thanks in advan...
Import directory data into Excel 2003
I have over 1000 media files that I would like to extract information from and put into an Excel spreadsheet. Using Explorer, I have defined the fields I would like to see, such as title, duration, comment etc. Now, I need to import this data into Excel. So far, I've not been able to find a way to do this. Can someone offer some suggestions please? Thanks, Nigel -- www.myoldcontacts.com - Tell your friends to tell their friends www.sysadmininc.com - Consultancy, Service, Sales, Networking... www.british-expats.com - Connect with British Expats World Wide www.kxez.com/shows_britishinv...
show last data point in chart
Hello, I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? Thank you. Nathan - > I am charting a range of observations/data points. Is there a way to make the last data point show up differently on the chart (different color/shape)? < Click the charted data once to select the entire data series. Pause. Click the single point to select it. Then use the Format menu. - Mike www.mikemiddleton.com Thanks for your reply. Well, that would work if I knew which point on the chart ...
Copy Data from One Group of Cells to Another Group
I have five columns of data on two different sheets in the same workbook. One set of columns is sorted in ascending date order the other in descending date order. When I enter data into the last row of Sheet 1, I need the data in that row in columns A, B, C and D to be copied into Sheet 2 columns A, C, D and E in a newly inserted row 14. Is this possible with the use of a macro? I can find the last cell in Sheet 1, but then need to go up one row and back to column A. I am having difficulty with that. Thanks is advance for any assistance offered! /s/ Alan Auerbach On Sat, 26 May 2007, ...
Dead space at end of column
I am working on a multi-paged document that is legal-sized, landscape orientation with three columns on each page. At the end of page two, I have a recurring problem with the cursor jumping to page 3 about two-thirds of the way down the column. I cannot type in that area. Displaying formatting characters reveals nothing to show that there is a problem there. This does not happen in any other location on the document. Check the jumping paragraphs to see if many of them are formatted as "Keep with next" and "Keep lines together." Or perhaps one as "Page b...
Text in column causing SUMPRODUCT error
Greegings. I have a SUMPRODUCT formula that is having errors when one of the columns has text instead of a NULL or a number. If I delete the text cells in that column it works as desired. I'll give a simple example. Suppose I have the following in A1:B6.... a 1 a 2 a abc b 1 b 1 a 2 And I need this... =SUMPRODUCT(($A$1:$A$6="a")*($B$1:$B$6)) It errors out until I delete the "abc" in cell B3, then it works as desired. I tried to replace the "abc" with a 0 by trying this... =IF(ISNUMBER(B3)=FALSE,0,B3) And it works for that pa...
Import excel data to outlook calendar
I have found lots of tips to import excel data to the address book, etc, but can't find how to "custom map" or how to import data from an excel spreadsheet into the outlook calendar. Could anyone make any suggestions? Hi Tracy, normally you are in the wrong newsgoup, but I try to help you. - First export the dates from your OL calender to an excel file. - In this file, you can find all the headlines for importing. - If you try to import date, be sure that the headline matches as described before. - Then to the normal job for import in Outlook -- Ich hoffe, das hilft / ...
How do I get total value data labels in a stacked bar chart?
I have a 3-D stacked bar chart with four series and I want to have the total value in each category be displayed in a data label. Can I do this, and if so, how?? Hi, This should help http://www.andypope.info/charts/StackColTotal.htm Cheers Andy blemerson wrote: > I have a 3-D stacked bar chart with four series and I want to have the total > value in each category be displayed in a data label. Can I do this, and if > so, how?? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Thanks, Andy. The CFO is thrilled. Heather "Andy Pope" wrote: > Hi, >...
Registration Entry for External Data Refresh Prompt
Hello, I have several Excel Workbooks with external queries, pivots, etc. I have "ASK TO UPDATE AUTOMATIC LINKS" checked in TOOLS - OPTIONS. But it seems like I still stometimes get asked whether or not I want to update. Particularily I notice when I close the workbook I may get prompted if I want it to automatically update. Is there something I can do so I do not get prompted? Something in the registry perhaps? Thanks for any assistance! ...