Welcome!

Creating more than One Link in from a sheet to another sheet

I am trying to get a sheet to link into another however we want to be able to use the sheet over and over again and have it where it will link in new rows.

Is there a way to do this?

Please Help

Thanks

Find more posts tagged with

Sheets

Cell linking

Comments

Chris McKay

Hi Justin,

You'll need to use a formula with a cross-sheet reference instead of a cell link.

Depending on how the source sheet is structured and the information contained (e.g. are there a fixed number of rows/cells or will new rows be continually added to this sheet as well) will determine the way you go about this.

Could you please tell us a little more about what your sheets look like?

Kind regards,

Chris McKay

justin46491

I have attached two files for you to assist me with. I would like the Project Check in sheet to be the sheet we keep using over and over again. The Project Information Sheet is the sheet I would like to use as the master.

I also would like to use the Project Check in sheet as a URL link from another sheet and then link it to the project information sheet as well.

I have attached a copy of where I would like to use the URL Link as well for the Project Check in form for each project.

Project Information.PNG

Master Sheet Project Name.PNG

Project Check in.PNG

justin46491

Chris,

Is there anything I can do for the information I provided above?

Chris McKay

Hi Justin,

So just to be clear (as you've not clearly included the sheet names in both images):

The blank sheet is your Project Information sheet
The sheet with PROJECT CHECK IN in Column 6 is your Project Check In sheet

Assuming this, is the following what you're looking to achieve:

Project information is entered into the project sheet
A new Project Check-in sheet is created (from a template) for each new project
You would like the information in the Project Check In sheet to be pulled from the corresponding row in the Project Information sheet

If this is correct, you will first need to add a unique identifier column into the Project Information sheet. Essentially a Project Code. This will ensure that you only one possible row can exist for each project. This would prevent issues with the one Contract Holder (Firm Name) having more than one contract. Maybe use an Autonumber column?

So if I'm on the right track, you'd want to do the following in your Project Check-in sheet template:

Create a Project Code cell (with a title to the left)
Now for each of the data fields, you'd want to use a formula containing cross-sheet references to find the row with your Project Code and then the corresponding column with the data you're looking for.
If we use Contract Holder (firm name) as an example:

=INDEX({Contract Holder Range}, MATCH([Project Code]1, {Project Code Range}, 0))

Where [Project Code]1 is the cell in your Project Check In sheet that has your Project Code

Kind regards,

Chris McKay

justin46491

Chris,

When I put the following formula in to the project check in sheet because that is where I would like all the information to go into from the Project Information Sheet I am getting an INVALID REF

Here is the formula:

=INDEX({Project Information Sheet Range 1}, MATCH([Project Code]1, {{Project Information Sheet Range 2}))

justin46491

Chris

I actually got it working now. I do have one more question. Will the cross reference information work if we fill out a form rather than a sheet?

Chris McKay

Hi Justin,

Glad to hear you got things working. Yes a sheet that is populated via a form will still work.

Kind regards,

Chris McKay

Sign in to join the conversation!

Quick Links

Trending discussions

Can You Reorder/Reorganize the Card View Lanes in a Report?
Are we able to drag and drop the card view lanes in a report to reorder the appearance of the lanes? I know this feature is available on the source sheet, but it doesn't appear to be functioning at the report level. I'm working on a report that doesn't need to see every person listed in a Contact column on the source sheet…
Conditional formatting for dropdown with multiple options and OR condition
Hi experts, I have a problem with a conditional formatting rule not performing as expected. I have a dropdown field called "Transit country", and multiple country options can be added from a list (not blurring out the actual options as I'm sure we're all aware of current travel concerns). I've used Contains as it's…
Portfolio Reports
Hello! Has anyone run into an issue with Portfolio Reports similar to the below and has a suggestion? We have a portfolio report set up based on the portfolio and it is bringing in sheets automatically based on the templates we have in the portfolio. Whenever we log out of Smartsheet and log back in, the report resets…