In the image below I have an awful calculator which currently the company I work is using. Because everything is in the same column, it makes it challenging to automate. I have created a new table that streamlines everything into separate rows but because this is part of an SOP it still has to be used for now.
As a result, I'm wondering if it's possible to copy down a block of formulas/values.
In the desired output, all Values are constant formulas based on the Name of the impurity in green. Essentially the Green cells define a block of the same formulas just relying on a different Impurity. The calculations work by looking up values elsewhere using the name of the impurity and calculating thereafter. For Simplicity of the question I haven't included these formulas But just as a little more context with the first block:
- Impurity Name e.g. Antimony (pulled from list)
- 0.0003 = XLOOKUP(D3,B3:B8,C3:C8, "-") Please note that Column B in reality is just a section of a table on a different sheet so column C n the fouls is just the adjacent values)
- ppm. This is just standard text. No formula.
- MADL (ug/day) - Standard Text
- =XLOOKUP(D3,B3:B8,D3:D9,"-")
- NSRL (ug/day) - Standard Text
- =XLOOKUP(D3, B3:B8,E3:E9, "-")
In the middle, I essentially have these "Blocks" and on the left, you can see the source data for the impurity names.
This isn't essential to solving, but I do come across similar issues and have been interested in position a similar question in the past.
I have tried indexing columns and Offsetting values but so far have not been successful.
Essentially I have F3 = B3 and when the block is pulled down, F10 = B4, F17 = B5 and so on.
I have had some success in the past copying down blocks in this way but here unfortunately not. Another idea I had was to create a helper cell that somehow only inserts a character e.g. "*" And then apply a condt lookup function based on this, but again I just don't know how to to get it to skip to the next value in the source column.
If anyone has done something similar I would be interested to know your solution.
Finally, although I think I could produce this in power query a. I don't really want to because its a terrible way to produce a table anyway, but also I think there needs to be some flexibility here so that the users can actually enter in data is they wish, but power query will remove this upon refresh. So really I'm just looking for an excel formula solution.
CodePudding user response：
You could write your referencing formula in such a way that that would be possible. IMO though, based on the info shown only, the complexity outweighs the relative benefit.
Of course, it's possible the number of substances might get massive and/or this could be more of a purely academic question. (Your OP doesn't clarify either of those.)
Assuming the data is as presented in your example, the following formula does what you want. Note: This is how it would appear in cell D3.
=INDEX($B$2:$B$8,COUNTIF(D$2:D2,"=ppm") 2)
What it's doing:
o A relative index into the table of substances, based on previous occurrences of 'ppm' above it
o First instance has none, so gets data from row 2
o Next instance has 1, so gets data from row 3, etc.
Your 'Pull down' would of course start at D3.