Posts by ecp98rjs

I have created a macro in Excel that add tooltips to shapes (the shapes are areas on a map):

Sub labelshapes()
    Dim cll As Range
    Dim val As String
    For Each cll In ActiveSheet.Range("Y30:Y375")
      val = cll.Offset(0, 2)
    ActiveSheet.Hyperlinks.Add Anchor:=ActiveSheet.Shapes("Freeform: Shape " + cll), Address:="", ScreenTip:=val
    Next cll
End Sub

I want to be able to transfer the map into powerpoint for a presentation, but when I do the hyperlinks and tooltips are lost.

Is there a way to do this and retain the tooltips, or alternatively recreate them in powerpoint? (I also noticed that powerpoint gives the shapes new Freeform numbers as well.)

Many thanks

Rob

Hi

I have a large table of data in Excel (called tblData) and I need to return the contents of field [value] subject to another field [GeographyID] matching any of 10 values contained in an array.

So far I have achieved this for cases of GeographyID individually using SUMIF, so

SUMIF(tblData[GeographyID],"A",tblData[value]) + SUMIF(tblData[GeographyID],"B",tblData[value]) +...

But effectively what I want is an array with the 10 values in returned by something like (and assuming my list of GeographyID are in a named range called MYRANGE)

IF(tblData[GeographyID] = MYRANGE, tblData[value],"")

But of course that doesn't work.

Any insight much appreciated.

Ultimately what I'm trying to do is answer the question "what is the average [value] of the lowest 5 of the GeographyIDs in MYRANGE".

Thanks
Rob

Re: trying to find specific text within a cell in another worksheet returning a 3rd v

A couple of ideas. Could you add an extra column to one of the tables that converts it all to lower or upper case and then VLOOKUP that (e.g. UPPER(B2)).

Or could you try one of the various fuzzy vlookup addins that are available. E.g. http://www.microsoft.com/en-gb…oad/details.aspx?id=15011.

Rob

Re: Time difference - Excel formula

Can you try formatting your dates as a number and checking that they then appear as a number? It may be that Excel is reading them as text.

Rob

Re: Enable macros when using Workbooks.Open within one workbook to open another

Sorry shoud have said I'm using Excel XP (2002)

Hi

I'm have a 'master' spreadsheet which I'm using to open several other workbooks and then run macros within them. It all works fine except that I have to manually click 'enable macros' when the other workbooks open. I can't set my macro security to low so wondered if there was a way around this? I'm using

Workbooks.Open filename("xxxxxx"), UpdateLinks:=0

but there doesn't seem to be any kind of EnableMacro:=1 or equivalent.

Many thanks for any suggestions.

Rob

Hi

Can anyone provide advice as to how to create a polar area diagram similar to the famous one by Florence Nightingale (see http://en.wikipedia.org/wiki/Pie_chart#Polar_area_diagram or http://en.wikipedia.org/wiki/File:Nightingale-mortality.jpg)

The only option I can think of is by creating a customised filled radar chart.

Thanks
Rob

Re: Change default chart colours using vba

Junho

Thanks. Yes I tried that first but my Excel doesn't seem to save the changes. I think maybe it loads a default set for all users on the network when we launch Excel. That's why I wanted to try the VBA route in the personal.xls.

I've tried sub auto_open using worksheets.color(16) = RGB(x,y,z) etc but this doesn't work as part of personal.xls because the new workbook book1.xls opens after personal.xls.

At the moment I've set things up so that I have a template that I can access using File... New... but this isn't ideal.

Rob

I would like to change the default chart colours used in Excel (XP). I know the simplest way to do this would be to save a file called book.xlt in the xlstat folder but my employer's security policy prevents me from doing this.

I wondered instead if it would be possible to put some code in personal.xls (which I can edit) that would change the defaults, but I don't know how to do this. Ideally I also want to make some other changes to the chart defaults such as background colour and axis font.

Many thanks for any suggestions.
Rob

PS also tried changing the default chart type but it just resets to the original.

Hi

I am trying to setup a default chart type. For the title I want to set it by default to be:

"Chart of [x axis title] and [y axis title]"

I know how to set the title to refer to a cell but the x and y axis titles are not from cells.

Please could you tell me if there is a way to capture the current x and y axis title in a formula?

Thanks
Rob

Re: Import tab delimited file with more than 255 variables

Yes, it is a one-off. I was hoping there was some way to use the import wizard to just pick up the fields I want, but it seems not.

Thanks for the code. I will use this if I can't find a more straightforward way.

Rob

I have a very large tab delimited file (94mb) that I would like to import into MS Access (I've tried with the 2k version but have 2002 on another PC if this makes any difference). The tab file has 24,000 records and 1300 variables (fields). Access seems to have a limited on the number of fields of 255.

I don't actually need all the variables - just a subset of around 20 - but in the import wizard it just won't show anything past the 255th variable for me to be able to select that and hide others.

Can anyone help with a workaround please?

Many thanks,
Rob

Re: if statement in pivot table calculated field

Good idea. Although this would mean, I think, that I wouldn't be able to have a "count of ID" alongside it to demonstrate the proportion that would be worse off. But that is a good suggestion for a workaround - thanks.

If anyone know a workaround within the calculated field, that would be useful for future reference.

Many thanks,
Rob

I've got a data item that I want to use in a pivot table that has a range of values from about -1000 to 3000. (This refers to the amount that individual people will be better or worse off under a financial change i'm modelling, and the field is called 'difference'.) I want a pivot table data item that will show the number of people who will be worse off.

I tried creating a calculated field (Called numworse) with a formula "If('difference'<0,1,0)" I then would use this as the data item. Excel seems to accept the formula ok, but then my pivot table just contains zeros.

Any ideas? The data is being pulled in from an Access database if that makes any difference.

Rob

Re: Error with MAX function in Access expression

I need help! Or more specifically, I'm not where to put the function code.

I assume once the code is in the right place I can just use the functions like any other (such as nz, Len, etc) ie. use mp_max in place of where I had Max in my code?

Thanks for your help.

Rob

I've built up an excel worksheet that did some manipulation on a small data table (in excel). I now need to repeat this on a really large data set and so am trying to translate my excel formulas into a (series of) Access queries.

I'm having a problem with one particular one. My query expression is:

Grantcont1: IiF([residual income]<26500,ROUND(ROUND(MAX([residual income]-17500,0)/6,0),0),1500)

But it reports the error "The expression you entered has a function containing the wrong number of arguments", and this appear to relate to the use of MAX.
I basically want the MAX function to return the highest of [residual income]-17500 or zero. I could use another IIF statement but this isn't very efficient and I have some others where I have 3+ arguments for the MAX function.

Can anyone help please?

Re: synchronizing two cells in different sheets

Norie,

cell C11 in the diploma sheet. I had recorded a macro and tried to put in some of the code from that. I obviously messed it up.

What you have suggested works thanks. My mistake was to try using the .Select rather than just referring to the cell directly.

For completeness, and for anyone else that ever looks this up, I now have:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("C11")) Is Nothing Then
  Exit Sub
 Else
  Sheets("diploma data").Range("C11").Value = Target.Value


 End If
End Sub

in the code page for the 'degree data' sheet. I just now need to put the same code on the diploma sheet (with the appropriate references changed).

Many thanks,
Rob

I've got two sheets that have very similar layouts. In cell C11 I have a dropdown box using data validation that allows the user to pick between three values: A, B or C (which are contained in other cells in the sheet).

What I would like to do is have the value of the two cells C11 kept sychronised between the two sheets when the user changes either of them.

I tried writing a macro using Sub Worksheet_Change something like this:

Private Sub Worksheet_Change(ByVal Target As Range)
 If Intersect(Target, Range("C11")) Is Nothing Then
  Exit Sub
 Else
  Sheets("diploma data").Select
  Range("C11").Value = "'degree data'!RC"
  
 End If
End Sub

(and several permutations on the same idea) but I couldn't get it to work. Any ideas anyone, please?

Thanks,
Rob

Re: find upwards until a non-blank cell

I had a go at writing a vba function to do this. It doesn't work, mainly because I know virtually nothing about vba syntax. Any thoughts on how this could be made to work, please?

Function offsup(test As Range) As Range


 Do While IsEmpty(test) = True


    test = test.Offset(-1, 0)
    Loop


offsup = test


End Function

The idea is that the function would be given a cell value (e.g. test = B9). It would check if B9 is blank and if it is would change test to b8 and check if that is blank, and so on. when it finds a non-blank cell it returns this as the result of the function.

Re: find upwards until a non-blank cell

Roy,

I'm not sure you have understood what I want to do. I want the vlookup in A15 and A16, which refer to B15 and B16 to use the value in B14, because b15 and b16 are blank.

I can get rid of the #n/a in A17 by changing the lookups in column A to VLOOKUP(left(B18,3),$G$2:$H$4,2,0).

But ignoring that for a minute, what i need to do is have something like VLOOKUP(AUTOOFFSET(B18,UP),$G$2:$H$4,2,0), where AUTOOFFSET would be a function that would check if b18 is blank, and if so would keep looking upwards (b17, b16, etc) until it found a non-blank cell.

Rob