See the attached.

You might also want to study Excel implicit intersection

Attached Files

Mary_4.xlsx (13.8 KB, 8 views)

I'm sorry, but I do not understand.

In other tables, I have used an explicit intersection. In that case, I used "name from selection" for the entire table and named ranges were created for each row and each column. Then I used an "intersection" reference for whatever cell I wanted to access.

Please explain how(if) an implicit intersection.

I also do not understand (what you write in your second sentence).

I regret, but I think it is time for me to stop.

xor, I must be going bonkers, but when I read your 11:01 post, all I saw was "implicit intersection" with no file attached.

Anyway, I appreciate your efforts, but you seem to be using a table as well.

Now, at least I know what I was doing is called "implicit intersection" and I will be able to research it further.

Thank you.

I am not using a table. See how col.1 and col.2 in file Mary_4 are defined.
Try to convert A1:F13 to a range and see that it doesn't change anything in columns I and J.

UPDATE: with xor's suggestion, I researched implicit intersection. The answer was not to be found there. However, I was able to use explicit intersection because my non-table was already set with named ranges for columns and rows.

This would have been some work had I not already it set up this way.

So, thanks again, xor.

In my previous posting, at end of it I explained, how to use dynamic Named Ranges to do what you want!

In attachment I redesigned your named ranges, and your formulas started work in way you did want. Additionally I added another range which includes data from both columns, and a couple of columns using this range as example.

Attached Files

Mary test 9-24-18.xlsx (12.2 KB, 9 views)

Arvilaanemets,

I just tried the dynamic range as you showed previously and I worked great.

and xor,

You did dynamic ranges as well. I am sorry I misread your instructions.

Just out of curiosity...Most descriptions of how to do dynamic ranges use offset or index. Your way is so much easier. Maybe I just missed this in my searches.


Thank you both.

Oh boy,

First he giveth then he taketh away.

The dynamic named ranges worked well on their own (first) sheet. However, from a different (second) sheet I had been using an explicit intersection to pull from the first sheet. Now, these values on the second sheet are all "null."

This is very specific technique and applicable at certain limitations only (essentially it is what I used before Tables were introduced to get same behavior as [@FieldName]). But it can get quite complex on it's own. E.g. you can combine several such dynamic ranges to create new dynamic ranges, or you can create a dynamic range or value, which reads data from row in one sheet additionally depending on data in some another sheet.

Did you use same named ranges there? Maybe you need different ones for this "explicit intersection" stuff? I can't look at moment what it does - I don't have Excel available currently.

I created named-ranges for the same columns, but this time non-dynamically and they worked as before.

I may be creating more work by needing two names for each column than I am saving. But I am very glad to know that this is available.

Thank you for your help.