populate a sheet without cell linking
I'm trying to pull data from one sheet into another sheet using formulas instead of cell-linking.
My source sheet has this column:
- Sheet One | Column: Projected Duration (days) - text/number column type. *populated by a formula
- Sheet One | Column: Project - text/number column type
The sheet I want to populate has
- On Sheet Two | Column: Projected Duration (days) - text/number column type
I have the following formula in Sheet Two | Column: Projected Duration (days):
=IFERROR(COLLECT({Projected Duration (days)}, {Project}, [Projected Duration (days)]@row), "UNK")
where
{Projected Duration (days)} range is the Sheet One | Column: Projected Duration (days)
{Project} criterion_range1 is the Sheet One | Column: Project
[Projected Duration (days)]@row is criterion1 is the Sheet Two | Column: [Projected Duration (days)]@row
I keep getting an #INVALID COLUMN VALUE error message. I think it is because I am not using COLLECT inside another FUNCTION but I don't know what other function to use - I want to pull data over based off the project, not compute something.
Any assistance would be appreciated.
Answers
-
Hi @Peggy P
Hope you are fine, could you please supply a screenshot for the tow sheet with a sample of what you want to do ( remove any sensitive data ).
bassam.khalil2009@gmail.com
☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Attachment named Sheet One is a screenshot of my source sheet. There are formulas in the following columns:
Project Start Date (date type column):
=IFERROR(DATE(VALUE(LEFT([Project Start | Project Name | Account IDs]@row, 4)), VALUE(MID([Project Start | Project Name | Account IDs]@row, 5, 2)), VALUE(MID([Project Start | Project Name | Account IDs]@row, 7, 2))), "")
Projected Duration (days) (text/number column):
=IFERROR(NETDAYS([Actual Start]@row, [Project Start Date]@row), "")
Attachment named Sheet Two is a screenshot of the sheet I want to pull the data into (without cell-linking). There is a formulas that I am using to attempt to accomplish this but it's not working - I know I've got something wrong - I just can't seem to figure it out.
Projected Duration (days) (text/number column):
=IFERROR(COLLECT({Projected Duration (days)}, {Project Start Project Name Account IDs}, [Project Start | Project Name | Account IDs]@row), "UNK")
Thank you for your help.
-
I think you just need a Vlookup...
VLOOKUP( search_value, lookup_table, column_num, [ match_type ] )
so on sheet two
= VLOOKUP([Project Start|Project Date|Account ID's]@row,{Sheet 1 Range},4,false)
Where the {Sheet1 Range} would be the all the columns in Sheet 1. When writing the VLOOKUP click the REFERENCE ANOTHER SHEET link in the formula helper to pick it
Basically, the Vlookup is going to pick the Data in Project Start|Project Date|Account ID column of Sheet Two from the row, then go to Sheet one and look in the 1st column there for an exact match.. (Basically, the [Project Start|Project Date|Account ID's] column in sheet one) find it and then return the value in the 4th column of the row that matches ...
this would be your Project Duration (days) value
The final FALSE tells VLOOKUP you want an exact match, not a close one...
Let me know if that helps or I missed the boat on your question
-
Apologies for not responding sooner. VLOOKUP won't work since I gave you a pared down version of the sheet that I'm trying to get this to work in. The column, Project Start | Project Name | Account IDs, in Sheet one is actually column 26; Actual Start is column 14; Project Start Date is column 17 and Projected Duration (days) column is 53.
In removing the sensitive data, I only showed you the columns that I was using. From what I understand about VLOOKUP function is that the search_value must be in the leftmost column (position 1) which the Project Start | Project Name | Account IDs column is not (and I am not able to move it there).
I think I will just add the necessary columns to Sheet Two and then add formulas. I was just hoping to not duplicate columns is all.
Thank you again for the input - appreciated.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!