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

cschaefer

Hi,

I'm reaching out to get some help on an issue I've been trying to solve for a couple days now. I have 2 sheets of data. The first named "General 6S Form Sheet v.02", where the data comes from a form and the second named "6S Audit Schedule v.02", where the data comes from "General 6S Form Sheet v.02." The intent is to use the "6S Audit Schedule v.02" to assign tasks based on the input from the form data. The audit schedule has customer data that is manually added, so when the data from the form comes in they need to align, so this is what the index columns are for.

The problem I'm having now is the "Audit Date" column. The formula is =IFERROR(INDEX({General 6S Form Sheet v.02 Range 1}, MATCH([Index (manual)]@row, {General 6S Form Sheet v.02 Range 7}, 0)), "---"). Its first range is "General 6S Form Sheet v.02" column "Audit Date", then "6S Audit Schedule v.02" row "Index (manual)", then "General 6S Form Sheet v.02" columns "RowID2 & "Department". It works on the first 4 rows, then shows empty cells on the next 4 cells, then works again on all of the following. The funny thing is that the "Days Late" cell uses the "Audit Date" in order to provide a value and it works. How is this possible? Is this value invisible?

Note: There are 6 different customers. There make form entries every Monday, this is the schedule. However, they are often late and this needs to be tracked.

I also had to have the form input the due date into the schedule to get this to work because I couldn't get it to work when I had the Due Date in the Schedule. Also, as you can see it populated all of the departments, when it shouldn't have or at least I didn't want it to (there are only 8 entries). It shouldn't populate until the form makes the entry and aligns the index with the department. Also, the departments may not make entries in order, so line items may be skipped.

Am I going about this all wrong. I have tried numerous different types of formulas to bring data over from the "General 6S Form Sheet v.02", but always run into issues. It seems it would be easy to bring data over from one sheet to another and keep them aligned, but I can't figure out what I'm doing wrong. Help please! Chris

Michael Culley

This may be a silly question but is the font color white for those 3 cells by chance?

cschaefer

Hi Michael, not a silly question. I checked this as well and verified it as BLACK just to make sure. Thanks for responding.

Genevieve P.

Hi @cschaefer

Can you check the Conditional Formatting on the sheet? I can see that it's turned on and I'd be curious to know if any of the rules turn the cell colour white in that column; this would still show the column as set to black, but the Conditional Formatting rule would override this.

cschaefer

Hi Genevieve,

Great thought. The conditional formatting rule is shown below. I believe it's just for the "Done" checkbox column that strikethrough the entire row. I clicked the checkbox and it had no effect on the cell. Thanks,

Genevieve P.

I have to admit I'm very curious about this mystery!

Ok a couple more things to check here then... would you be able to copy/paste the formula that's being used in the "Days Late" column?

Try changing the 0 at the end of the MATCH function to 1:

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

The 1 should find the "first match". This is just in case it's reading through your column and finding 0070 and matching that accidentally... 0007 should be earlier in the sheet so it would be the first match.

cschaefer

Hi Genevieve,

I changed the "Audit Date" column formula to =IFERROR(INDEX({General 6S Form Sheet v.02 Range 1}, MATCH([Index (manual)]@row, {General 6S Form Sheet v.02 Range 7}, 1)), "---"). What it did was make all of the dates the same, which I believe was the first date from the source 06/29/22 index 0001.

I'm not sure what you mean about the "Days Late" column. You want me to copy/paste that formula. Correct? The formula here is: =IFERROR(NETWORKDAYS([Due Date (from form)]@row, IF([Audit Date]@row <> "", [Audit Date]@row, TODAY())), "---").

Let me know. Thanks for the help.

Genevieve P.

Ah thank you, this is very helpful!

So the Days Late formula is working even when the cell is blank because it's using "TODAY" when the cell is blank:

IF([Audit Date]@row <> "", [Audit Date]@row, TODAY())

The Networkdays is looking at the Due Date and Today:

NETWORKDAYS([Due Date (from form)]@row, TODAY())

My guess at this point is that the cells coming through as blank may have to do with the leading 0's in your matching value. Can you try using an INDEX(COLLECT instead of a MATCH, so we can use @cell = the matching condition, like so :

=IFERROR(INDEX(COLLECT({General 6S Form Sheet v.02 Range 1}, {General 6S Form Sheet v.02 Range 7}, @cell = [Index (manual)]@row), 1), "---")

Does this make a difference?

cschaefer

Missing argument.

What is the "@cell =" piece of this? Thanks.

=IFERROR(INDEX(COLLECT({General 6S Form Sheet v.02 Range 1}, {General 6S Form Sheet v.02 Range 7}, @cell = [Index (manual)]@row), 1), "---")

Genevieve P.

The @cell tells the formula to look through each cell of the previous range, and then you can compare each cell with a value. In your case you're looking to see if it = the value in the Index cell.

In most cases you shouldn't need it, but there have been reports where matching values with leading 0's works best when using @cell = "Value" instead of just Value

See: Create Efficient Formulas with @cell and @row

Can you post a screen capture of the formula open in the cell? It doesn't have any missing arguments that I can see (and tested on my sheet).

cschaefer

Hi,

It comes back with Incorrect Argument Set. See images. Thanks much.

cschaefer

Hi Genevieve,

I wonder if the @cell can be used with the Index(collect) formula. It requires very specific items in order to work. What do you think? Thanks,

cschaefer

Hi Everyone,

I have got this part of the problem partially with your help. With Genevieve steering to use the Index(collect) formula, I was able to put this formula together and is seems to be working with the exception of index rows 0003 & 0004. For some reason they are not displaying the dates.

=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 16}, @cell = [Index (manual)]@row, {General 6S Form Sheet v.02 Range 2}, @cell = [Department (manual)]@row), 1), "---").

Any ideas? Thanks in advance.

Kelly Moore

When you take the IFERROR off the formula, what error is happening?

cschaefer

Hi Kelly,

I get an #INVALID VALUE in the cells that were empty and the cells that were showing the dates, they continue to show the dates, so no change. Thanks,

Kelly Moore

One way to generate an INVALID VALUE error is when there isn't matching data in an Index/Collect. With all the other cells working, this seems a plausible explanation. Is it possible there isn't data to collect for those two rows?

Kelly