Link from a cell-reference and make the answer a column formula

Annaleah Morrow

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?

KPH

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.

1. Go into your cell, type =SUM(
2. (I know this sounds wrong, but trust me)
3. The pop up will appear to set up a cross sheet reference
4. Set up a reference to the cell in sheet B with the industry in
5. Your formula will now be something like =SUM({new reference}
6. Now delete the SUM( part
7. You will end up with ={new reference}
8. Which you can make into a column formula

Corey W.

What if you put the link to the Industry value in the sheet summary. Then made your column formula reference the sheet summary?

Annaleah Morrow

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.

Annaleah Morrow

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.

KPH

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.

1. Go into your cell, type =SUM(
2. (I know this sounds wrong, but trust me)
3. The pop up will appear to set up a cross sheet reference
4. Set up a reference to the cell in sheet B with the industry in
5. Your formula will now be something like =SUM({new reference}
6. Now delete the SUM( part
7. You will end up with ={new reference}
8. Which you can make into a column formula

Annaleah Morrow

OOOO, I will try it! Thank you!

Annaleah Morrow

@KPH that worked!!! Genius! @kaelynbister FYI

KPH

Awesome. 😉our little secret cheat!

Annaleah Morrow

@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.