populate a sheet without cell linking

Peggy Parchert

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.

Bassam Khalil

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

Peggy Parchert

@Bassam.M Khalil

Attachment named Sheet One is a screenshot of my source sheet. There are formulas in the following columns:

Sheet One.png

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.

Sheet Two.png

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

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

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