Fill previous and next Cells based on middle cell

[jilaba]

B2 has Dropdown list referring to a namerange A1:A9 (text entries)

How to fill B1 & B3 based on B2 selection sequentially

Ref. attachment.

[Shijesh Kumar]

use index and match..

check the attachment

[zbor]

Wasn't you look for same problem few days ago?
It was solved... It wasn't good or?

http://www.excelforum.com/excel-new-...ous-cells.html

[jilaba]

You are right. That solution used VB... i don't know it.

Wasn't you look for same problem few days ago?
It was solved... It wasn't good or?

[jilaba]

Thanks :
Problems
When brick is selected in B2, B1 should show Electricity.
When Electricity is selected in B2, B3 shows #REF (supposed to show brick).

ie., the list is like a circle.

use index and match..

check the attachment

[zbor]

Attachment 38625

OK, here's moderated above file (because it doesn't work with first and last cell)...

Is it problem to add vritually Brick and Electricity (look A1 and A10, are not empty only white)...

This way formula works without VBA

[Shijesh Kumar]

use this in B!

Please Login or Register  to view this content.

and this is b2

Please Login or Register  to view this content.

[zbor]

Yes, this is nicely

[jilaba]

Great.... works fine.... Thanks Shiju.

use this in B!

Please Login or Register  to view this content.

and this is b2

Please Login or Register  to view this content.

[jilaba]

Thanks...........

Attachment 38625

OK, here's moderated above file (because it doesn't work with first and last cell)...

Is it problem to add vritually Brick and Electricity (look A1 and A10, are not empty only white)...

This way formula works without VBA