Excel Formula: define specific name every 100 lines
Excel Formula: define specific name every 100 lines
Hello experts,
I am looking for a formula in order to cover the following need:
Define an incremental number every 100 lines dynamically for an specific lines:
Example if I define my formula as of A2 I should have the following Name from A2 till A100
=Name_A2-A100
From A101 till A201=Name_A101-A201
From A202 till A302=Name_A202-A302
And so on...
If you have questions, don’t hesitate to contact me.
Hello experts,
I am looking for a formula in order to cover the following need:
Define an incremental number every 100 lines dynamically for an specific lines:
Example if I define my formula as of A2 I should have the following Name from A2 till A100
=Name_A2-A100
From A101 till A201=Name_A101-A201
From A202 till A302=Name_A202-A302
And so on...
If you have questions, don’t hesitate to contact me.
Rob Henson
Your question is not very clear, please can you upload a file with some sample data?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for this proposal.
I tested in Column B the formula and it works.
In column A is the required values.
I have just some complementary questions if I want to optimize the formula:
1-Instead of reporting directely "A" is not possible to dynamically use a formula to report the Column letter in which is the formula?
2-If I want to modify the incremental number to 200 I need to modify all the values which contains 101 by 201 in the formula?
Thank you very much for your help.
Dummy_File.xlsx
I tested in Column B the formula and it works.
In column A is the required values.
I have just some complementary questions if I want to optimize the formula:
1-Instead of reporting directely "A" is not possible to dynamically use a formula to report the Column letter in which is the formula?
2-If I want to modify the incremental number to 200 I need to modify all the values which contains 101 by 201 in the formula?
Thank you very much for your help.
Dummy_File.xlsx
For clarity A2 - A100 is only 99 rows, if you want 100 it should be A2 - A101; the next 100 groupings would then be:
102 - 201
202 - 301
302 - 401
etc
102 - 201
202 - 301
302 - 401
etc
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for those proposal.
They works both!
They works both!