quickly move linked cells within the same sheet without losing linking

OshaK

I have a metrics sheet that I use to generate a document that has the cells that are linked to a master sheet. We use it to display students by year. Each year we need to rearrange them in columns in the metrics sheet because they move up one year, the seniors leave and the freshmen come in.

Is there a way to move the linked cells within the same metrics sheet without losing the linkage? When i move them from one column (one year) to a different column (different year), i either lose the linkage or if I do Paste Special, the feature 'Links to copied cells' is greyed out and i wouldn't want them to be linked to the cells i copy. I want to copy the cells and retain the links to the master sheet. Thanks!

Julie Fortney

So if the one-row sheet contains these columns, would that suffice for your document?

If so, with your new Role column on the source sheet, all you would have to do is select the new role for each person, and their info would be pulled into the correct columns on the one-row sheet above. (As well as linking the correct photos)

Here are the formulas for the Name, Name and Pager, and Name, Pager, Titles columns. Once you set these up for Resident 1, you can copy and paste them for the others, with a few tweaks.

Keep in mind the cross-sheet references refer to my sample source sheet and columns, so you will need to adjust these. I used CHAR(10) to create line breaks, but you can replace that with another separator such as " ".

Name Column

=INDEX({OshaK Resident Info - Name}, MATCH("Chief Resident 1", {OshaK Resident Info - Role}, 0))

To copy to the other name columns, be sure to update the "Chief Resident 1" to the correct role for that column.

Name and Pager

=[Chief Resident 1 Name]@row + CHAR(10) + INDEX({OshaK Resident Info - Pager}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0))

Name, Pager, Titles

=[Chief Resident 1 Name]@row + CHAR(10) + INDEX({OshaK Resident Info - Pager}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0)) + CHAR(10) + INDEX({OshaK Resident Info - Academic Title}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0)) + CHAR(10) + INDEX({OshaK Resident Info - Admin Title}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0))

Julie Fortney

Ah, I think I know what's going on. Your cross-sheet reference names in your formula don't match the names in your reference manager.

Try this:

=INDEX({Name}, MATCH("Chief Resident 1", {Role}, 0))

Genevieve P.

@OshaK

Jumping in here - it looks like you're just missing a closing parentheses 🙂

INDEX(...MATCH(..)) < this one

=INDEX({Name}, MATCH("Chief Resident 1", {Role}, 0)) + CHAR(10) + INDEX({Pager}, MATCH([Chief Resident 1 Name]@row, {Name}, 0))

Julie Fortney

@OshaK,

Are you able to share a screenshot of your sheet, (with sensitive data blocked out)?

OshaK

thank you @Julie Fortney here is the sheet I'm generating a document from. I have all Residents in one row (27 residents/5 years). All the cells are linked to the main master sheet with more info about them. Each year, i need to remove the Chief and move the rest to the left, so year 4 residents become Chiefs, year 3 - to year 4 etc and add the new freshmen to the Year 1. I need to be able to retain the links to the master sheet for those who remain (their names and photos are public, so i didn't block them.). Thanks!

Julie Fortney

@OshaK

Thanks for the screenshot. That helps me visualize what you're doing.

To make sure I understand - you have them all in one row so that you can generate a document from this information? Working with multiple rows would simplify this quite a bit, but I've run into limitations like this to, where you are forced to structure your sheet in a less convenient way.

I think we can make this a bit simpler to maintain. Is your source sheet in columns? For instance, I envision it having one row for each person with columns for name, picture, pager, and any other contact info.

Here is a sample source sheet I set up:

The good news is that we can automate some of this without you have to recreate all of the cell links. The one column you will still have to recreate cell links for is the pic column, because we can't use a cross-sheet formula to pull a photo from another sheet. That's because the formula will only pull in the photo file name, and not the photo itself.

But let's talk about the good news - you can set this up so that each year, all you will need to do is move the names into the correct Name columns, and you can use a formula that will pull in their correct contact info. (In this example above, it's the Pager column.)

Here's the formula. You can type this formula into the first Pager column, then copy and paste it into all the other Pager columns, and it should adjust the reference to the correct Name column:

=INDEX({OshaK Resident Info - Pager}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0))

As I mentioned previously, you would still need to update the photo cell links manually.

I hope this helps!

www.linkedin.com/in/juliefortney-pmp-smartsheetpartner-lssblackbelt

OshaK

@Julie Fortney thank you so much! I'm not sure I totally understand what i need to do, sorry. Let me give you more details to see how your suggestion would still work.

The ultimate goal of this struggle is to generate a single Face Sheet for all 27 residents. Is there a way to do it from multiple rows, that would be fantastic, but I only found a way to generate a single document from a single row.

So here is what I need at the end:

1. Name of the year - Chief Residdents, PGY4, PGY3 etc.
2. Headshots
3. The names, and credentials below each.

The second face sheet would have in addition the pager number under each name.

You're correct - I have a master sheet that is set up as one row per person and has way more information than we want to publicly display. The primary column is empty and the first column is set up as a drop down for a year they are in (that changes every year). Then, the column First/Last Name/Degree combined (helper column) combines those 3 separate column into one ( to fill one fillable text field per person when I generate a single document for all of them.

Should I still use the formula in the single row document? I'm also not sure what "OshaK Resident Info" refers to. Can you please elaborate?

I also tried to add my formula that combines first/last names with degrees into Primary column but it's not working there.

Thank you so much!

OshaK

Also, @Julie Fortney are you using the names in the single row document as static text? I was trying to see if i can pull them as linked cells from the master sheet with multi row document (I need to create another sheet for the faculty whose titles change often and I need to minimize the duplication to avoid human errors). Thank you so much!

Julie Fortney

The "OshaK Resident Info" reference in the Index/Match formula refers to the source sheet, which I named "OshaK Resident Info."

When you see { } in a formula, that signifies a cross-sheet reference. When you're creating a formula, you can click "Reference another sheet" to establish these references and use them in your formula.

The formula below is for the pager column on your target sheet (the one-row sheet). It is telling Smartsheet to look at the Name column on the source sheet and find the name that matches the Chief Resident 1 name on your target sheet, then pull the pager number on that row.

=INDEX({OshaK Resident Info - Pager}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0))

Julie Fortney

Yes, the names in the single row document are static text. If you want these to update dynamically, you could create a unique identifier column in your source sheet that would tell Smartsheet which name to pull into each Name column in the target sheet.

For instance, you could have a "Role" column in your source sheet that would tell Smartsheet which column that person should appear in on your target sheet. For instance, at the start of the year, Person 1 has become a Chief Resident, so you make them Chief Resident 1 in the "Role" column. The Chief Resident 1 column could contain a cross-sheet formula that tells Smartsheet to pull the name that has Chief Resident 1 in the "Role" column. Does that help?

OshaK

@Julie Fortney thank you. I added the column ROLE in my master sheet. So how do i combine

=[First Name]@row + " " + [Last Name]@row + ", " + Degree@row - from the Names column with Role and add to the target single row sheet?

for the second sheet I need to add =[First Name]@row + " " + [Last Name]@row + ", " + Degree@row and pager

for the faculty sheet i need to combine =[First Name]@row + " " + [Last Name]@row + ", " + Degree@row with their academic and a separate administrative titles.

Thank you!

OshaK

@Julie Fortney I'm trying to keep last and first names separately in the original sheet because we often want to sort them A-Z, and it becomes hard if we have first and last names as static text together. so the 'Names' column has a formula to combine the F/L names and their credentials into one.

TY!

Julie Fortney

So if the one-row sheet contains these columns, would that suffice for your document?

If so, with your new Role column on the source sheet, all you would have to do is select the new role for each person, and their info would be pulled into the correct columns on the one-row sheet above. (As well as linking the correct photos)

Here are the formulas for the Name, Name and Pager, and Name, Pager, Titles columns. Once you set these up for Resident 1, you can copy and paste them for the others, with a few tweaks.

Keep in mind the cross-sheet references refer to my sample source sheet and columns, so you will need to adjust these. I used CHAR(10) to create line breaks, but you can replace that with another separator such as " ".

Name Column

=INDEX({OshaK Resident Info - Name}, MATCH("Chief Resident 1", {OshaK Resident Info - Role}, 0))

To copy to the other name columns, be sure to update the "Chief Resident 1" to the correct role for that column.

Name and Pager

=[Chief Resident 1 Name]@row + CHAR(10) + INDEX({OshaK Resident Info - Pager}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0))

Name, Pager, Titles

=[Chief Resident 1 Name]@row + CHAR(10) + INDEX({OshaK Resident Info - Pager}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0)) + CHAR(10) + INDEX({OshaK Resident Info - Academic Title}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0)) + CHAR(10) + INDEX({OshaK Resident Info - Admin Title}, MATCH([Chief Resident 1 Name]@row, {OshaK Resident Info - Name}, 0))

OshaK

@Julie Fortney thank you so much for being so patient with me. I tried to mimick your set up, even the sheet name but I'm getting =INVALID REF error. I'm attaching Reference window as I can't figure out what I'm doing wrong. Thank you so much!

master sheet ( I'm referencing to):

metrics sheet (I'm referencing from) with

=INDEX({OshaK Resident Info - Name}, MATCH("Chief Resident 1", {OshaK Resident Info - Role}, 0))

Reference window:

Julie Fortney

Ah, I think I know what's going on. Your cross-sheet reference names in your formula don't match the names in your reference manager.

Try this:

=INDEX({Name}, MATCH("Chief Resident 1", {Role}, 0))

OshaK

this worked, thank you!, @Julie Fortney

but this one didn't:

=INDEX({Name}, MATCH("Chief Resident 1", {Role}, 0) + CHAR(10) + INDEX({Pager}, MATCH([Chief Resident 1 Name]@row, {Name}, 0))

Genevieve P.

@OshaK

Jumping in here - it looks like you're just missing a closing parentheses 🙂

INDEX(...MATCH(..)) < this one

=INDEX({Name}, MATCH("Chief Resident 1", {Role}, 0)) + CHAR(10) + INDEX({Pager}, MATCH([Chief Resident 1 Name]@row, {Name}, 0))

OshaK

thank you @Julie Fortney and @Genevieve P. It's working now! many thanks!