Modifying the INIDIRECT Formula with several arguments

[brendan68]

Hi,

I am trying to use the Indirect formula to copy across my worksheet data that appears down the page. I am having trouble figuring out what arguments I would need to tell the formula to ignore its orginating cell but copy all other cells across in the range. Here is my example

A1 B1 C1 D1 D2 D3 D4 D5 D6
1 C100 C101 C102 C103 C104 C105 C106
2 C101 C100 C102 C103 C104 C105 C106
3 C102 C100 C101 C103 C104 C105 C106
4 C103
5 C104
6 C105

As you can see the data going across will ignore the orginating cell from the INDIRECT formula (A1,A2,A3 etc) but include the rest. Is this possible?

Thanks

 

[Yard]

Hi and welcome,

I really can't follow either what you have or what you want to get.

A1 B1 C1 D1 D2 D3 D4 D5 D6
1 C100 C101 C102 C103 C104 C105 C106
2 C101 C100 C102 C103 C104 C105 C106
3 C102 C100 C101 C103 C104 C105 C106
4 C103
5 C104
6 C105

Are these the cell references you want to use in the INDIRECT function?

What are these values?

Little more clarity, please!

 

[brendan68]

Hi Yes sorry they are cell references. Those values are the index numbers. Apologies for the confusion. The C100, C200 etc are example part numbers. If C100 is in the orginating cell then i dont want that part to appear across the page when I place the others next to it using the Indirect formula. Basically the others are alternative parts for that number

 

[Yard]

Nope, that doesn't help, sorry! Does your example show what you have or what you want to have?

How are you making C100 appear in that cell?

And by what rules do you want the other values to appear?

 

[brendan68]

Nope, that doesn't help, sorry! Does your example show what you have or what you want to have?

How are you making C100 appear in that cell?

And by what rules do you want the other values to appear?

Okay I have a list of part numbers in cell B2 as follows for example:

C100
C101
C102
C103
C104
C105

I need these numbers to appear going across the worksheet next to each part number from cell F1 which I can do using the formula =INDIRECT(ADDRESS(COLUMN()-3,2)) so:

C100 C101 C102 C103 C104 C105

my problem is that i do not want C100 in cell B2 to appear going across:

I need to add an argument to the formula to tell it to ignore this cell likewise for the others. So for part number C103, In cell F1 going across I need it to copy all others in this range but not C103:


C100
C101
C102
C103 C100 C101 C102 C104 C105
C104
C105

Im not sure if an INDIRECT formula is the best way to go about this as I would inevitably have to change the orginating address anyway Ithink. But do you have any advice as to how I can do this otherwise a manual process may take up most of my lifetime as there are 3000+ part numbers to go through!

Thanks for your patience!

 

[Yard]

Try this in C2 and copied down/across

=INDEX($B$2:$B$7,COLUMN()-COLUMN($C2)+1+COUNTIF($B2:B2,INDEX($B$2:$B$7,COLUMN()-COLUMN($C2)+1)))

 

[brendan68]

Try this in C2 and copied down/across

=INDEX($B$2:$B$7,COLUMN()-COLUMN($C2)+1+COUNTIF($B2:B2,INDEX($B$2:$B$7,COLUMN()-COLUMN($C2)+1)))

Nice one mate absolutely brilliant. Cheers