Forum Discussion
conditional formatting
- Jun 30, 2024
LenB The syntax =FUNCTION([@Jan]) is actually a structured table reference and I would recommend you to learn about them. It means that 'the function' references the cell on the same row @ in the column called Jan
That way, you don't need to worry about what cell you are referencing. But it needs a bit more if you want to make such a reference absolute like $B$15. To make a reference like [@Jan] absolute, you need to include the table name and repeat the column name, separated by a colon and with an extra pair of square brackets. It would look like Table1[@[[Jan]:[Jan]] . This reference can be copied to the right and it stays fixed on Jan. And to make a bit more awkward a formula that should first average Jan:Feb then Jan:Mar then Jan:Apr etc. when copied to the right, looks like this:
=AVERAGE(Table1[@[Jan]:[Jan]]:[@Feb])
The attached file contains an example. Read more about structured table references here:
And there are many more articles to be found on line that deal with this type of references.
Thank you, I finally got that to work.
First attempt when I went to do the =average formula and clicked on the D15 cell, it came up =AVERAGE([@Jan],) instead of D15. There must be some embedded formula in their template. Column E is [@Feb], F is [@Mar] and so on. It won't allow me to freeze the [@Jan] cell with a $ so I'm copying across and adding what I need to each cell to make it right, then I'll copy everything down.
It seems to be working properly so, thank you again.
LenB The syntax =FUNCTION([@Jan]) is actually a structured table reference and I would recommend you to learn about them. It means that 'the function' references the cell on the same row @ in the column called Jan
That way, you don't need to worry about what cell you are referencing. But it needs a bit more if you want to make such a reference absolute like $B$15. To make a reference like [@Jan] absolute, you need to include the table name and repeat the column name, separated by a colon and with an extra pair of square brackets. It would look like Table1[@[[Jan]:[Jan]] . This reference can be copied to the right and it stays fixed on Jan. And to make a bit more awkward a formula that should first average Jan:Feb then Jan:Mar then Jan:Apr etc. when copied to the right, looks like this:
=AVERAGE(Table1[@[Jan]:[Jan]]:[@Feb])
The attached file contains an example. Read more about structured table references here:
And there are many more articles to be found on line that deal with this type of references.
- LenBJul 11, 2024Copper ContributorI finally was able to get back to working on the spreadsheet. What you suggested works perfectly. Thank you so much and thanks for the link to the structured table references. I'm 63 and have a lot to learn, but it's always satisfying when all goes how it should.
- Riny_van_EekelenJul 11, 2024Platinum Contributor
LenB haha! And I’m turning 62 in a few weeks. Never old enough to learn 😃
- LenBJul 12, 2024Copper ContributorThat's right! This old dog can still learn some new tricks, but you can learn AND teach...Awesome!
Have a great weekend and happy birthday.