# Modifying the INIDIRECT Formula with several arguments

#### brendan68

##### New Member
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

#### Yard

##### Well-known Member
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?

#### brendan68

##### New Member
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

##### Well-known Member
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

##### New Member
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!

#### Yard

##### Well-known Member
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

##### New Member
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

Replies
5
Views
132
Replies
1
Views
425
Replies
2
Views
175
Replies
4
Views
166
Replies
1
Views
226

1,190,852
Messages
5,983,234
Members
439,832
Latest member
Kephart87

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back