Translating an Excel Formula to Smartsheet - #UNPARSEABLE

Dwight.D.Silverstone

Hello all,

I'm attempting to create several self-updating sheets fed by a manually updated database sheet. The goal is that it will feed into the multiple other sheets so that we can then give access to smaller groups of people based on which locations are returned by the reference cells. I've been able to successfully create this formula in Excel and have attempted to translate this coding to Smartsheet to no avail. I am not currently very comfortable or knowledgeable with Smartsheet's Syntax or its formula restrictions. I do believe the biggest problems in my Excel formula vs SS translation is that SS doesn't have the "Row" and "Column" formula. I assume there is another way to make this work though! Can you help, please?

Excel formula:

=IFERROR(INDEX('[Test Table]Sheet2'!$A:$O, SMALL(IF('[Test Table]Sheet2'!$A:$A=$R$1, ROW('[Test Table]Sheet2'!$A:$A)-MIN(ROW('[Test Table]Sheet2'!$A:$A))+1, ""), ROW(A1)), COLUMN(A1)),"")

Smartsheet attempted formula:

=IFERROR(INDEX({TEST FROM [Branch]:[Modified By]}, SMALL(IF({TEST FROM [Branch]:[Branch]}=$[Branch References]$1, {TEST FROM [Branch]@row}}-MIN({TEST FROM [Branch]@row}))+1, "") [Branch]@row, [Branch]),"")

Thank you so much!

KPH

Hi @Dwight.D.Silverstone

The IFERROR part looks good.

This part of your INDEX formula:

{TEST FROM [Branch]:[Modified By]}

Looks like a cross sheet reference to two columns. I see two potential issues here.

1. The INDEX formula is expecting a single column not multiple columns.
2. Using square brackets within cross sheet reference column names can be problematic. While the UI doesn't stop you using them, I don't advise it. Not only do the square brackets make reading the formula more difficult it can cause errors. It seems to work if you create the reference without the square brackets and then amend the name. But if you name the reference straight away with square brackets you encounter errors. I just avoid it.

Then, for your row index it looks like you are trying to do some math across columns using this {TEST FROM [Branch]@row}. At this point, I have more questions than is practical to type here. Can you share a screen shot of your smartsheet and the same from excel with the desired outcome?

Dwight.D.Silverstone

Hello @KPH ,

Yes, I can share a version of what I'm using it for. The goal is to have a different sheet for each separate location. In this case, London would only see London and Hogwarts would only see Hogwarts. When the master sheet updates, it feeds that info to the Hogwarts/London sheets automatically. I've included the array formula line as well in case that's helpful. Essentially, I've created a master database sheet feeding smaller local sheets that can be sent out for access to different locations without sharing the master Smartsheet(this is the goal, it's now only applicable in Excel) to all of them and without giving more information than what is pertinent to that location. I'm trying to translate the info to Smartsheet! Thank you :)

Explaining the array:

A:O is pulled with the array formula from the master sheet based on $R$1. R2 is the next name in the master sheet below the one currently populating. This is for ease of building the next sheet. Change R1, get the next name, build the next sheet, etc. R3 is which row in the master has the first instance of R1. R4 is which row has the next name in the Master, R2. R5 is how many reference rows should exist of R1. R1 is hand input at the creation of the sheet, the rest of the sheet populates. A = $R$1, the rest of the row from A:O shows up based on the master information.

Dwight.D.Silverstone

@KPH

Here are the Smartsheet screenshots. Not much to see, but I couldn't figure out how to get them to connect. If there is an automation that will help this process, that would be fantastic too! I'm not familiar with all the abilities this system has.

Thank you!!

KPH

Hi @Dwight.D.Silverstone

Having read your intentions I wonder if you even need to have multiple sheets.😱 Hear me out!

If the intention is to just enable you to share a subset of information from a sheet, would a report be able to achieve what you need? No formulas. No cross sheet references. No duplicated sheets. Just a few filtered reports based off of a single sheet.

You just need to create a report, filter on Column8 (aka I) to include only "Hogwarts" and you're done. Copy the report, edit filter for "London". Share these as view only. Take a look at this and see if it might work for you:

https://help.smartsheet.com/learning-track/smartsheet-intermediate/reports

Dwight.D.Silverstone

Hello @KPH

I will do some research on those reports!

Thank you,

KPH

Yes, the reports will update as the master sheet changes. Just share the link and the viewer will see the latest information whenever they click the link. If things change while they are looking, the report will update if they refresh.

You can even automatically create PDFs from reports and automatically send them at regular intervals if you wanted static copies, instead.

Dwight.D.Silverstone

@KPH

I have been building these reports for a few days. I'm working on the 12th reference sheet and the 25th Report to link to. This is exactly what I was looking for and the 2-way updates from the reports to the sheets and the sheets to reports is fabulous!

Thank you for helping and I very much appreciate the insight. Looking forward to exploring the rest of Smartsheet's capabilities!

KPH

That is wonderful news. You came with a formula question and left with a new approach. That’s what community is all about. Thank you so much for letting me know you’re succeeding.

Dwight.D.Silverstone

@KPH

Hello again!

So we have now built about 26 reference sheets using the report feature and linked them to all of our Master Sheets. All 26 reports were sent out to the individual local sites and when they logged in, all they saw is a blank page. My best guess for this is because they do not have access to the Master Sheets. Our goal all along has been to not share the master sheet as it has information all over the country and more information than the local sites need.

Is there either a way to get the reports to populate correctly without giving all employees at the 26 sites access to the Master sheet?

Or is there a way to give "linked access" between the report and the Master Sheets without giving access to the sheet?

Or is there a way to give someone access to the Master Sheets in order to make the reference to the report work, but have it show up as blank on their side on purpose?

Is there another solution to this?

We would love to avoid having to hand transfer information from one sheet to another either by copy and pasting or with automations if the information from one sheet will not live-feed to another when it gets updated. The report feature is working perfectly for us that have access to both the Master sheets and the local reports, but not for those that do not.

Thank you!

KPH

Hi

Use this button to publish the report and share the link to the published view. You do not need to share the sheet.

Dwight.D.Silverstone

@KPH

Seriously, you are such a fantastic Wizard.

THANK YOU SO MUCH! That does exactly what we were hoping for :) No restructuring, recreating.

What a wonderful world it is to have people like you helping silly folks like me!!

KPH

That is so great to hear! I'm so pleased you have a solution that you love. 😍