Free Online Lessons/tutorials For 2000 Formulas???
Jun 30, 2009Im after online tutorials for formulas. eg vlookup, sumif, concatenate etc. Does anyone know of free sites that will show a noob how to use them?
View 2 Replies
ADVERTISEMENT
2000/2003 Formula Compatibility: The Formulas Fields Do Not Populate Data Just #N/A
Apr 24, 2007When I open an excel 2000 spreadsheet in 2003, the formulas fields do not populate data just #N/A. I can see data just fine in Excel 2003 viewer.
View 5 Replies View RelatedFill An Online Form In From VBA
Mar 18, 2008I've got access to a website that you input a bank account sorting code, and it tells you which bank it relates to. I've got a file with several thousand a month to check. It need a login and password but I've got them, so could have the screen open.
On the website, I enter the sortcode into a field, and it returns the bank who owns that sort code onto the screen as selectable text.
Is it possible, using VBA, to fill in the input field, press the search button, and snag the value returned in the text box?
Web Query Online Pdf Document
Mar 12, 2008Is it possible to extract data via a webquery from an online PDF document? Like this one: http://www.paalp.com/_filelib/FileCa...r2007Recap.pdf
View 2 Replies View RelatedOnline Database - Query More Than 15 Digits
Sep 25, 2011I want to web query an online database. One of the columns contains numbers 16 digits long. When Excel imports the query I get the number in exponential format and it replaces the last number with a zero.
Example: The original is 1002002660100027, after query ends up as 1.002E+15 and reformated as text I get 1002002660100020 (Last # changed to a "0")
Is there a way to prevent the query from replacing the last number with a zero when importing from a query? Or.. can I get the query to paste as text?
Sheets/Worksbooks Online For Sharing
Oct 16, 2006We want to share an excel sheet online, where we can go to and edit and save it. And possibly link the fields to a dB to build a website from. Anyways what is the best cheapest way of doing this? I googled and found so much stuff, was hoping someone here has already gone thru this process and can just point me to the best one....
View 2 Replies View RelatedOnline Sharable Dynamic Company List
Jan 18, 2013I've also attached an example excel spreadsheet of how I was thinking the first page would lay out.
I work for a company that has multiple properties around my region here. We all deal with similar companies and temporary workers, and what i'd like to do is set up an online document (using google docs?) where we can all go online with our login, search the document, and even add entries, as well as comment and rate on entries already existing. I may be stretching the boundaries of what excel can do, but I really don't want to start diving into programming something.. if it can be done this seems like the easiest way to accomplish this task.
Here's a quick summary of what I'm thinking (and use the attached as a visual example)
the first page will be the only place people will really "look" at. On there it will have searchable areas to load a list of names based on criteria (name/partial name/letter, as well as maybe a drop down or check box of specific standards the company or worker deals with like lighting/audio/video/etc)... In my mind the company or worker could specialize in more than one of these areas.
once you hit the search button, it will populate a list of names based off of that search. Inside of that scrollable box will be the name of the company/worker, and some ratings (it could be stars or even a number percent) - the name itself would be clickable, then next to that would be a button to rate the company/worker, and a button to comment. If you click on rating it would pop up a box to add a percent. that percentage would add a field on another page and the total average rating is what is displayed. If you click on comment, a pop up box would come up and they would be able to add a quick comment on that company or worker. Once they click add on that it would add/append that comment onto another page in the database with the time/date stamp.
In the next section, when you click on a name from the search, that contacts information will pop up in a pre-defined format. Clicking on a different name would replace all of the information with the new contacts information sort of "dynamically" loading the content each time from a separate sheet that has all of that information. Nothing in here will be clickable, just a nice laid out viewport of the information that's been entered for that company or worker with address/phone number/rating/etc.
Then finally in the last section would be a scrollable box that would pop up all of the comments that have been added for that person with a date/time stamp for each comment.
As far as rating system.. i was beating myself up a bit about how to do that, but ultimately not many people would have access to it so I'm not worried about individual hits.. as far as I'm concerned people can rate more than once, it would just add to it and it would average those ratings.
example.xlsx
Using Vlookup To Find Phone Numbers Online
Apr 12, 2006How can I create a vlookup function to automatically retrieve phone numbers off switchboard.com using my current mailing list?
I have a list of more than 4,000 of my customers. I want to ad in phone numbers automatically. I currently have the first and last name in one column. The address, city, state and zip are all each in their own column.
I have never use vlookup before, nor have I ever used excel other than for data entry. What is the easiest and most efficient way to do this?
I searched this site and seen vlookup mentioned for this type of automatic data entry. There may be another way or method I am not aware of.
Online Spreadsheet To Share Between Few Friends With Password Option?
Dec 25, 2004Is there anywhere I can set up a Online Excel Spreadsheet to share with a few friends with a password option to prevent people from looking at it?
I need everyone in the group to be able to update and save the spreadsheet
Excel 2010 :: Importing Online Data Into Spreadsheet
Jun 2, 2014I have been tasked with streamlining a process to collect data from a specific online website (Web of Science) and import it into an Excel 2010 spreadsheet.
Currently they are going to the website, entering a short number of search parameters and then manually recording the pertinent data from the webpage. They would like to be able to enter a keyword in Excel (which acts as the search item) which then automatically does the rest of the process and provides them with a spreadsheet of the required data.
Is this possible? Perhaps by using Visual Basic code within Excel? I also saw a method that employed SharePoint Server 2010.
Online Status - Conditional Cell (Connected / Unconnected)
Mar 4, 2012I am seeking some code that will check that the PC / Laptop has an online ethernet connection via it's NIC (Network interface card). I want it to output to the spread sheet to a Conditional cell (Connected / unconnected).
View 2 Replies View RelatedFree Plug-ins Add-ins
Oct 17, 2006Need useful Excel Plugin/Addins.
View 4 Replies View RelatedExcel Macro To Lookup Edit Data In Online Database
May 18, 2012I'm working on an Excel based product. As part of the security process I would like to require the user to enter a product id key into excel. Then the spreadsheet would compare the entered value to some kind of online database to verify the id key is valid. Finally, the spreadsheet would update the online database so that the id key is no longer functional. How easy is this to do?
I know verifying that the id key is valid is fairly simple.
Here is my biggest issue: How to get Excel to update the database and of course what form would this database take. Maybe its a table on a webpage, or google docs. Again the issue is how would I be able to update the table.
How To Get An Overview Of Free Rooms
Mar 19, 2007have six different locations in a city in which I have 6 beds for people with psyhiatric problems to sleep in.
The managers of the different location send me whenever somebody new comes or somebody leaves the following information:
Name of client
Start date of stay (when new clients arrive)
End date of stay (when client leaves)
I have to keep all record of clients that have used our locations, so the list gets very long during the year and it is then very hard to see which beds are still available.
What I would like to see in a separate sheet is which location still has how many beds available. I then mail this overview weekly to organizations that assign people to my locations.
Include That Weekends Are Free
Jan 13, 2006Does anyone knows how to calculate if translator is free or not, regarding that weekends are free ( I mean if weekends are free how to check if translator is able to translate a document)
View 9 Replies View RelatedAdd Free Column To A Pivot Table
Aug 8, 2012I have a pivot table with slicers to easily filter the data. I need to add a column with editable, free text that is filtered along when using the slicers.
If I try adding a column in my data source of the pivot and I change one value in the pivot then all rows that have this value change along.
Moving Row To First Free Line Of Other Sheet
Apr 30, 2009Im looking to use this code to move the selected rows of a workbook over to a new worksheet in the sameworkbook. It works fine however for some reason it pastes the row over the last row.
how to modify it so it pastes the row on the first empty row?
Sub MoveSelectedRows()
Application.ScreenUpdating = False
Dim strSheetName, strCellAddress As String
strSheetName = ActiveSheet.Name
strCellAddress = ActiveCell.Address(False, False)
Rows(ActiveCell.Row).Cut
Sheets("Closed Projects").Select 'Change sheet name to whatever consolidated tab name is.
Range("A" & Range("A65536").End(xlUp).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & ActiveCell.Row).Select
Sheets(strSheetName).Select
Range(strCellAddress).Select
Rows(ActiveCell.Row).Delete
Application.ScreenUpdating = True
End Sub
Also, as a side note, how do I put a prompt in so that once I press the button to move the row the user is prompted asking if they are sure they want to move the row? 'Are you sure you wish to move row X?' Yes/No.
If yes - run rest of script
If no - abort script
Free Memory As Macro Running
Aug 18, 2007I have a macro that is performing a lot of string comparisons and sorting out a lot of unformatted data using a loops and instr calls and passing results into arrays to store in a database. The macro works flawlessly, however as the macro runs, over time is gets slower and slower until its barely moving 10% as fast as it was to start.
I run the macro in a separate windows session so I can continue working w/o the macro affecting other excel related tasks I work on. I can tell the computer begins to slow down as the macro runs. Is their any way to clear the system memory while the macro is running, maybe every 5 minutes or something?
Free Form Textbox In Search Field
Feb 10, 2013How can I allow users to either select the SKU from the dropdown menu, or to type in the SKU # in the dropdown menu (C5 through F5) and have it populate the data fields for that SKU when they press the 'Enter' key?
Currently using Data Validation ='KVM Comparison Data'!$B$4:$OP$4 and cell logic to allow the information to be pulled from a hidden tab =INDEX('KVM Comparison Data'!$D$2:$OP$65,MATCH('KVM Product Comparison'!$B6,'KVM Comparison Data'!$A$2:$A$65,0),MATCH('KVM Product Comparison'!C$5,'KVM Comparison Data'!$D$4:$OP$4,0))
In short, I want to keep it the way I have it, but allow users to also have the option of typing in a SKU and pull the data up that way. I know this is possible, I just can't figure out how to do it, while keeping my current solution in place as well.
Format Column Widths - Free And Fixed
Jan 11, 2010I need to know how to format columns in a spreadsheet. What I want to do is autofit the columns, all except for Column A; I want that want at a fixed width of 9. The code below is my attempt to do this, and it's not resulting as I wish. Stepping through the code, it performs the first part beautifully and autofits all of the columns. Where I have the line
View 2 Replies View RelatedExcel Automation On Tax Free Period / Holiday?
Oct 9, 2013Any excel formulae to build in into my financial model for tax free period/tax holiday, eg for first 6 years, the project company no ned pay tax? it is best if the formulae can bring flexibility for user to change the no. of years (tax holiday).
View 1 Replies View RelatedCount If Left(cell, 4) Not Equal To Free
Jul 14, 2008Im looking to count fields in column G, where the first 4 letters of column G do not begin with the letters "free", I came up with the following formula which doesn't work at all!!
Im also looking to do the same but with a sumif to perform the sum on a different column of data, but with the criteria LEFT(G15:G164, 4) & "" & "free") - (not sure if this bit is right either) staying the same.
=COUNTIF(G15:G164, LEFT(G15:G164, 4) & "" & "free")
Suspend/hibernate Sheet To Free Cpu Power
Apr 28, 2007how to suspend/hibernate some specific sheets in one workbook , to free cpu power?
View 5 Replies View RelatedModify Free-form Shape Coordinates
Feb 21, 2008Right now I'm modifying a figure based on size data in an excel table.
I'm modifying the X/Y coordinates of the points of the freeform shape with VBA, however, I have to use absolute x,y coordinates.
I'd like to be able to move the shapes throughout the sheet. With fixed coordinates, this makes it difficult. I'm hoping there's an easy way to it
Compound Interest After Mortgage Free Point
May 10, 2008I've found this calc but it doesn't compare to my particular scenario/goals: http://dinkytown.net/java/InvestmentDebt.html. The major difference is that this dinkytown calc requires a new loan to be put in place and I am not in a position to refinance my current mortgage. My plan is to pay an extra $267 (b3) to the mtg (b4). It will take 128 months (b6) to pay off the mortgage. After that point, I need to calculate what the newly freed capital (b5) would do if I put in an account (i.e. a CD or a simple US savings account) that had an Annual Percentage Yield (APY) of 1, 2, and 3% for 20, 15, 10 and 5 years. I thought that I had the right formula in place for cells b7:b8 should the account earn a 0% rate of return, but I think it's faulty since it gives me a negative number for 10 and 5 year accumulation periods.
View 2 Replies View RelatedVBA To Change Background Colour In Free Form Depending On Text In Cells?
Apr 3, 2014I have a spreadsheet with several freeforms. I would like them to change backgroundcolour, if the content in a cell is equal to another cell.
Basically I would like my shape "Freeform1" to change backgroundcolour to RGB (0, 180, 0) if Sheet1.Range ("A1") = Sheet2.Range("D3").
If it's not the same content in both cells, I would like the freeform to remain unchanged, that's RGB (79, 129, 189).
This should happend automaticly, so I don't have to click the shape to make it happen.
VBA - Distinguishing When Cell Is Edited By Data Validation List Or Free Format Text
Mar 20, 2014VBA code which can distinguish whether a cell has been edited by:
selecting an option from a data validation list (DVL)
or
manually editing the text in a cell?
The ""Show error alert after invalid data is entered"" option is unticked to allow both selection from the DVL as well as free format text entries.
The problem arises when using the code below which makes an edited cell within a range display the new value as well as the prior value(s). This works well when using the DVL but not when editing the cell.
Is there any code which can distinguish between the 2?
[Code] ....
Data Validation Dropdown Menu But At Same Time Allowing Users To Enter Free Text
Nov 15, 2010Is there a way to have a data validation drop down menu but at the same time allowing users to enter free text as well.
View 3 Replies View RelatedSave Back Up Copy Online At Each Save
Mar 27, 2008I have a sheet which I would like to back up online every time it is saved.
I have a sub, OnlineFile(), which I have used many times without any problems, which creates a .BAT file to use ftp to upload files onto our server. As arguments, it takes the local file path that you want to upload (or download to), the name of the file as you would like it stored, the online file path, and whether you want to upload the file onto the server, or download it off the server.
It should be very easy to combine the two: what I tried was:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, cancel As Boolean)
ThisWorkbook.SaveCopyAs ThisWorkbook.Path & "" & "Rubbish.xls"
Call OnlineFile(ThisWorkbook.Path & "" & "Rubbish.xls", "Rubbish.xls", "wwwrootexcelfilesMasters", "Upload")
Kill ThisWorkbook.Path & "" & "Rubbish.xls"
End Sub
Every time I ran this, the file got uploaded correctly, but Excel had a series of problems. Often the code ran fine the first time it was run, but crashed the second time round.
After a fair bit of troubleshooting, I found that the problem came about when the file was trying to save itself - i.e. after the end of the sub. If you pause any macros from running, it still saved fine, but when the code ran, it found errors, froze or closed excel.
My thought at this point was that the code was fragmented or something. I made a new sheet, and retyped just the relevant bits of code (i.e. the BeforeSave sub, the OnlineFile sub and created the form called by OnlineFile). The problem persisted.
I've come up with a workaround for this now (which uses the same OnlineFile sub & form, and works fine), but it's driving me crazy - why does this cause excel to die? Particularly on the second time through? The code seems far too simple to cause such problems - is there a bug in Excel perhaps (I'm using Excel 2000 on Windows XP).
Add Ins For 2000
Mar 30, 2005Where do i find the numbering add ins for Excel 2000? I receive a message
every time I open a document that says: I must load them for optimal
numbering and toolbar behavior. I have looked in the add ins by going to
add remove program, selecting Microsoft office, then change, then excel.
under the add ins there is nothing that says "numbering". I have also looke
on line for a down load at the Microsoft web site.