Fill With Names Ranges

[abmyers]

Hi thanks in advance. I'm looking for help on getting excel to fill when using named ranges. I'll try to be specific. On Sheet 3. I have a table that occupies P1:X22. In the table I have named column P "COPY ITEM NUMBER", column Q "OFFER SIZE" and column R "TICKER".

On another worksheet tab, sheet 2 I have an Index formula that references the table.

In cell C15

=INDEX(COPY_ITEM_NUMBER, SMALL(etc......)

This works fine when I fill down but I want to be able to fill right. I want cell D15 to fill with:

=INDEX(OFFER_SIZE, SMALL(etc......)

Right now it seems I need to manually edit each column.

help?

 

[ukmikeb]

Hi

Try -

=INDEX((COPY ITEM NUMBER,OFFER SIZE,TICKER),SMALL(etc--),,COLUMNS($C1:C1))

copy across to D15 and E15.

hth

 

[abmyers]

Hi

Try -

=INDEX((COPY ITEM NUMBER,OFFER SIZE,TICKER),SMALL(etc--),,COLUMNS($C1:C1))

copy across to D15 and E15.

hth

Is there a way to do this without hard coding the names?

 

[abmyers]

Nevermind I got what you were saying....Use full index. Thanks

 

[ukmikeb]

Hi

The formula I specified used the area number option.
You could try -

=INDEX($P$1:$X$22,SMALL(etc--),COLUMNS($C1:C1))

that's assuming SMALL references the Row position.

hth