Cell reference with condition.

I have a List of Street names and a Checkbox on a Source sheet!

I like to copy the street names and the R-count to a summary sheet, but only if the checkbox is checked.

I guess it will be best to have a formula in the Street column and another in the R-Count

Seems to me it should be simple, but I cannot get it to work.

I can get the Street Name unconditionally, using the “Link from another sheet” tool, but I cannot get a formula, with a logic to check the checkbox, to work.

Can any one point me to the right solution?

Find more posts tagged with

Accepted answers

Paul Newcome

When you want to change a cross sheet reference to a new one and leave the original one in place, you need to remove it entirely from the new formula and create a new cross sheet reference.

If all you are doing s clicking the link to "Edit Reference", you are editing that cross sheet reference, not creating a new one.

Paul Newcome

Happy to help. 👍️

All comments

Melissa Yamada

Hi @Teamciccone30576

You could use the Copy Row automation. The condition would be to only copy if Assigned is checked.

https://help.smartsheet.com/articles/2479626-automatically-move-or-copy-rows-between-sheets

Teamciccone30576

Hi @ Melissa Yamada

I do not think we can use Copy Row in this case.

But thanks for responding.

I should have been clearer in my post!

The previous screenshot shown is just small test sheets.

The actual Source sheet has about 20 Columns across, hereof 15 "Assigned" Columns.

(And it contains about 250 street names – not important for this discussion)

The actual Summary sheet has about 30 columns across.

I updated the screen shoot to better communicate the layout.

I need to construct a formula in the "Assigned Streets" column which fetches the street name for all the checked Streets.

I can use the same formular in the R-Count column , just adjusted to fetch the checked “R-Counts”.

So far i have not been able to get anything to work correctly.

Hope someone can point me in the right direction.

Paul Newcome

Is there a reason you can't use a report or sheet filter?

Teamciccone30576

Hi @ Poul Newcome.

Because it is supposed to be a formatted summary output available to all team members

There is right now 15 Farms across the sheet - 3 of which is shown here. Right now, it is a manual created sheet.

Trying to automate this, so any changes in a mailing list will be shown right away in the summary sheet.

It shows the street name and the number of residences on each street.

Note: the columns with "1" will not be in the final version.

We can show the street names with a cell reference, so I thought it would be possible to make it conditional - but so far, I have not found a solution.

Paul Newcome

It can be done via formula, but it can get a little bit complicated. If a report was possible, that would definitely be a much easier route to go.

To make sure I understand... In the [Farm1] column you would want to pull in all entries from the {Streets} column that have the {Assigned 1} checked? Then [Farm2] would be a list from that same {Streets} column except this time where {Assigned 2} is checked?

Teamciccone30576

@ Poul Newcome

That is correct.

Paul Newcome

In that case you would first insert a text/number column (called "Number") and manually enter the numbers 1 through however many you are going to need (plus a suggested buffer).

Then the formula to pull the data in would be:

=IFERROR(INDEX(COLLECT({Street}, {Assigned 1}, @cell = 1), Number@row), "")

Changing the second cross sheet reference to {Assigned 2} will pull in all of the streets where that column is checked off, so on and so forth.

Teamciccone30576

@ Poul Newcome

Thanks – does work – in one column when I test it on the test sheet.

But something strange happens when I add the formula to more columns (Farms) or use the modified columns on the R-Count column.

Changing the range in one column changes the range in all columns on the sheet!

It makes no sense – I have other formulas which use the common street list and different checkbox column – they do not change across the sheet.

Am I doing something wrong?

See the print screen attached.

I do have problems when I add the formula just to one farm on to the actual sheets, I get ”UNPARSEABLE” – but that is not the most pressing problem right now.

Paul Newcome

When you want to change a cross sheet reference to a new one and leave the original one in place, you need to remove it entirely from the new formula and create a new cross sheet reference.

If all you are doing s clicking the link to "Edit Reference", you are editing that cross sheet reference, not creating a new one.

Teamciccone30576

Hi @ Poul Newcome

Everything is now working as described.

I did not realize that we could not just edit the references – even after that, I learned the hard way. After inputting the of 4 farm/8 formulas I made a mistake in the references – and I thought I could edit this newly typed formula – but the whole sheet tokes the edit.

But now everything is OK, and I cannot thank you enough for helping me with this.

I learned a lot over the last few weeks, from you and others about Smartsheet.

Kurt

Paul Newcome

Happy to help. 👍️

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.