Increment a Formula Reference in a Cell

[nutrastat]

Hi All,

I do hope somebody can help me?

I have formula references in cells, such as:

Deliveries!T25

Deliveries!AZ25

Deliveries!CK25

Deliveries!K26

Deliveries!CK26

<colgroup><col style="mso-width-source:userset;mso-width-alt:4386;width:96pt" width="129"> </colgroup><tbody>
</tbody>

They are not prefaced by an equals (=) sign, as this is to allow a program to read the reference location, to find data on another spreadsheet, and put the extracted data into a database. This allows me to change my original data excel spread sheet, which then allows me to change these data location references, without having to change the program. Just so that you understand why it has been completed in this way.

The above references are part of Table 1. Table 2 numbers happen to offset by 10, and so Deliveries!T25 on the next set of references becomes Deliveries!T35. The next Deliveries!AZ25, becomes Deliveries!AZ35 and so on.

I wish to use a formula to update these references, and have tried the following:

=IF(LEN(C41)-FIND("!",C41)=3,LEFT(C41,FIND("!",C41)+1)&NUMBERVALUE(RIGHT(C41,2))+10,LEFT(C41,FIND("!",C41)+2)&NUMBERVALUE(RIGHT(C41,2))+10)

Which works as long as I do not go into 100 & above. Adding a IF(LEN(C41)-FIND("!",C41)=5 to the formula, and making the changes to allow for the increased sized text, also does not work.

I am probably making a 'pigs ear' out of updating these cells, having also tried: ISNUMBER(MID(C140,FIND("!",C140)+1,1)) & ISNUMBER(MID(C140,FIND("!",C140)+2,1)). Because I do not get a proper TRUE & FALSE, as it always views it as 'text'.

Can somebody please help me, so that the formula can be replicated down, referencing the previous formulas, as there are many to do, and I would prefer not to use separate columns with all the information split out?

 

[Special-K99]

So put simply you want to increase by 10 the row number for each of the cell reference values.

This should work

=LEFT(A1,LEN(A1)-LEN(VALUE(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1))))&VALUE(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1))+10

NOTE: Based on your formula you said this

"Which works as long as I do not go into 100 & above"

That's not correct, row numbers like 99 or 109 simply add 10 and work fine with your formula.
What doesn't work is when the row number is a single digit however the formula I've supplied handles row numbers with one to five digits at least and columns consisting of a single letter or double letter.

See what my formula gives you.

 

[nutrastat]

Hi Special-K99,

THANK YOU SO MUCH, this was driving me crazy, and your formula is spot on. Sorry I complicated the explanation.

I have used it to replicate many formulas on the first section I was using, and will now try to work out how you have arrived at it, as MIN & SEARCH are not formula terms I have used before.

Thank you for helping me so quickly.

 

[Special-K99]

I'm not sure how it worked myself, I googled Excel extracting a number from a string and found this part

VALUE(RIGHT(A1,LEN(A1)-MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))+1))

and just modified it. Still a long formula but does the job hopefully.

 

[nutrastat]

Thanks NOT fair!
I was trying to do that with Google, but getting nowhere.
I believe I have found the URL:https://www.ablebits.com/office-addins-blog/2017/11/22/excel-extract-number-from-string/
I will take a look at it a little later on, so that I can try to understand it.
Thanks anyway for your help.

 

[Special-K99]

Yep thats the page!

Forgot to say this probably won't work if you put numbers as part of your tab names, e.g Deliveries1, Deliveries2 etc

Right I think it does this.

It searches for the numbers 0-9 in A1 (I think it's adding "0123456789" to ensure we don't get an error if a number is not present) and extracts the position
Irrespective of other numbers it uses MINimum to find the position of the first number, e.g. Deliveries!A10, there is a 1 at position 13 and 0 at position 14 so the
MIN will return 13.

Deliveries!A10 is 14 characters long so
LEN(A1)-13+1= 14-13+1 = 2

So it takes the last 2 characters RIGHT()... of the string and turns "10" into a number using VALUE()
This value will change dependent on the length of the string and the position of the first number in the string.

I thinks that's pretty much what it does.

 

[nutrastat]

Thanks again for your help,
I now have most of it done, ready for my meeting tomorrow with the software developer.
Bye...