Formula instead of cell linking?

JLC

Hi all, please note I've never used any functions like this so not sure if I'm asking in the right way. I have two sheets:

Sheet 1 includes the following columns:

- Project # 

- Start Date 

Sheet 2 includes the following columns:

- Project # 

- Start Date (blank, needs to be filled with formula)

How do I build a formula in Sheet 2's "Start Date" column stating that if the cell in Sheet 2's "Project #" column matches anything in all of Sheet 1's "Project # column", return the value of Sheet 1 "Start Date" from the same row?

So for example, in Sheet 2, I have a blank "Start Date" in my row with "Project #" being 19-001. I want the formula to search through Sheet 1's "Project #" column for the match of 19-001, then display the corresponding start date.

Hopefully that makes sense, please let me know if there's any additional information I can provide to make this work. Thank you so much in advance for your help! This will really help us automate some work instead of having to cell link in a series of dates each time a new project gets added. 

Lauren B

You can use an INDEX/COLLECT formula with sheet references to accomplish this. For example, if you were indexing a column within the same sheet the formula would look like this:

=INDEX(COLLECT([Start Date]:[Start Date], [Project #]:[Project #], [Project #]1), 1)

But since you need to reference the other sheet, you would replace the applicable values with sheet reference values (as you type in the formula, a "Reference Another Sheet" option will appear). It will look something like this where "Sheet 1" is the actual name of your sheet ([Start Date]:[Start Date] will become Range 1 and [Project #]:[Project #] becomes Range 2):

=INDEX(COLLECT({Sheet 1 Range 1}, {Sheet 1 Range 2}, [Project #]1), 1)

Here's a help article with more on referencing data between sheets: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets

Joshua Fargason

I've always used a VLOOKUP function for something like this, not sure if that's more or less intensive than the INDEX/COLLECT solution @Lauren B suggested above.  

It works like this: You'll type =VLOOKUP( then reference what you want to look up, in this case the Project #.  You'll then specify where to look, which will be your Project # AND Start Date columns.  You can pick a name for that selection (I've used "Sheet 1 Array" below).  You'll specify that you want the 2nd column's value listed if the Start Date column is adjacent to your Project # column.  If it isn't, you'll have to put in the number of columns to the right of your Project # column the Start Date column is.  Put in false to tell it you want an exact match.  

It should look like this:

=VLOOKUP([Project #]@row, {Sheet 1 Array}, 2, false)

Again, not sure if that's easier than the solution above, but it's what I've used and had work well.  

JLC

Hi there, thanks so much for trying to help! Here is the formula I've tried, and it's giving me an incorrect argument set error:

=INDEX(COLLECT({Project list - Start date}, {Project list - Project #}, [Project #]2, 1))

Where "Project list - Start date" and "Project list - Project #" are the ranges as described from Sheet 1. To get those, I used the "Reference Another Sheet" option and clicked the column header to reference the whole column. So the first reference range is selecting the whole start date column, and the second reference range is selecting the whole project # column.

Any tips? Thanks SO much.

 

 

JLC

Oh that worked a treat, thank you so much!!! Really appreciate the help.

Lauren B

Looks like you found a good alternative solution, but just for reference, I think your extra end-paren is giving you an error message with this formula. Either way, looks like you are good to go!

JLC

Thanks Lauren! I'll give your fix a go too though as it would be helpful for others viewing the source sheet to not see a whole range of columns (18 at this point) with cell links out when using VLOOKUP. Any chance you know how I can avoid errors when moving the source information? The sheet it comes from is a master project list and once the project is closed we move it to another sheet, but still of course want to retain the information. Doesn't seem to stick when I do as the formula doesn't know I've moved it - returns a NO MATCH error. Due to the sheer volume of rows we work with, it's not an option for me to simply keep closed projects on the same sheet. 

Thank you again!

Lauren B

No problem. Are all of your closed projects in one archived sheet? If so, you can add an IFERROR component. This basically says if there is no error in the Active Projects sheet (because the row has not been moved yet), then give me the Date value; however, if there is an error (because the row has been moved to the archived sheet), search the Archived sheet instead and give me the Date value. It would look something like this:

=IFERROR(INDEX(COLLECT({delete2 Range 1}, {delete2 Range 2}, [Project #]1), 1), INDEX(COLLECT({Copy of _delete2 archived projects Range 1}, {Copy of _delete2 archived projects Range 2}, [Project #]1), 1))

JLC

Hi there, I got your original formula to work with your suggestion! However, can't get the updated version with the IFERROR to work.

=IFERROR(INDEX(COLLECT({Project list - Start date}, {Project list - Project #}, [Project #]7), 1), INDEX(COLLECT({COMPLETED PROJECTS Project #}, {COMPLETED PROJECTS Start date}, [Project #]7), 1))

This is giving me the #INVALID VALUE error message for the instances where there would be an error (project exists only on the archived "completed projects" sheet. There is no error message for the instances where the project exists on the original project list sheet. Any tips? Hoping it's another case of too many parentheses? Haha.

Thank you again.

Lauren B

Yes, it looks like you have your ranges flipped for the archived sheet. The first range indicates what column you want to pull the value from (i.e., Start date). The second range is the column that it is searching against in both sheets (i.e., Project list).

You have:

=IFERROR(INDEX(COLLECT({Project list - Start date}, {Project list - Project #}, [Project #]7), 1), INDEX(COLLECT({COMPLETED PROJECTS Project #}, {COMPLETED PROJECTS Start date}, [Project #]7), 1))

Should be:

=IFERROR(INDEX(COLLECT({Project list - Start date}, {Project list - Project #}, [Project #]7), 1), INDEX(COLLECT({COMPLETED PROJECTS Start date}, {COMPLETED PROJECTS Project #}, [Project #]7), 1))

JLC

Hey Lauren, I should maybe set this up better - I have three sheets the project date may be located at any time. One sheet for active normal projects, one for active confidential projects, and one for closed projects. Is there another option or should I still be using the IFERROR component?

Lauren B

Continuing down this road, you could add another nested IFERROR as seen below for the confidential sheet. There may be a simpler way, but this is all that comes to mind at the moment. So first it will search the Active Projects, then Closed, then Confidential. Range 1 being the Start Date in each respective sheet and Range 2 being the Project #. Hah, this was an interesting one. Give a few seconds for the sheets to refresh when testing, it takes the formula a moment to query every sheet.

=IFERROR(INDEX(COLLECT({delete2 Range 1}, {delete2 Range 2}, [Project #]1), 1), IFERROR(INDEX(COLLECT({COMPLETED PROJECTS Start date}, {COMPLETED PROJECTS Project #}, [Project #]1), 1), INDEX(COLLECT({Copy of _delete2 confidential projects Range 1}, {Copy of _delete2 confidential projects Range 2}, [Project #]1), 1)))

JLC

Lauren, can't thank you enough for your help. Working fabulously and looks great - a lot less scary for people than doing the VLOOKUP, as easy as that one was, because of the cell link icons showing on such a large range of columns. Now it's more work for me but less panic for others Appreciate it!