=INDEX =Headache...

[ourfear]

Hi Peeps,

Im working on what i thought would have been an easy spreadsheet.

Basically Im looking up data from a table, then im using the index function to sort the data.

This all works fine.e.g: =INDEX($A$1:$C$7,SMALL(IF($A$1:$B$7=$E$1,ROW($A$1:$B$7)),ROW(1:2)),3)

Which looks up the text in E1 and then lists the activities that have been associated with it on a table.

What im then trying to do is to simply amalgamate these references into one cell so:

=E1&" , "&E2

The problem being that E1 = Activity 1 and E2 = #Num! as no data has been filled in on the relavent table yet. Now the =E1&" , "&E2 wont work as it also just shows #Num!

I finally fogured out that if i place a second coloumn next to my data collector that reads:

=IF(ISERROR(E1),"Error1",E1)

Then i can still combine the cells but with ERROR1 written in the cell rather then #Num!.

Now im trying to write a macro or a function that will remove the Error1 refference from the text string in my merged cell.

Complicated...yes..make sense...hope so!!

Just in case here is a link to my work sheet:

http://www.mediafire.com/?fhjtmaau45x

Many Thanks.

Russ

 

[Andrew Poulsom]

Welcome to th Board.

Why not?

=IF(ISERROR(E1),"",E1)

 

[ourfear]

Thanks for your reply.

That is what i have been playing with, unfortunately my cell then looks like this:

Activity 1, Activity 2, , , , , , ,

There could be up to 30 cells merged into this one cell so it would get a bit messy.

 

[ourfear]

I found a VB code to remove the Numbers out of a Postcode, Im wondering now if this can be used to remove Error1 or " " out of my cell?

Function RemoveError(Rng As String) As String
Dim Tmp As String
Dim i As Integer

Tmp = Rng
For i = Error1 To Error1
Tmp = Application.Substitute(Tmp, i, "")
Next i
RemoveError = Tmp
End Function

 

[Andrew Poulsom]

To remove the #NUM! error in eg G2:

=IF(ISNUMBER(SEARCH(G1,$A$1:$C$7)),INDEX($A$1:$C$7,SMALL(IF($A$1:$B$7=$G$1,ROW($A$1:$B$7)),ROW(1:2)),3),"")

 

[ourfear]

Thats returning as a blank cell even if the text it's looking for is present.

Please feel free to download the Zip file in my original post and have a ply. Ive taken all the other pages out except the 3
relevant ones. : )

many thanks for your help.

 

[Andrew Poulsom]

Sorry. In E2 try:

=IF((COUNTIF($A$1:$A$7,E$1)+COUNTIF($A$1:$A$7,E$1)),INDEX($A$1:$C$7,SMALL(IF($A$1:$B$7=E$1,ROW($A$1:$B$7)),ROW(1:2)),3),"")

 

[ourfear]

Still get the #Num! 's I'm afraid. : (

 

[Andrew Poulsom]

In E2 that formula returns:

Activity 3 - Three

If copied to G2 it becomes:

=IF((COUNTIF($A$1:$A$7,G$1)+COUNTIF($A$1:$A$7,G$1)),INDEX($A$1:$C$7,SMALL(IF($A$1:$B$7=G$1,ROW($A$1:$B$7)),ROW(1:2)),3),"")

and returns null.

 

[ourfear]

Apologies, In E2 that formula does return Activity 3, and copied into E3 it shows Activity 4, but in E4 it becomes #Num! again.

Sorry to be a pain!!!1 : )