Excel Help - Please

mercman1969

Hi folks,

I am putting together a small excel document and am very new to the whole formula thing

Initially I would like to know if it is possible to obtain a total (by number) of rows that has a hyperlink
?

I have a number of rows containing names (120 total). Some of these have a certain skill and i have highlighted that skill by attaching a hyperlink to their name/s

At the top i have a single cell that i would like to obtain a number value of how many have the skill denoted by a hyperlink

is this possible?

droopsnoot

The easy way I'd do it is to have another column where each cell contains the following formula, or one very similar to it:

=IF(ISNUMBER(SEARCH(".",D6)),1,0)

where "D6" in this case is the cell that contains either nothing, or your hyperlink. You now have a cell alongside that will either contain "1" if the hyperlink field contains a "." (fair to assume any external link will do so), or 0 otherwise. You can then sum that column to get your count.

Probably a much easier way of doing it, I only have Excel 2003 and not used it in depth. No doubt someone else will be along with more suggestions.

InsideInsurance

Are they all hyperlinks to a common site or such? If they are then the easy way to do it is to treat it like text and do:

=CountIf(C1:C10, "*domain.com*")

To actually test for a true hyperlink rather than just a common piece of text is more tricky without using macros/ VBA

bod1467

Alternatively, if they links are to different websites then (using the same idea) ...

=COUNTIF(A2:A6,"://")

This assume the hyperlink is visible and includes the http:// part. If the only information seen is the person name and that some names are clickable, then I don't know.

tom15387202

Use a custom function

Function countLinks(r As Range)
For Each c In r
If c.Hyperlinks.Count = 1 Then countRng = countRng + 1
Next c
countLinks = countRng
End Function

Count the links using the formula

=countLinks(A1 : A10)

mercman1969

tom15387202 wrote: »

Use a custom function

Function countLinks(r As Range)
For Each c In r
If c.Hyperlinks.Count = 1 Then countRng = countRng + 1
Next c
countLinks = countRng
End Function

Count the links using the formula

=countLinks(A1 : A10)

How do you use a custome function?

mercman1969

tom15387202 wrote: »

Use a custom function

Function countLinks(r As Range)
For Each c In r
If c.Hyperlinks.Count = 1 Then countRng = countRng + 1
Next c
countLinks = countRng
End Function

Count the links using the formula

=countLinks(A1 : A10)

Tom,

Many thanks...I have sorted it (of sorts)

I have used 2 cells merged together to make a single cell

When i put in your formula i am getting the number that is correct but assuming a single cell for each item....

How would i divide the number by 2 in order to obtain the correct number?

TrickyDicky101

I would think you can shorten that to:

Function CountLinks(r As Range)
CountLinks = r.Hyperlinks.Count
End Function

which won't double count merge cells containing links.

mercman1969

TrickyDicky101 wrote: »

I would think you can shorten that to:

Function CountLinks(r As Range)
CountLinks = r.Hyperlinks.Count
End Function

which won't double count merge cells containing links.

TrickyDicky......

Many thanks....worked a treat

how little i know about excel.......

Heedtheadvice

=countLinks(A1 : A10)/2 ?

or in the vba "countLinks = countRng/2"

but best to probably not have a merged cell for this sort of thing as it will be prone to errors if the cell merging gets changed.


Ah! beaten by the post!!

As with Excel it's very powerful and there are many ways to skin a cat!!!

i.e. lots of other ways

mercman1969

My next question is..........

Can i obtain a number from a Colour?

My next field cell has 2 Colour options

Yellow
Green

I would like to obtain a number field from the amount of Green cells with a columb......can this be done?