Hidden Sheet Values - =Index(Match) - Help Please!

Genevieve P.

https://community.smartsheet.com/discussion/comment/335112#Comment_335112

Hi @cschaefer

This looks great!

For the two that are returning an error... are there any errors in the source sheet? In any of these ranges?

• {General 6S Form Sheet v.02 Range 1}
• {General 6S Form Sheet v.02 Range 15}
• {General 6S Form Sheet v.02 Range 16}
• {General 6S Form Sheet v.02 Range 2}

cschaefer

Hi Genevieve,

Thanks for the response. None that I can see. The #InvalidError cells should be showing what is in the cells shown in the General 6S Form Sheet with the dates. Not sure why it's not.

I have no idea what could be causing this to be honest. Any ideas? Thanks in advance.

cschaefer

Hi Kelly,

Yes, there is data in both of those cells. I also checked to see if there were errors in those cells and there were none. Thanks for your help.

cschaefer

All,

I found a small problem in one of the "General 6S Form Sheet v.02" source cells, which removed the error but there is still no dates just the "---". Another thing I noticed when comparing the sheets is that the 6S Audit Schedule sheet is mixing the data. See the attached image. Its mixing Index 0003 & 0004. The 6S should bring the data over even if the data comes out of order, so it should skip lines (I think) and place it according to the Index (basically align the data from one sheet to the other no matter what order it comes in).

Hope this makes sense. I think this is what's causing the first problem. If this can be solved, then it should solve the first problem.

Thanks in advance.

cschaefer

All,

I know why this is not working. The data is coming in from the form and being assigned a ROWID. The ROWID according to the current formulas are looking to match with the data in the Audit Schedule sheet. However, if the data does not come in exactly how the schedule is manually input, then it will show an error. The 2 that are showing errors were input out of sequence, this is why and this would be normal practice. So my approach needs to change.

So how do I make it so that the data from the source sheet comes over into the Audit Schedule sheet and places it into the correct row according to the index? I brought over the RowID column to make sure the data is lining up correctly using the same Index(collect) formula from before. I added a new data set to test (RowID 0009) and the Audit Schedule is just giving me a "---". Not sure why at this point. Any ideas? Thanks in advance.

Genevieve P.

Hi @cschaefer

You've done a lot of research and progress since I last saw this post! Very impressive.

It sounds like you have three separate INDEX(COLLECT formulas in each of those three columns, is that correct? If so, can you post all three formulas here for us to take a look at, identifying where each of your ranges are going?

The one formula above looks to be referencing your other cells that have formulas, so if we fix one it should fix the others.

cschaefer

Hi Genevieve,

Thanks Genevieve,

I just started using SmartSheets, learning it without any training, using just videos and reading. I'm getting there but it's slow. Anyways, I found out that the Due Date column formula is wrong as well. Its placing the date in the wrong row. It placed it at row 0009, when it should be at 0011 as shown in the attached image.

Due Date Formula

=IFERROR(INDEX({General 6S Form Sheet v.02 Range 15}, MATCH([Index (manual)]@row, {General 6S Form Sheet v.02 Range 16}, 0)), "---")

RowID Formula

=IFERROR(INDEX(COLLECT({General 6S Form Sheet v.02 Range 16}, {General 6S Form Sheet v.02 Range 2}, @cell = [Department (manual)]@row, {General 6S Form Sheet v.02 Range 1}, @cell = [Audit Date]@row, {General 6S Form Sheet v.02 Range 15}, @cell = [Due Date (from form)]@row), 1), "---")

Audit Date Formula

=IFERROR(INDEX(COLLECT({General 6S Form Sheet v.02 Range 1}, {General 6S Form Sheet v.02 Range 15}, @cell = [Due Date (from form)]@row, {General 6S Form Sheet v.02 Range 3}, @cell = [Work Week (auto)]@row, {General 6S Form Sheet v.02 Range 2}, @cell = [Department (manual)]@row), 1), "---")

Question: When the data comes into the audit schedule. Is it possible for it to skip a line or a row? This is the intent. Or is there a better way of handling this?

Thanks in advance.