Pulling out data from a master sheet

Elisabet Run

Hi all,

I'm in need of a solution to track activities. There is a master sheet that I have view access to where items get added by different parties. I want to pull out data based on a filter to create a tracking tool to make sure that my tasks in relation to the tasks in the master sheet are completed. Think about reaching out to a client once we have a new date for a batch of product available. Therefore the product ID is stable but the batch numbers and dates are constantly being added or updated.

Normally in excel I'd use an Array formula or smart filtering to get the information but neither seem to be available in Smartsheets. Index-Match and Vlookup only deliver the first batch number. Simple cell linking is not sufficient as there are number of other items in the master sheet that I'm not interested in and I'm way over the 500 cell limit.

A report does not achieve my goal as I need to be able to add a date column and a check box to the data I pullout from the master sheet.

Does anyone have an idea of what I might try next?

GrahamWelsby

Hi There,

With only 'View Access' are you able to grab any of the data?

Do you need a seperate sheet from the Master? Could you use filters for those using the sheet that will only display the open tasks that are assigned to you?

Thanks

Graham

Elisabet Run

Hi Graham,

I absolutely need to use a separate sheet for my function, it is too far removed from the original purpose of the master sheet and it is unwieldy enough as is.

I am able to use both Index-Array and Vlookup on the master sheet but neither is dynamic enough to pull out the amount and type of data I need.

I've seem some discussions in these forums where project managers have been asking the same questions but I've never seen a dynamic solution implemented.

Best,

Elísabet

GrahamWelsby

Hi Elisabet,

It's difficult to say without seeing the data sets. are there unique numbers in the Master Sheet that you could use? Can you Link the cells from the first sheet?

Are you only wanting to see the tasks assigned to just you/dept? If so are you able to add the Completed Date and Check box to the Master, then use a report and filter anything that is not blank out?

Are your wanting to add in the date/check box to complete the tasks? If so, how will you update the master?

thanks

Graham

Elisabet Run

Hi Graham,

I've created the following example with a very simplified master sheet.

This would be my mastersheet:

While this would be the extract and my added columns highlighted:

I have the constant that I only want information about the shoes but I need to know where they are manufactured, when and the batch number of them.

Once I have that information I'd assign them to a client, client manager and track if they've been contacted. This is of course really simplified but the added columns are really not what the manufacturing team is working on so they really don't care to add columns to their side, which I have full understanding of.

This would be easy if I just needed a report but since I want to use a single source of truth that might change and get more data with time I've been struggling on the approach I can use.

Any suggestions are highly appreciated, thanks.

Elísabet

GrahamWelsby

Ah, OK. So your extracting data from the master where it contains a certain criteria (team or client).

I think perhaps you could set up an automation where if it fits your criteria, it copies the row to you second sheet, Unfortunately this will carry ALL the columns, but you can add more columns to the end of your second sheet to assign for follow up. You can also add more columns that may track no days since it was logged etc.

Do you think this would be of use? And are you OK setting up the automation at all?

Another possible solution - taking Batch Number as the constant, you can set another automation up that will email you asking you to fill in a form - you could use this as the 'Assign' stage. you would have to set up a column in the Master which had a url to the form on your secondary - and add a query parameter to pull in the batch number - from there, your other columns manufacture, product etc could be pulled in using vlookup or index etc (you may need to move the batch number to the start of all the columns) this way you could control only the date you want to transfer?

Elisabet Run

Hi Graham,

Thank you for your continued support.

This solution crossed my mind but unfortunately I'm not able to set up automations as I'm only a viewer on the original sheet, that was my second line of attempts sorry I forgot to mention it.

Best,

Elísabet

GrahamWelsby

No problem @Elisabet Run, do you have access to the owner of the sheet? Can you enquire if they can set up the automation for you? I think this would work for you. Otherwise, you would only be able to pull information into a report without better access...

thanks

graham,