=INDEX =Headache...

ourfear

New Member
Joined
Jan 16, 2009
Messages
29
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

ourfear

New Member
Joined
Jan 16, 2009
Messages
29
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. :biggrin:
 

ourfear

New Member
Joined
Jan 16, 2009
Messages
29
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jan 16, 2009
Messages
29
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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),"")
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
Jan 16, 2009
Messages
29
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 : )
 

Watch MrExcel Video

Forum statistics

Threads
1,102,344
Messages
5,486,310
Members
407,539
Latest member
ltwkuav

This Week's Hot Topics

Top