How to combine cells from two sheets to get information
I have two sheets where they both have the full name column and I want to get the average numbers a person goes from avature application date to productive date:
Both sheets have full name but I am not sure of the formula to get the average days person (full name) avature application to productive date which is in another sheet.
Below is the sheet where productive date is the data I want to pull from a cell if the full name is the same as in the other sheet:
Below is the sheet where I want to use the avature application date:
Answers
-
Below is the formula that I tried:
=INDEX(COLLECT({Hired Range 2}, {Hired Range 1}, [Full Name]@row))
-
Try replacing the INDEX function with an AVG function.
-
How do I copy columns from one sheet to anther. Not pull data, but bring the columns I created.
-
@TKh You can either do it manually by hovering over the far left side of the row, clicking on the three vertical dots to access the row options, then selection "Copy row to another sheet", or you can set up an automation that will do it automatically based on a selected trigger (with optional conditions to further filter which rows).
-
@TKh if you copy a single row over manually, it will automatically add any missing columns to the second sheet. You can then delete the row from the second sheet if needed.
-
Thank you, @Paul Newcome , it does not work. Is there a video for show and tell to follow? The software is powerful but not very user friendly and does not do simple tasks such as copying and pasting from one sheet to another unless you apply complicated formulas or extra steps. When crunched for time, simplicity in software application can make a difference in being effective. Thank you again.
-
I created two different sheets both already populated with rows and columns. Sheet 1 includes several projects of Department that are listed. Sheet 2 includes different project list plus additional 5 columns that I added formulas for scoring. Instead of recreating those 5 columns, I would like to simply copy the columns that I created in sheet 2 and bring them the structure and formulas to sheet 1. Smartsheet does not offer a simple way to do so. I am assuming, I have to recreate them in sheet 1 after all. Thank you!
-
The formula below:
=AVG((COLLECT({Hired Range 2}, {Hired Range 1}, [Full Name]@row)))
I got unparsable
-
Hi @Louis.Smith
It looks like you have a few extra parentheses that don't need to be there. Try this:
=AVG(COLLECT({Hired Range 2}, {Hired Range 1}, [Full Name]@row))
Note that Range 2 should be the one you're looking to Average, and Range 1 should contain the matching value of [Full Name]
Cheers,
Genevieve
-
That didn't work as well.
I would need to pull the avature date from one sheet where full name is found and is the same as in the master. Then subtract the avature date from the productive date then the get the average of the two for column
-
Try creating the duration on the source sheet and then averaging the duration column.
=[Productive Date]@row - [Avature Date]@row
-
Wouldn't I need to have the avature dates pulled into the Master Directory Sheet? Currently its not pulling into the sheet. That formula still is not working for me.
-
You need to calculate the individual durations on the source sheet.
-
Can you help with the formula for pulling in the avature application date by matching the full name column so that I can calculate the individual durations?
-
Hello, I was wondering if you could be of assistance.
Help Article Resources
Categories
Check out the Formula Handbook template!
