populate a sheet without cell linking

Options

I'm trying to pull data from one sheet into another sheet using formulas instead of cell-linking.

My source sheet has this column:

  1. Sheet One | Column: Projected Duration (days) - text/number column type. *populated by a formula
  2. Sheet One | Column: Project - text/number column type

The sheet I want to populate has

  1. 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

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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 ).

    PMP Certified

    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"

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @Bassam.M Khalil

    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.

  • bcwilson.ca
    bcwilson.ca ✭✭✭✭✭
    Options

    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

  • Peggy Parchert
    Peggy Parchert ✭✭✭✭✭✭
    Options

    @bcwilson.ca

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!