Is there a way to condense Excel Formula references into one reference while keeping the formula contained to one cell?
For reference the formula I am using is
=VALUE(SUBSTITUTE(RIGHT(G4,LEN(G4)-
MIN(FIND({0,1,2,3,4,5,6,7,8,9},G4&"0123456789"))+1),"-","."))
Which References G4 multiple times, I would like someway to make it so I or another user only has to change G4 in one place should the change be required.
Perhaps something like this (Not real excel formula:)
=MULTIPLEX(VALUE(SUBSTITUTE(RIGHT(#1,LEN(#1)-
MIN(FIND({0,1,2,3,4,5,6,7,8,9},#1&"0123456789"))+1),"-",".")),#1,G4)
So that the #1 does not need to be changed and the G4 being changed once updates the whole formula.
I was trying to avoid VBA so that it is a user friendly formula, I will not be the only one using it.
Side note an implementation of my theoretical multiplex formula would allow for multiple references to be made. Syntax would be like MULTIPLEX([formula],[ref1],[cell1],[ref2],[cell2],...)
Side Side note the Formula is meant to extract the number from an item identifier for sorting purposes. ABC1234-1 becomes 1234.1 so we can sort by number ascending.
