Dynamically defined names

[spacely]

Hello,

I was looking into "dynamically defined names", but am not sure that's what will work for me...

I have values changing in column A, and based on that value, the cells in column B (same row) should have a particular defined name. But there could be several possible dynamically defined names assigned. So, i.e. if A4 = 4% and A11 = 19%, the assignment would find them both, and assign B4 as MyFirstOne, and B11 as MySecondOne.

Would like to avoid VBA, but it's OK if we need to go there

Thanks a lot!

Dave

 

[mikerickson]

How can we tell which cell in column A meets the criteria for MyFirstOne?

 

[spacely]

By the value in A4. So, if A4=4, then the name will be MyFirstOne.

 

[mikerickson]

What if A4 contains 3?

 

[spacely]

Then there is no defined name at all. So I need an if-then or case statement checking for specific values, what row they occur on, and making a defined name based on that.

 

[mikerickson]

If the name MyFirstCell refers to =INDEX(Sheet1!$B:$B, MATCH(4, Sheet1!$A:$A,0), 1) then MyFirstCell will be the cell in column B next to the first occurrence of 4 in column A.

 

[spacely]

I put that in the "refers to" box while defining a named variable, but nothing got named. I have my column A and B like this:

1 g
2 f
3 r
4 t
5 y
6 u
7 i
8 o
9 p

Is that not the right way to implement it?

 

[mikerickson]

If you fill in the name and refers to boxes and then put =MyFirstOne in a cell, it should return the value from that cell.

 

[spacely]

I just get a #N/A. Can I upload this excel sample somehow?

 

[mikerickson]

Does the Sheet1 in my formula match your situation.

No, this forum doesn't have the capability for attaching workbooks.