Link from a cell-reference and make the answer a column formula
When we set up a sheet we did not add an 'industry' column. I can link to a metadata sheet to get that information and link it to a single cell on my sheet . . . however, I need to have that value appear on every line of a column (so that reports across with the info across industries can be sliced differently). I created a helper column to bring in the cell thinking I could create a column formula to always bring back the absolute value of that cell, but you can't make a column formula from an absolute.
I am open to other ideas. I know I can create an automation, but ideally I would like to use a formula so that I can add the new column and formula at my blueprint level and not have to touch each active project after we push it out through control center.
Ideas?
Best Answer
-
My understanding of your requirements is:
- you have an Industry column in your sheet (sheet A) and you need each row to contain the industry that is specified in a cell in another sheet (sheet B). All rows will have the same industry - from that one cell.
- it must be referenced so if the value in sheet B changes, all references in A update.
- the formula must be a column formula so you can't cell link one cell and then reference that in all the others.
If this is correct, there is a sneaking trick you can try.
- Go into your cell, type =SUM(
- (I know this sounds wrong, but trust me)
- The pop up will appear to set up a cross sheet reference
- Set up a reference to the cell in sheet B with the industry in
- Your formula will now be something like =SUM({new reference}
- Now delete the SUM( part
- You will end up with ={new reference}
- Which you can make into a column formula
Answers
-
What if you put the link to the Industry value in the sheet summary. Then made your column formula reference the sheet summary?
-
I can't link to another sheet in the summary, that I have found so far. I am trying to automate capturing the information so it doesn't have to be touched as we spin up new projects. Any ideas you have are appreciated.
-
I also tried to do an automation where I link the Industry into the Industry Helper column, and then automate completing a cell that if Industry is blank I always make the cell =[industry helper]$1 but it only reads it as text and doesn't convert it to a formula. The challenge is that I need EACH row to have the industry, not just have the single instance on the page.
-
My understanding of your requirements is:
- you have an Industry column in your sheet (sheet A) and you need each row to contain the industry that is specified in a cell in another sheet (sheet B). All rows will have the same industry - from that one cell.
- it must be referenced so if the value in sheet B changes, all references in A update.
- the formula must be a column formula so you can't cell link one cell and then reference that in all the others.
If this is correct, there is a sneaking trick you can try.
- Go into your cell, type =SUM(
- (I know this sounds wrong, but trust me)
- The pop up will appear to set up a cross sheet reference
- Set up a reference to the cell in sheet B with the industry in
- Your formula will now be something like =SUM({new reference}
- Now delete the SUM( part
- You will end up with ={new reference}
- Which you can make into a column formula
-
OOOO, I will try it! Thank you!
-
@KPH that worked!!! Genius! @kaelynbister FYI
-
Awesome. 😉our little secret cheat!
-
@KPH it is a great cheat! Fun fact…it does not work with global rollout or blueprints, so it takes manual touching. A little annoying, but thank you for finding a workaround.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.7K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!