See if this works.
I've created a very pared down example since the data is sensitive.
If you look at the listing tab, just the presence of a 'code' there is enough to show that the item was received. You can see the countif nested in an if I've used in colunb B of Summary to check this. (I know - overcomplicated for this purpose, but the same method is used elsewhere where it does need to be so complicated, so standardisation is good.)
In column C I need to pull across whether the item has then been sent out. If the text in listing column B contains 'In' for the relevant Code, then it has not been sent out. If it contains 'Out' then it has.
But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry?
I guess I could do it in two steps? A VLOOKUP to a hidden cell, the a MID from that cell? But is there a way to do it in one?
Solving this is not possible within the normal bounds of excel I fear. The problem is, you need to return an array reference where Excel can only return values. This is the problem not being able to create array reference variables on the fly. What you really want is to return an array reference. You will have to use the VLOOKUP function and then create a hidden column in order to solve the problem.
Your problem falls apart where you say "But how do I tell excel to look for the line containing the relevant Code, then column B on that line, then the middle of that entry
". Excel handles things like
=IF(This, then_do_that, else_do_that)
but there is no way you can recover where 'This' happens and forward it to the 'then_do_that' or 'else_do_that' parts of the formula. It will only tell you if
it happened. Same with the VLOOKUP function, and in fact, all of Excel I think. This sort of makes sense as you cannot do that in a scripting language either. What you are really asking is 'how can I make a variable'; you can't sadly, not in the context of a self contained formula:
=IF(This_happens, cell_ref = "Where_this_is_happening", then_do_this_with_cell_ref, or_do_that_with_cell_ref)
We can string search for 'n' in the code column very easily and also parse strings easily. But you can only find how many instances of that code exist, not where
they are; more accurately you cannot return them in a single line
, hence your problem. You can only create another column which will return an array reference, and then use that particular reference in all of your loops. You could try using VBA, but that means using Visual Basic macro in Excel which is possibly a cause for hanging. I hate Excel sometimes, lovely to look at, but useless for data analysis.
Excel can do some really cool stuff with graphs, but its inability to do complex searches and return nice variables is annoying. It's well up there with C-Shell scripting in my list of least liked computing activities.