Linking two sheets into one master sheet
Ok Here is what I got, I have two employees who work for purchase import section.
From Rising the IPO to having the LC created to have the goods delivered to the factory.
I have created a common grid header for each of them and they are populating it as I write this. I will then create a master sheet where I will put in the most import data and have it linked to the two employees who are working on it.
My question/requirement is, When each of this employee creates a new row ie new IPO
I want in the master sheet to auto create this row and which employee is handling it.
The Aim is to have a master sheet where the manager can see the most basic important information to make quick decisions, without any more intervention. While the secondary working sheet is used by the purchasing team to do its day to day job easier
Attach is a very rudimentary diagram, hope I am able to explain.
Thanks and Regards
Answers
-
This can be solved using formulas, I would avoid that if possible. I've done it before and it is painful. I'll post a formula solution in another response in a minute. (it will actually be 3 separate formulas.)
Why can't you use a report to do this? With the report you can grab data from both sheets and post them together? Would it be better to instead have a math sheet with formulas across the sheets, create a report to merge the sheets, then post all on a dashboard?
-
Unfortunately there is no way to accomplish exactly what you are looking for without the help of some third party app.
The route I would go would be to start with the Master and then pull from there. Both forms populate the same sheet. You can then pull a report showing all info for person A, a report showing all info for person B, and a third report with the limited columns for the Manager.
-
The first formula you need is the collect. You will use this to join all of your data together. You need a column that has unique values for the return, I recommend using an autonumber column. When you use the collect you want to join it with a unique identifier. something like:
=join(collect({autonumber column reference},{Range for criteria 1},Criteria 1,{Range for criteria 2},...),",")
Which will return all of the index numbers given your criteria.
After you have gotten this working, you need to parse out the numbers.
The first return is going to be the first index. The easiest way is to keep the comma with the value. For this you are going to reference the results of your join cell.
=left(collect1,find(",",collect1))
Then you need to develop your pulldown formula. This will use the len and mid function to find your next value:
=mid(join1,len(join(collect2:collect$2)),find(",",right(join1,len(join(collect2:collect$2)))))
Drag this formula down until you are satisfied with the number of rows.
next you want to use index(match()) to return the value of the cells in the sheets
(if you want to make this easier, I recommend having a unique identifier in the autonumber sheets that shows what sheet it is. ex: A1 for sheet 1 and B1 for sheet 2. Then you can use an if left()=A,search this sheet, else if left()=B search this sheet to make your life a little easier.
As you can see this isn't really easy, and takes a lot of work to develop. I just typed this up and didn't test the formulas, but I based it off of some sheets I developed a while ago. I really recommend avoiding this if possible, but it is a good way to learn the formulas and how to make multiple sheets in smartsheet work together to your advantage.
The next level of this allows for you to improve the performance of smartsheet. When you have sheets that are slowing down, you can merge them in a similar manner using formulas and improve the overall performance, so there are definitely applications for this approach. Just expect to spend a lot of time troubleshooting, and not a huge amount of help here as it takes quite a bit to dig through this style of sheet and understand where problems are.
-
L@123
You are correct. In theory this could be done. And you are also correct in that parsing data within SS is a royal pain and prone to numerous errors.
Unfortunately your third formula (MID) only works for the second delimiter. Expanding this to multiple delimiters requires more and more nested FIND functions, although reading through your solution and re-reading through my response has given me a few ideas as to how to make this a little easier.
For now though I have a meeting to get to.
To be continued...
-
I have developed this for merging and filtering 4 sheets(actually 16 but ran into intersheet link limits)
Tried to simplify my solution down a bit, but my actual working formulas are below as well as a snapshot of the sheet.
=IF(OR([By Operation]1 = "Rough Niles", [By Operation]1 = "All"), JOIN(COLLECT({01. Rough Niles Counter}, {01. Rough Niles Shift}, IF($[By Shift]$1 = "All", OR(@cell = "1st", @cell = "2nd", @cell = "3rd"), @cell = $[By Shift]$1), {01. Rough Niles Created}, IF(ISNUMBER($[Within Last (Days)]$1), NETDAYS(@cell, TODAY()) <= $[Within Last (Days)]$1, IF(ISBLANK($[Date Range]$2), @cell = $[Date Range]$1, AND(@cell >= $[Date Range]$1, @cell <= $[Date Range]$2)))), ",") + ",", "")
is my collect formula for Rough Niles
=IFERROR(VALUE(MID(JOIN([Index Ref]$2:[Index Ref]$5, ","), LEN(JOIN([Index Ref]$7:[Index Ref]8, ",")), FIND(",", JOIN([Index Ref]$2:[Index Ref]$5, ","), LEN(JOIN([Index Ref]$7:[Index Ref]8, ","))) - (LEN(JOIN([Index Ref]$7:[Index Ref]8, ","))))), "")
Is my index finder
=IF(ISBLANK([Index Ref]9), "", IF(OR($[By Part]$1 = "JD 650", $[By Part]$1 = "All"), IF([Index Counter]9 = "Rough Niles", INDEX({01. Rough Niles JD 650}, [Index Ref]9), IF([Index Counter]9 = "Rough Mazak", INDEX({02. Rough Mazak JD 650}, [Index Ref]9), IF([Index Counter]9 = "Mill/Turn Pins", INDEX({03. Mill/Turn Pins JD 650}, [Index Ref]9), IF([Index Counter]9 = "Oil Hole Drill", INDEX({04. Oil Hole Drill JD 650}, [Index Ref]9))))), ""))
is my index formula
Mine is much more complicated as i have separate columns for filtering out the information, but the basic concept is the same. There's just smaller formulas.
-
I think you misunderstood the formula. The formula to return the first value is
=left(Collect1,find(",",Collect1))
Then the Mid formula is posted below this formula, and dragged down. this way it can grab all of the values, hence the $.
-
My mid formula was a off a bit, I went ahead and built a sample
https://app.smartsheet.com/b/publish?EQBCT=d50d7021caf3416c9fa49d115d619435
=MID(Collect$1, LEN(JOIN(Collect$2:Collect2)) + 1, 1 + FIND(",", RIGHT(Collect$1, LEN(JOIN(Collect$2:Collect2)))))
-
Ah. Ok. It is starting to make a little more sense now. My next question would be though...
How to you account for ever-expanding sheets? Do you need to pre-populate additional rows and hope you don't exceed that number?
-
Dear all
Thanks for all the input, I think the best way is to avoid all the formula as there is too many possibilities to make a mistake. So will be going with the Report methods.
Guess that will be the best methods
Thanks and Regards
-
Yes. I know there will be 3 inputs to each of the main sheets each day, one for the end of each shift. I pre-populated enough rows that I can analyze 5 years worth of submissions at any given time. I don't really see the need for them to have a wider range of analysis than that for what we are recording, though I still have plenty of space to expand more. It would probably slow the sheet down to the point that it isn't really useful though, but that's just a guess, I haven't tried it.
Because mine allows the user to filter, I just have an error pop-up if the user picks to much data.
This whole project wasn't easy to build, but it is rather effective. On the main sheet where the user picks the filter data it even selects a dashboard from a list of over 40 different dashboards depending on what the user picks. If there is any error, instead of the link to the dashboard the user gets an error code telling them what to fill out.
-
Why wouldn't you just use an automation to create a row in another sheet each time either employee adds one to theirs?
-
If you do an automation, the new, merging, sheet becomes static. That would only work if the data inputs will never change once initially entered.
In addition to the massive index architecture build referenced above, I believe control center could work as a solution for this. Either way, it's no simple feat, but would work well once established.
