How do you separate multiple lines from Excel into Access
Hello, I am importing data from an excel spreadsheet, which is fielded this way: Parcel_ID, Owner_First_Name, Owner_Last_Name, etc... The first column, the Parcel ID, could look like this: Parcel_ID 7-5-029-001 But sometimes, because the SAME owner may have more than one parcel (under the SAME name), some of the fields can have up to three entries in the same cell, which are separated by a new line. Which could look like this: Parcel_ID 7-5-030-001 7-5-031-001 7-5-032-001 When I bring in the spreadsheet into Access, using File/Get External Data/Import, the access table NOW has all three Parcel_ID's in the same field, separated by the little square (which is the new line character, I believe), which I will use a ^ to substitute it for this discussion. The new access field now looks like this: 7-5-030-001^7-5-031-001^7-5-032-001 HERE IS MY QUESTION: Using a query (I guess), I would need to get Access to read the Parcel_ID, find the separator, extract the next number series, then make a new record, with the SAME Owner_First_Name, Owner_Last_Name, etc..., with the different Parcel_ID? Thanks in advance for your reply (ies). Phil. <If you want to respond directly just remove the caps from the email address shown>
|
0 |
|
CAD
6/21/2005 5:36:40 PM
access.conversion
3037 articles. 
0 followers.
3 Replies
638 Views
Hi Phil,
I'd do it as follows:
1) import or link the data as is to a temporary table, so you have up to
three entries in the Parcel_ID field.
2) build a union query that calls the ParseItems() function below I've
pasted at the end of this message to extract individual items from
Parcel_ID. It will look something like this and should deliver one
record per parcelID per owner:
SELECT
ParseItems(Parcel_ID, 0, Chr(10)) AS fParcel_ID,
Owner_First_Name, Owner_Last_Name, ...
FROM TempTable
UNION
SELECT
ParseItems(Parcel_ID, 1, Chr(10)) AS fParcel_ID,
Owner_First_Name, Owner_Last_Name, ...
FROM TempTable
UNION
SELECT
ParseItems(Parcel_ID, 2, Chr(10)) AS fParcel_ID,
Owner_First_Name, Owner_Last_Name, ...
FROM TempTable
WHERE fParcel_ID IS NOT NULL
;
3) create a new, empty table with the fields you need.
4) When the union query is working properly, use it as the source of an
append query to move the data into the new table.
'CODE STARTS ----------------------
Public Function ParseItems( _
List As Variant, _
Item As Long, _
Optional Separator As String = " " _
) As Variant
'Returns the specified item from a list of "words" separated
'by a space (or items separated by Separator).
'Counting starts at zero.
'Returns Null if the list is too short.
'By John Nurick, 2004
Dim arWords As Variant
If IsNull(List) Then
ParseItems = Null
Exit Function
End If
arWords = Split(CStr(List), " ", Item + 2)
If UBound(arWords) < Item Then
ParseItems = Null
Else
ParseItems = arWords(Item)
End If
End Function
'CODE ENDS-----------------------------
On Tue, 21 Jun 2005 10:36:40 -0700, CAD Fiend
<pelampeREMOVETHECAPS@cox.net> wrote:
>Hello,
>
>I am importing data from an excel spreadsheet, which is fielded this
>way:
>
>Parcel_ID, Owner_First_Name, Owner_Last_Name, etc...
>
>The first column, the Parcel ID, could look like this:
>
>Parcel_ID
>7-5-029-001
>
>But sometimes, because the SAME owner may have more than one parcel
>(under the SAME name), some of the fields can have up to three entries
>in the same cell, which are separated by a new line.
>
>Which could look like this:
>
>Parcel_ID
>7-5-030-001
>7-5-031-001
>7-5-032-001
>
>When I bring in the spreadsheet into Access, using File/Get External
>Data/Import, the access table NOW has all three Parcel_ID's in the same
>field, separated by the little square (which is the new line character,
>I believe), which I will use a ^ to substitute it for this discussion.
>
>The new access field now looks like this:
>
>7-5-030-001^7-5-031-001^7-5-032-001
>
>HERE IS MY QUESTION:
>
>Using a query (I guess), I would need to get Access to read the
>Parcel_ID, find the separator, extract the next number series, then make
>a new record, with the SAME Owner_First_Name, Owner_Last_Name, etc...,
>with the different Parcel_ID?
>
>Thanks in advance for your reply (ies).
>
>Phil.
>
><If you want to respond directly just remove the caps from the email
>address shown>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
0 |
|
John
6/21/2005 9:02:26 PM
There's an error in the ParseItems code I posted; should be
'CODE STARTS ----------------------
Public Function ParseItems( _
List As Variant, _
Item As Long, _
Optional Separator As String = " " _
) As Variant
'Returns the specified item from a list of "words" separated
'by a space (or items separated by Separator).
'Counting starts at zero.
'Returns Null if the list is too short.
'By John Nurick, 2004
Dim arWords As Variant
If IsNull(List) Then
ParseItems = Null
Exit Function
End If
arWords = Split(CStr(List), Separator, Item + 2)
If UBound(arWords) < Item Then
ParseItems = Null
Else
ParseItems = arWords(Item)
End If
End Function
'CODE ENDS-----------------------------
On Tue, 21 Jun 2005 22:02:26 +0100, John Nurick
<j.mapSoN.nurick@dial.pipex.com> wrote:
>'CODE STARTS ----------------------
>
>Public Function ParseItems( _
> List As Variant, _
> Item As Long, _
> Optional Separator As String = " " _
> ) As Variant
>
> 'Returns the specified item from a list of "words" separated
> 'by a space (or items separated by Separator).
> 'Counting starts at zero.
> 'Returns Null if the list is too short.
> 'By John Nurick, 2004
>
> Dim arWords As Variant
>
> If IsNull(List) Then
> ParseItems = Null
> Exit Function
> End If
>
> arWords = Split(CStr(List), " ", Item + 2)
> If UBound(arWords) < Item Then
> ParseItems = Null
> Else
> ParseItems = arWords(Item)
> End If
>End Function
>'CODE ENDS-----------------------------
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
0 |
|
John
6/23/2005 6:29:29 AM
OK. That explains why it wasn't working on my end. I will go back and check it and re-run it. Thank you John. John Nurick wrote: > There's an error in the ParseItems code I posted; should be > > 'CODE STARTS ---------------------- > > Public Function ParseItems( _ > List As Variant, _ > Item As Long, _ > Optional Separator As String = " " _ > ) As Variant > > 'Returns the specified item from a list of "words" separated > 'by a space (or items separated by Separator). > 'Counting starts at zero. > 'Returns Null if the list is too short. > 'By John Nurick, 2004 > > Dim arWords As Variant > > If IsNull(List) Then > ParseItems = Null > Exit Function > End If > > arWords = Split(CStr(List), Separator, Item + 2) > If UBound(arWords) < Item Then > ParseItems = Null > Else > ParseItems = arWords(Item) > End If > End Function > 'CODE ENDS----------------------------- > > On Tue, 21 Jun 2005 22:02:26 +0100, John Nurick > <j.mapSoN.nurick@dial.pipex.com> wrote: > > >'CODE STARTS ---------------------- > > > >Public Function ParseItems( _ > > List As Variant, _ > > Item As Long, _ > > Optional Separator As String = " " _ > > ) As Variant > > > > 'Returns the specified item from a list of "words" separated > > 'by a space (or items separated by Separator). > > 'Counting starts at zero. > > 'Returns Null if the list is too short. > > 'By John Nurick, 2004 > > > > Dim arWords As Variant > > > > If IsNull(List) Then > > ParseItems = Null > > Exit Function > > End If > > > > arWords = Split(CStr(List), " ", Item + 2) > > If UBound(arWords) < Item Then > > ParseItems = Null > > Else > > ParseItems = arWords(Item) > > End If > >End Function > >'CODE ENDS----------------------------- > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email.
|
|
0 |
|
CAD
6/25/2005 6:36:01 AM
Similar Artilces:
Emailing in excel 2003 02-26-10
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...
Outlook Mobile Access Problem
Hi I've a big problem in Outlook Mobile Access. I've a server exchange standalone withoput a front-end=20 one... I've configured the OWA web form authentication and SSL. For OWA it's all right...everything is going right... The problem is OMA When I try to log on on oma I find the following error=20 message :"Unable to connect to your mailbox" To solve my problem I've found in microsoft web site the=20 following link Microsoft Knowledge Base - 817379 Cannot Access Exchange Server 2003 by Using Outlook Mobile=20 Access When the Exchange Virtual Directory Require...
Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...
VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...
Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...
how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...
Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...
Line measurement
When I draw a line is there a way to put the length measurement on top of the line? I am drawing the footprint of a house for instance. I have all the measurements. How do I go about drawing each measurement as one line while it telling me the length between connection points? isometrics has such dimensioning...actually updates as you change the shape dimension "Matt" wrote: > When I draw a line is there a way to put the length measurement on top of the > line? I am drawing the footprint of a house for instance. I have all the > measurements. How do I go about...
Multiple hyperlinks in one cell
Hey, I'm stuck with an excel problem which I cannot solve. I want to pu different messages in one cell and add a hyperlink to some of thes messages. For example I want to put this information all into one cell "message1 - message2(with hyperlink) - message3(with hyperlink) message4" I really hope it is possible to create multiple hyperlinks within on cell, but for now I can only add a hyperlink to a cell. If you would know how I could fix this issue I would be reall gratefull. Thx in advance, Veroniqu -- Message posted from http://www.ExcelForum.com If it's really...
Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...
EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...
Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...
Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...
connect a form to excel database
I have a database in excel which lists our stock and prices etc. I would like to be able to enter a stock number on an invoice that will retrieve the details and price from my excel database. Can this be done? Excel has built in option--try data|form. If that doesn't quite work, but it's close, John Walkenbach has an enhanced dataform at: http://j-walk.com/ss/dataform/index.htm And if you want to design your own, Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html Kathy wrote: > > I have a database in excel which lists our stock and p...
Does anyone know where I can download EXCEL.MSI?
I am missing my Excel 2002 CD, and can't install Office Update Service Pack 3 without this file. ...
Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...
Excel to check for regular expression?
Hi, How do you use Regular expression in excel? For eg: Check a column of data to see whether are all of them valid email addresses? Please advise THanks By valid, do you mean that the email is in proper syntax or that it is an actual email address. Text formulas can be used for proper syntax. Perhaps a Visual Basic proceedure. I doubt you can check that the email address actually exists, as many companies and ISPs will simply disregard and drop mail to invalid email addresses to eliminate spam "user" wrote: > Hi, > > How do you use Regular expression ...
In Excel program shows a broken line and don't know how to get ri.
I am trying to put in entries in Excel program and when I go to Print Preview, it only shows part of the entries I put in. When I go to the last entry that it shows in Print Preview, there is a broken line between it and the next entry. I don't know what I did. I could use some help You probably have multiple areas selected for your print area. you can check this by <view><page break preview> One possibility to correct this would be <file><print area><clear print area> or specifically select the area you want printed and <file ><Print area>...
Excel Cells Contents to HTML Pages
Hi, I have an excel sheet which has two columns. Column 1: PageName Column 2: HTMLCode PageName has information like: OurServices.htm which is the name of th HTML page. HTMLCode clumn has actual HTML Code like: <html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta name="GENERATOR" content="Microsoft FrontPage 5.0"> <meta name="ProgId" content="FrontPage.Editor.Document"> <meta http-equiv="Content-Type" content="text/html charset=windows-1252"> ...
Multiple conditional formating for one cell
I have a workbook with several sheets. Sheet1 has several hundred rows of text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2 I have I have a cell that allows my users to free hand type in a keyword(s) to search for in Sheet1. This cell is also has a reference name of "Special_Char". Now comes the question..... I like to for the user to type the keywork into "Special_Char" and any or all the rows in Sheet1 that contain the keyword as part of its text to turn yellow. Is there a way to do this? Please keep in mind that the keywork i...
How to draw a selection line?
I want to draw selection lines with 1 pixel width dotted line style. normally it works ok, but the it looks not very good when I zoom the image to a bigger magnification. What I want is like the effect in many popular image processing softwares such as Adobe Photoshop. for example, I make a ellipic selection and draw a dotted line ellipse it looks ok. but when zooming in it still the same as normal ,but in photoshop it became aliasing lines which seems to be composed by several small blocks. Does any have any idea on this question?? any help would be appreciated. ...
Question Regarding Excel 2007 Formatting Corruption #2
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...
opening Excel and then closing it
I have a file that is passed to me in XLS format, but I would much prefer to work with it in CSV. So I did this... ' open the downloaded file in Excel and copy it over to L in csv format Workbooks.Open "O:\Downloads\D00100001554_0.xls" Workbooks("D00100001554_0.xls").ConflictResolution = xlLocalSessionChanges Workbooks("D00100001554_0.xls").SaveAs "L:\Activity.csv", xlCSV Workbooks("Activity.csv").Close False This works fine except it leaves Excel open in the background. The only thing to do is fine it in Processes a...
Free training manuals for Microsoft Office (inc. Excel)
Mouse Training in the UK has released their training manuals free for all comers plus some Quick Reference Guides. The Training Manuals are for Office 2007, Office 2003, Office XP (2002) and Office 2000. They cover Word, Excel, PowerPoint, Outlook, Access, Project and Visio. There are also 'Quick Reference Guides' for Office 2007 and Office 2010 applications. All documents are in PDF format and available from mousetraining.co.uk . "Victor Delta" <none@nospam.com> wrote in message news:ih7th3$9a5$1@news.eternal-september.org... > Mouse Training in the UK h...
Excel Formula 11-24-09
Could someone please help with the following formula as I keep coming up with too many arguments. Column 1 - Salaries Column 2 - NI code Column 3 - Result £ 1,535.25 51A £ 25,125.00 51A £ 45,000.00 51D £ 2,900.00 51D £ 56,475.00 51A £ 15,225.00 51C I want to use the above columns to calculate the NI for each person using the following forumulas: If Column 2 is 51A,51B or 5...
If i type in the cell A34: neil.Holden@test.com and press a button is it possible to email to the address of what ever is in A34 is? The email body should say: this has been submitted for cell B34 and todays date. Thanks. Check out Ron De Bruins "Send-Mail" tips: http://www.rondebruin.nl/sendmail.htm Micky "Neil Holden" wrote: > If i type in the cell A34: neil.Holden@test.com and press a button is it > possible to email to the address of what ever is in A34 is? > > The email body should say: this has been submitted for cell B34 and...
Outlook Mobile Access Problem
Hi I've a big problem in Outlook Mobile Access. I've a server exchange standalone withoput a front-end=20 one... I've configured the OWA web form authentication and SSL. For OWA it's all right...everything is going right... The problem is OMA When I try to log on on oma I find the following error=20 message :"Unable to connect to your mailbox" To solve my problem I've found in microsoft web site the=20 following link Microsoft Knowledge Base - 817379 Cannot Access Exchange Server 2003 by Using Outlook Mobile=20 Access When the Exchange Virtual Directory Require...
Importing Data into an Excel Pivot Table via Access
I have set up a query in Microsoft Access which is linked to our AS400 server. I have created pararmeters within Access which asks for certain fields which works. I then go into Excel and create a pivot table with the external data source that I have created in access. When I go to enter a pararmeter within Microsof Query I get a reply saying that "Parameters can not be used with this Query", what I want to do is setup a parameter on the Excel spreadsheet which then goes and gets the data i require from this parameter. I would be very grateful if someone could help me with thi...
VBA from another app: Suppressing Excel confirmation dialog?
After creating/formatting several worksheets from MS Access, I'd like to delete the "Sheetn" worksheets that got put there when I did a .WorkBooks.Add. I avoided using them because I'm not sure how/why they are created - i.e. maybe some user's defaults would only create 1 empty sheet or none. So, form MS Access's VBA I'd like to do: On Error Resume Next .Worksheets("Sheet1").Delete .Worksheets("Sheet2").Delete .Worksheets("Sheet3").Delete .Worksheets("Sheet4").Delete On Erro...
Illegal operation error while printing EXCEL or WORD Files
Hi, I am facing an illegal operation error when i try to print any file from excel (any no. of pages), this happens in stand alone printer as well as a networked printer. When we press the print button, it flashes this message, but still prints, but once the printing is completed, i will have to restart the PC. Due to this error other applications PRINTING also will NOT HAPPEN and the only way out is, restart the PC. This happens not only in EXCEL, it happens in all the MS applications (outlook, access, front page, powerpoint also). When I check the print manager (before restart),...
how do I add times in Excel and result in hours & mins
I want to insert a time when I start work and a time when I take a break, then a time when I leave work. Following that I want to be able to add up the amount of hours that I have worked. This will enable me to plan my week ahead and ensure I only allocate a specific amount of time to a project. http://www.cpearson.com/excel/datetime.htm#WorkHours -- Kind Regards, Niek Otten Microsoft MVP - Excel "Rty Shaw" <Rty Shaw@discussions.microsoft.com> wrote in message news:37D03D72-5525-4D6E-8ED7-2911B16248B0@microsoft.com... >I want to insert a time when I start work and...
Opening a new instance of Excel
I am using multiple monitors for work and it is great! Is there a setting that I can use so that it opens each new excel file in a new excel window so I can drag different ones to each monitor? Is there a similar setting for Word? I am using Excel 2002 and Word 2002. Thank you. Hi, Yes, you can check the Windows in Taskbar checkbox in Tools; Options. This is on the View tab for both Word and Excel. >-----Original Message----- >I am using multiple monitors for work and it is great! Is >there a setting that I can use so that it opens each new >excel file in a new excel ...
Line measurement
When I draw a line is there a way to put the length measurement on top of the line? I am drawing the footprint of a house for instance. I have all the measurements. How do I go about drawing each measurement as one line while it telling me the length between connection points? isometrics has such dimensioning...actually updates as you change the shape dimension "Matt" wrote: > When I draw a line is there a way to put the length measurement on top of the > line? I am drawing the footprint of a house for instance. I have all the > measurements. How do I go about...
Multiple hyperlinks in one cell
Hey, I'm stuck with an excel problem which I cannot solve. I want to pu different messages in one cell and add a hyperlink to some of thes messages. For example I want to put this information all into one cell "message1 - message2(with hyperlink) - message3(with hyperlink) message4" I really hope it is possible to create multiple hyperlinks within on cell, but for now I can only add a hyperlink to a cell. If you would know how I could fix this issue I would be reall gratefull. Thx in advance, Veroniqu -- Message posted from http://www.ExcelForum.com If it's really...
Excel Edit F2 button changed for Mac???
Switched to Microsofts version of Excel for Mac. Can anyone tell me what keystroke allows me to edit a cell? Before I switched to a Mac it was the F2 button. Please help. Thank you. See the answers in the m.p.mac.office.excel newsgroup. In article <1176582208.958694.269620@q75g2000hsh.googlegroups.com>, ssears@indy.tds.net wrote: > Switched to Microsofts version of Excel for Mac. Can anyone tell me > what keystroke allows me to edit a cell? Before I switched to a Mac > it was the F2 button. Please help. Thank you. ...
EXCEL TROUBLESHOOTING #2
I have an excel file (2000 format), that after I made a number of changes is causing me problems when I re-open the file. Windows task manager goes to 100% CPU activity, and i cant do anything within the excel file. However, if I set recalculation to manual before I open the file, all seems fine. Obvioulsy I have a problem. But how do i find that problem ? Thanks in advance. I have had some experience running large spreadsheets lately. Above a certain size, the recalculation time seems to climb very fast. While Excel is recalculating, you can't do anything anyway. Best in my v...
Excel Crash
I use Excel and Word 2003 using Windows NT. I've kept some files on a jump drive so I can work on them at home. I attempted to work on a Word documents which had an Excel worksheet inserted in it. I tried double clicking on the worksheet to edit it and Word and Excel shut down. Now when I attempt to open Excel at home it asks for my Office XP Professional installation cd. (I have Office XP at home with Windows XP). I'm having a hard time locating my original discs. Does anyone have any suggestions or experience anything like this? ...
Does anyone have a dashboard gauge (speedometer style) for Excel?
I am trying to create dashboard charts from Excel data and would love other templates not available in Excel today - speedometer charts, multi-dimension comparitive charts, charts that build information overlays. I regularly create these in a manual way for executive and customer summaries but would appreciate the ability to automatically generate these types of charts allowing for real time viewing of "what if" scenarios. Steve, there are tons of these things out there to review, few better than this collection: http://www.andypope.info/charts.htm Andy Pope has put together...
connect a form to excel database
I have a database in excel which lists our stock and prices etc. I would like to be able to enter a stock number on an invoice that will retrieve the details and price from my excel database. Can this be done? Excel has built in option--try data|form. If that doesn't quite work, but it's close, John Walkenbach has an enhanced dataform at: http://j-walk.com/ss/dataform/index.htm And if you want to design your own, Debra Dalgleish has a get started with userforms: http://contextures.com/xlUserForm01.html Kathy wrote: > > I have a database in excel which lists our stock and p...
Does anyone know where I can download EXCEL.MSI?
I am missing my Excel 2002 CD, and can't install Office Update Service Pack 3 without this file. ...
Excel ignores boot-time regional settings when interpreting a date
I have a third party DDE app that exports dates as strings, correctly using the short date format in the regional and language settings, which, in my case is MM/DD/YYYY (reset at every boot). Unfortunately excel insists in interpreting that date as DD/MM/YYYY [Application.International(xlDateOrder)=1, it should be 0], disregarding my regional settings. The result is that all the dates are wrong or #VALUES! If this is not wierd enough, listen to this: it only happens after I reboot (when the date format is reset to MM/DD/YYYY). If subsequently I change the short date format in the regional se...
Excel to check for regular expression?
Hi, How do you use Regular expression in excel? For eg: Check a column of data to see whether are all of them valid email addresses? Please advise THanks By valid, do you mean that the email is in proper syntax or that it is an actual email address. Text formulas can be used for proper syntax. Perhaps a Visual Basic proceedure. I doubt you can check that the email address actually exists, as many companies and ISPs will simply disregard and drop mail to invalid email addresses to eliminate spam "user" wrote: > Hi, > > How do you use Regular expression ...
In Excel program shows a broken line and don't know how to get ri.
I am trying to put in entries in Excel program and when I go to Print Preview, it only shows part of the entries I put in. When I go to the last entry that it shows in Print Preview, there is a broken line between it and the next entry. I don't know what I did. I could use some help You probably have multiple areas selected for your print area. you can check this by <view><page break preview> One possibility to correct this would be <file><print area><clear print area> or specifically select the area you want printed and <file ><Print area>...
Excel Cells Contents to HTML Pages
Hi, I have an excel sheet which has two columns. Column 1: PageName Column 2: HTMLCode PageName has information like: OurServices.htm which is the name of th HTML page. HTMLCode clumn has actual HTML Code like: <html> <head> <meta http-equiv="Content-Language" content="en-us"> <meta name="GENERATOR" content="Microsoft FrontPage 5.0"> <meta name="ProgId" content="FrontPage.Editor.Document"> <meta http-equiv="Content-Type" content="text/html charset=windows-1252"> ...
Multiple conditional formating for one cell
I have a workbook with several sheets. Sheet1 has several hundred rows of text. Some of the same text will appear in multiple rows of Sheet1. On Sheet2 I have I have a cell that allows my users to free hand type in a keyword(s) to search for in Sheet1. This cell is also has a reference name of "Special_Char". Now comes the question..... I like to for the user to type the keywork into "Special_Char" and any or all the rows in Sheet1 that contain the keyword as part of its text to turn yellow. Is there a way to do this? Please keep in mind that the keywork i...
How to draw a selection line?
I want to draw selection lines with 1 pixel width dotted line style. normally it works ok, but the it looks not very good when I zoom the image to a bigger magnification. What I want is like the effect in many popular image processing softwares such as Adobe Photoshop. for example, I make a ellipic selection and draw a dotted line ellipse it looks ok. but when zooming in it still the same as normal ,but in photoshop it became aliasing lines which seems to be composed by several small blocks. Does any have any idea on this question?? any help would be appreciated. ...
Question Regarding Excel 2007 Formatting Corruption #2
Hi Folks, I am having a problem with Excel 2007 files losing all formatting (merged cells, colors, borders, and data formatting (99% turns into 0.99)) when I open a file on our office server make edits and then save the new file on the server. Each sheet usually has a mix of locked and unlocked cells and I unprotect the sheet to make edits. Also, something is fundamentally changed with the file as its size doubles or triples. If I reopen the corrupted file and redo any of the formatting and try to save it none of the new formatting is retained either. Has anyone else ever experienced a pr...
opening Excel and then closing it
I have a file that is passed to me in XLS format, but I would much prefer to work with it in CSV. So I did this... ' open the downloaded file in Excel and copy it over to L in csv format Workbooks.Open "O:\Downloads\D00100001554_0.xls" Workbooks("D00100001554_0.xls").ConflictResolution = xlLocalSessionChanges Workbooks("D00100001554_0.xls").SaveAs "L:\Activity.csv", xlCSV Workbooks("Activity.csv").Close False This works fine except it leaves Excel open in the background. The only thing to do is fine it in Processes a...
Free training manuals for Microsoft Office (inc. Excel)
Mouse Training in the UK has released their training manuals free for all comers plus some Quick Reference Guides. The Training Manuals are for Office 2007, Office 2003, Office XP (2002) and Office 2000. They cover Word, Excel, PowerPoint, Outlook, Access, Project and Visio. There are also 'Quick Reference Guides' for Office 2007 and Office 2010 applications. All documents are in PDF format and available from mousetraining.co.uk . "Victor Delta" <none@nospam.com> wrote in message news:ih7th3$9a5$1@news.eternal-september.org... > Mouse Training in the UK h...
Excel Formula 11-24-09
Could someone please help with the following formula as I keep coming up with too many arguments. Column 1 - Salaries Column 2 - NI code Column 3 - Result £ 1,535.25 51A £ 25,125.00 51A £ 45,000.00 51D £ 2,900.00 51D £ 56,475.00 51A £ 15,225.00 51C I want to use the above columns to calculate the NI for each person using the following forumulas: If Column 2 is 51A,51B or 5...