Absolute cell range when using Table nomenclature

[teacard]

I was just making a formula and as the range was in an Excel table it automatically used the nomenclature instead of saying (for example) ' F5:F38 ' it said ' Table2[sales] '

So I went with this, but pressing F4 doesn't seem to apply any formatting to the reference, and when I copy the formula across horizontally the nomenclature stays relative.

I'm not sure what I'm doing wrong here, is there a different way to create absolute references other than F4 when using table nomenclatures?

Thanks.

 

[Smitty]

See: Absolute Formula References in Structured Tables | Excel Campus

HTH,

 

[Weazel]

you can try putting the field names in double square brackets and separating the range with a colon.

=SUMIFS(Table1[[sales]:[sales]],Table1[[name]:[name]],H1)

 

[teacard]

Cheers, formula currently looks like -

=SUMIFS(Table2[Sales],Table2[SalesRep],$J15,Table2[Region],K$14)

But the table references keep shifting.

I'll have a look at the link Smitty posted... I'm guessing this is basically a no though? (There isn't an easy way to make table nomenclatures absolute)

 

[teacard]

Hi OK, just had a play with this and it works but it's pretty convoluted...

Do either of you actually bother with this? Or would you just turn off nomenclature?

Cheers

 

[Weazel]

I think Mike explains it better then I can here:

Excel Magic Trick 1038: Make Table Reference Absolute With Colon and Double Square Brackets - YouTube

Whether you leave it on or turn it off, I suppose, is personal preference.

I probably don't use it often enough to know all the particulars of writing nomenclature formulas, and if I'm in a hurry I usually turn it off.....but that's me.

 

[Smitty]

The method referenced in the link is easier than using INDIRECT or INDEX, although it still is a pain. For what it's worth, the Excel development team has been asked to extend F4 Absolute/Relative toggle functionality in the next version, so it is something that they're considering how to implement.