Search results

Hi Coolkiran Love this problem -- not being at a Mac right now, I can't test the solution. The "issuse" is the .webarchive format. 1) Convert .webarchive to .html with (on mac) textutil -convert webarchive whatever.html 2) Then in Excel link the whatever.html as a data source Your file...

I've used this in the past: http://chandoo.org/wp/2009/06/05/hide-cell/ Cheers Glen

In Excel 2013 this can be achieved with a combination of: A1=WEBSERVICE("http://maps.googleapis.com/maps/api/geocode/xml?address=POSTCODEHERE+,+&sensor=false") > replace POSTCODEHERE with your post code / zip To extract the latitude B1=FILTERXML(A1,"//result/geometry/location/lat")...

Hi Shaun Pulling data from XML is something I've done a fair bit - and the way you are going about it was the way I used to do it until 2 things: 1) Excel 2013 - WEBSERVICE function - pulls in the XML feed directly and then you can extract from the XML tree. BUT that wont work for you as...

Hi The simplest way is to protect the source files from changes so that this can't happen. Cheers Glen

Hi Not sure you can do this. Conditional formatting will let you trap and error and alter the cell format: "=ISERROR($B$2)" for example and set the cell to FILL with RED and RED Text .... Cheers Glen

Hi Why do you want to do this - I ask as there might be a different way to achieve what you are looking for. For example, do you want sort by Author? Cheers Glen

QIF - as in Quicken? Never used it, but: http://lcbell4.tripod.com/softwareman4life/qif_to_csv_converter/qif_to_csv_converter.html Seems easy enough. Glen

Hi This is a one shot deal - right? IE this is not a working spread sheet, you just want to clean the data so you can analyse the results? I would: 1) Start again 2) Use the DATA : Text to Columns function 3) Delimited with , and space : Treat Consecutive delimiters as one This will...

Hi Can you post a sample of the spreadsheet so that we know exactly what we are dealing with. Dropbox or other.... Cheers Glen

Hi At risk of offence - Google is your friend. I don't mean to be awkward. Cheers G EDIT: OK - I didn't look where this was posted. Sorry for my "Mr Grumpy" reply.

Hi Assuming that the list of dealerships starts from A2. Put list of Main Delerships (Mazada etc) into D2 downwards. In E2 place: =ISNUMBER(LOOKUP(9.99999999999999E+307,FIND($D$2:$D$999,A2))) This checks the string in A2 against the list of dealerships in D2:D999 (Make sure there are no...

Hi In WORD define the place that you want to JUMP to as a BOOKMARK -- in your case highlight December and INSERT BOOKMARK. Name the bookmark as December. In Excel, when you insert the hyperlink linking to the document, add #December after the file name. When you click on the link in...

Hi Interesting question - and one I had never considered. http://www.contextures.com/xlcomments03.html#Indicator ... has an alternative to number the comments with a rectangular box and inset number. Glen

Hi Isn't this as simple as Print Selection and select PDF as the printer? Is the selection you want to print a named range? Use the macro recorder function to capture the steps and view the code. Cheers Glen

Hi Not sure - but I've done this before: Alt-F11 to start the VBA editor. Double click ThisWorkbook paste this: Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) ThisWorkbook.BuiltinDocumentProperties("Author").Value ="Your Name" End Sub Every time you...

Hi Sam On the basis of you last post, my formula is wrong and it's more simple. Modify all the H2CALC formula to read: H2Calc = e2val * htmult Should be fine then. Glen

Hi Sallan I got lost on what you meant / logic -- could you provide your expected output when E2 = 10, 20, 50, 100. However, I did this via a udf: Public Function H2Calc(e2val As Single) As Single Dim h2mult As Single Select Case e2val Case Is = 1 h2mult = 25 H2Calc = h2mult Case...

Not really a quest for here. It depends on how long it took you to create, the sophistication of the model you have made and your target customers pocket depth. Or as the rich and famous stick to -- "things are worth what people will pay for them, not a penny more". Give it away free for...

Sorted (I think) Public Function BestFit(rng As Range) As Single Dim formlrBestFit As String formlrBestFit = "= AVERAGE(IFERROR(SUBSTITUTE(" & rng.Address & ",{""a"";""b"";""c""},{"".75"";"".5"";"".25""})+0,""""))" BestFit = Application.Evaluate([formlrBestFit]) End...

OK, so my next question is: How to I put that array formula into a UDF so I can create a BestFit() formula that passes the selection to the UDF..... Using macro recorder I end up with: Selection.FormulaArray = _...

Haseeb This: =AVERAGE(IFERROR(SUBSTITUTE(E3:I3,{"a";"b";"c"},{".75";".5";".25"})+0,"")) as an array, works a treat. Perfect & thanks Glen

Hi My file is here: http://dl.dropbox.com/u/13025982/level.xlsx I have a sheet with the following in E3 to J3 5a, 5b, 5a, 5c, 5b (equally it could be any string from 4c to 7a) I am looking to streamline selecting the best numerical representation of these numbers/letters The best I...

HI Of course you can program in an "exit" with error trapping: Sub KeepDoingSomething() Application.EnableCancelKey = xlErrorHandler On Error GoTo ErrHandler Dim i As Integer i = 0 Do Until i = 10 [A1] = i + 1 Debug.Print i Loop Application.EnableCancelKey =...

Hi (Lukes is more elegant though) In B1: =LEFT(A1,FIND(".",A1)-2) copy down In C1: =LEFT(MID(B1,FIND(".",B1,2)-1,100),(LEN(MID(B1,FIND(".",B1,2)-1,100))-LEN(D1))) and copy down In D1...