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
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