Nested IF & VLOOKUP for Checkbox

Di Mueller

So I'm trying to write a formula to pull a date from Sheet 1

if a checkbox is checked and match it to the same email on Sheet 2 .

=IF({Sheet 1 Range 1} = true, VLOOKUP([Column2]@row, {Sheet 1 Range 2}, 3, false), "")

Occasionally Smartsheets gives me trouble with pulling information from date columns that I've set to pull dates from a 3rd sheet (the schedule maker sheet- so that people don't mix up the dates when picking the workshop they assign). When I was testing this, I tried just the VLookup portion and it worked on day 1, so I c/p that working formula into a sticky note and continued to fiddle hoping to get the IF portion to work correctly. Day 2, I copy and pasted that exact formula back in only to get an #Invalid Column Value error.

I've used both 1 and true when checking for the checkbox, since they both seem to work for different types of formulas.

The column properties are set as date in both sheets.

Paul Newcome

Try something like this:

=IFERROR(INDEX(COLLECT({Sheet 1 Date Column}, {Sheet 1 Email Column}, [Column2]@row, {Sheet 1 Checkbox Column}, @cell = 1), 1), "")

Paul Newcome

Try something like this:

=IFERROR(INDEX(COLLECT({Sheet 1 Date Column}, {Sheet 1 Email Column}, [Column2]@row, {Sheet 1 Checkbox Column}, @cell = 1), 1), "")

Di Mueller

@Paul Newcome You are a gentleman and a scholar, sir!!! Thank you! Worked like a charm

Paul Newcome

Happy to help. 👍️

MANNA

Hello @Paul Newcome

Can you help me with the same problem please, trying to pull information from 3 columns on a checkbox row to another sheet. I cant seem to get the VLookup formula correctly.

Paul Newcome

@MANNA Are you able to manually enter the expected output in a screenshot?

MANNA

So I'm trying to build an issue/risk sheet. When an item from the original sheet is flagged, 3 columns in that row would copy out to the issue sheet. it seems like I can only use data mesh for that or create a form to insert the information that I need.

I was hoping I can use the Vlookup formula to automatically copy out information from the flagged row.

Paul Newcome

@MANNA Is there a reason you can't use a report?

MANNA

not really because my staff wont be able to explain the action taken to fix the issue along with adjusting the issue status in a report.

Paul Newcome

@MANNA If the users have access to edit the sheet(s) then they will have access to edit the report. How are they currently explaining the action taken and adjusting the status?

MANNA

This is their first time using smartsheet. Currently, they are recording everything into a spreadsheet.

Paul Newcome

@MANNA Ok. So if they have access to edit the sheet(s), you can also give them access to edit the report. The report will only show rows that they have access to the original sheet(s).

So if you have sheets A - F but only give me access to A and C, when I open the report it will only show rows from A and C, but they will be contained in a single report instead of having to go back and forth between the two sheets.

If you have an "Assigned To" column you can even go one step further and have the report set to filter based on Current User so that when I open it I only see rows assigned to me, but when you open that same exact report you will only see rows assigned to you.

As long as you provide editor access on the underlying sheets feeding the report and the report itself, users can make updates in the report and it will push through to the appropriate sheet.

MANNA

I'll try that thank you

Paul Newcome

Happy to help. 👍️