Link cell values from a Master sheet to another sheet

Ipshita

I have a master data sheet that has numerous columns and another sheet that needs to be linked to certain cell values of the master sheet. I have hidden the excess columns in the master file that I don't need to look at while I am cell linking the values in the target sheet and saved it in this format. However, every time I click on 'link cell from another sheet' and choose the master file, it still shows me all the hidden columns and I have to scroll forever to find the right (name) and (date) and (value) columns I need to link with. Is there a way that I can save this extra time in trying to locate the cell value I need to link with in my target file from a robust file and not having to look at the extra columns?

Andrée Starå

Hi @Ipshita

I hope you're well and safe!

You could initiate the linking from the Master sheet instead.

Did that work/help?

I hope that helps!

Be safe, and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ipshita

Hello @Andrée Starå ,

Thank you for the response but no, unfortunately it doesn't help. I can't link out the cell value I need from the master sheet to the target sheet because -

1) it's not giving me an option to "link out"

2) if you mean I convert the cell value to a "hyperlink" and add that in the target sheet, that's not possible as I cannot change anything in the Master sheet (and even if I could do that, it would still be very time consuming)

Best,

Ipshita

Andrée Starå

@Ipshita

Apologies!

I meant to copy what you want from the Master Sheet and Special Paste it into the other sheet.

Important that it's in the same tab. You can't change to another tab or window.

Make sense?

Would that work help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ipshita

Hi again,

Yes I tried that too but "Paste Special" is showing as deactivated even though both master and target sheets are in the same tab :(

Ipshita

Andrée Starå

@Ipshita

How do you change from the Master sheet to the Target sheet?

Ipshita

@Andrée Starå,

I didn't exactly quite understand your question here.

I am not trying to change anything from the master sheet to the target sheet. I have a master sheet, consider that as an inventory of items for the whole country while the target sheet only pertains to certain regions of the country where I want to evaluate the existing stock of the items in just those regions, excluding the rest of the country.. makes sense so far? Now, the master sheet has numerous other columns that are not needed in the target sheet as I only need the name of the city and the stock (number of items) for, let's say Central America from the master file over to the target file and the reason these need to be cell linked is, so that for all the number of items that get added on to this range in the master inventory will also get updated in the target sheet for Central America on its own. I have saved the master file in the order of City names and Stock side by side so when I open the "link cell from another sheet" and choose the master file I can see it exactly how I have saved it, so it's quicker and easier for me to cell link but unfortunately all the hidden columns still show up and then I have to scroll over to the right name of the city column and then again to find the stock column... this is extremely time consuming and I was looking for a workaround that could bring a bit more efficiency to this process..

Thank you,

Ipshita

Andrée Starå

@Ipshita

Yes, I understand.

You could instead try this. Much quicker than cell linking.

You could use cross-sheet formulas combined with either a VLOOKUP or INDEX/MATCH structure to connect the sheets, and when you update the source sheet, it will reflect on the destination sheet.

To connect them row by row, you'd use an Autonumber Column in the Source sheet and add a so-called helper column to manually add the row id on as many rows as you think you need in the Destination sheet.

Would that work/help?

✅Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Ipshita

@Andrée Starå Yes I have used that INDEX MATCH formula before but just in this unique case, unfortunately since we cannot add any columns to the Master File (it's also used by other departments) this option is not available. The only thing that could have worked is if the cell linking was made any easier. And it's an outright suggestion for Smartsheet to improvise.

Thank you,

Ipshita