Linking Cells from one sheet to the next

Copper Contributor

I created a document to help me track project docs/progress that's basically just a live checklist. I created a second sheet to try to track schedules and linked the first column so that my project names would transfer from the first to the second sheet, but when I add a new project to sheet 1 the first column shifts down the way I want it to on sheet 2 however the information in columns B and on stays put. Is there a setting that I can change so that my rows shift on the second page so the schedule for the project stays with the project it belongs to?

4 Replies

@Chrissy_Harger 

 

I suspect it's not a "setting" that you really are looking for, but rather a revision to however it is that you've linked the one sheet with the other. Is it possible for you to post your actual workbook, just making sure that any proprietary information is not present? Or a representative replica with altogether fictitious data....

@mathetes  I'm fairly certain the job names are a matter of public record so I deleted everything but those from a copy. The colored cells on sheet 2 are about three lines above where I want them after adding several jobs so I'm looking for a way to force the rows to shift down when I add to sheet 1 instead of having to manually shift it as I add jobs.

@Chrissy_Harger 

So as I understand you want the values in Sheet 2 to update whenewer you update the projects in Sheet 1. I guess you are ordering the project list or insert projects between other projects.

 

the problem is your project list on first column of Sheet 2 is dynamic, which means they are updated as soon as values on sheet 1 has changed however your values on other columns are static. 

 

There is no setting for that. You need to create a complete dynamic sheet where you get those values dynamically too.

@Chrissy_Harger 

 

What @erol sinan zorlu has pointed out is accurate. You really are only linking the first column, not the other data.

 

So you will need to rethink how you've organized things here.

 

Is it possible for you to basically work on a single database, on Sheet 1, and then have Sheet 2 become an extracted view, a filtered view perhaps? It's hard to believe that you always are going to want to look at everything at once....

 

I've created a rough example--using the new FILTER and SORT functions on a new Sheet2. These functions do need the most recent version of Excel in order to work, so if they don't work on your computer, I'd recommend getting the update.

 

Even assuming this does work on your computer, it is not what I'd consider finished, in that you might well want to refine further what you see on Sheet 2. The basic idea is that you ENTER all information on Sheet 1...all of those monthly status updates...and Sheet 2 is purely for output viewing. Then you  use FILTER to define the rows and columns you want to see. It's set up so that it automatically sorts the projects in project number order, and will keep all data on any given row in the correct row.

 

Here's a YouTube video that explains these new Dynamic Array functions (they replace the dynamic function you'd used)....https://www.youtube.com/watch?v=9I9DtFOVPIg

 

 

 

Learn everything about our brand-new Dynamic Arrays and how you can use them to build advanced spreadsheets. Arrays (CSE) have long been present in Excel, but were limited to power users. With Dynamic Arrays we have rebuilt the calc engine, effectively turning all formulas into array formulas ...