Increment based on number. Without using scripts

[Kevineamon]

I have a problem here with an excel function. This wouldn't be a problemif could use scripts but unfortunately, there's another piece of softwarethat needs to read the data, this software cannot read scripts. I musttherefore, only use functions. I have this list of data
%MW101.0
%MW101.1
%MW101.2
%MW101.3
%MW101.4
%MW101.5
%MW101.6
%MW101.7
%MW101.8
%MW101.9
%MW101.10
%MW101.11
%MW101.12
%MW101.13
%MW101.14
%MW101.15

I’ve created this formula which is possibly slightly morecomplex than it needs to be.
=IFERROR(IF(MID(A2,SEARCH(".",A2),1)=".",CONCAT("%MW",MID(A2,SEARCH("W",A2)+1,3)+1)&MID(A2,SEARCH(".",A2),2)),CONCAT("%MW",MID(A2,SEARCH("W",A2)+1,3)+1))

It basically implements the last number by one unless there’sthat .0 at the end.
Eg.
%MW102.0
%MW102.1
%MW102.2
%MW102.3
%MW102.4
%MW102.5
%MW102.6
%MW102.7
%MW102.8
%MW102.9
%MW102.1
%MW102.1
%MW102.1
%MW102.1
%MW102.1
%MW102.1

This is the next column that it generated which is all good.
And if I pull the angry rabbit thing across the columns, toa set number, then it will generate, the necessary data.

I just want that to be automated, but not on the firstoccasion. I want the data on the first occasion but not for the rest. I wantthe rest to show the increments.

 

[steve the fish]

Hi. Its unclear to me what you ask for. You cant automate as such without using code. You can do logical tests using formulas if thats what you mean? What is your starting point, is it %MW101.0 in cell A2? Why in your second example do you repeat %MW102.1 lots of times?

 

[Kevineamon]

Sorry Steve that was me playing about with the code the .# should simply go down to 15 again.

These are memory addresses

so for eg
Given these values

%MW101.0
%MW101.1
...

I want it to go:

%MW102.0
%MW102.1

for the successive row or column

Lets say I choose the number 4

Then it should continue incrementing
%MW103.0
%MW103.1
...
%MW104.0

 

[steve the fish]

I cant say as you have made yourself too clear but lets says you have your first column of values in A2:A17 (%MW101.0 to %MW101.15) then you could place this in B2 and copy down and across:

=SUBSTITUTE(A2,MID(A2,4,FIND(".",A2)-4),MID(A2,4,FIND(".",A2)-4)+1)

 

[Kevineamon]

I've actually got it to do that already:-

=IFERROR(IF(MID(A2,SEARCH(".",A2),1)=".",CONCAT("%MW",MID(A2,SEARCH("W",A2)+1,3)+1)&MID(A2,SEARCH(".",A2),3)),CONCAT("%MW",MID(A2,SEARCH("W",A2)+1,3)+1))

Sounds like I know what I'm doing, but I'm only a average user, trying to accomplish a complex task.

Problem with these formulas is - I need these in rows one after another. I think I remember a TRANSPOSE formula or something similar that can change rows to columns.

I'm going to actually start another thread. I've thought of a simpler way to phrase this.