how to retrain the values' SUM of different variable

[HANA1061]

Dear all,

I have a table which contains one changeable cell and its amount in the other cell. It means that cell "I3" would be changed from 1 through whatever required, and for every single number (1 or 2,...) the calculated amount would be found in the other cell.

e.g.: When I3:1 then L10 would be calculated 10 ; When I3:2 then L10 would be calculated 22 ; ... .

Right now, I want to record all those revised values of I3 with its relevant amount of L10 in another cells such as below:
NO. SUM
1 10
2 22
...
how can I saved the previous amount even after the No. and Sum would be revised in I3 and L10?( something like while F2 tab and F9 do for the formula of one cell).

I'd rather to do this in Excel not in Micro or VB.

really appreciated for your quick help in advance.

[Kaper]

Well, you posted the question in Excel Programming / Macros / VBA
while you wrote:

I'd rather to do this in Excel not in Micro or VB.

You (not so easy to say, as you posted no attachment) may try to rewrite/change your formula in L10 (and probably some other cells) in that manner, that copied down (either alone or with these other cells) it will refer to I4, I5... etc.
Then write next values to I4, I5 ... and ypu have the result.


Doing it with code would be rather easy. Let's say you want output for 1..20 in cells AA2:AB21 (headers in row 1)

Please Login or Register  to view this content.

[davesexcel]

Here is a simple example of Vlookup
https://www.youtube.com/watch?v=DBEyr-9ZSN4

[Kaper]

Hi Dave,

I think you've shown possible next step.
If I understood first post right, HANA1061 plans to build table, not use it (for now).

[HANA1061]

Hi ,
forum.xlsx
I attached the sample file which I am working on. ( hope it would be opened)
In that file you can find out that I wrote a code in the cells for gathering a record of all different SUM.s there.
my main purpose is I only can put number in E3 cell, then there would be some different numbers in cells L6 through L9 regarding to every E3(:1,2,3,...).

For instance: when E3:2 then H6 to H10 are: 2,10,20,30 , and then when E3 would be replaced with 3 then H6 to H10: 3, 20,13,24, and so on.
Now I need to record all E3 with the related SUM.s of H6 to H10.

My current problem is that in the recording table I can have SUM of H6 to H10 in front of number 1,2,3,... but this sum would be changed regarding to change of H6 to H10. I want to save relevant sum of H6 till H10 for E3:1 , then sum of H6 till H10 for E3:2, and more.forum.png.

many thanks for your ongoing help and supports.

[Kaper]

Why

E3:2 then H6 to H10 are: 2,10,20,30 , and then when E3 would be replaced with 3 then H6 to H10: 3, 20,13,24,

Or in other words: how can we know what shall appear in H6:H10 when there will be 4,5,6... in E3

[HANA1061]

I've only showed the simple excel file. Actually in the real file, each of cells from H6 to H10 are the results of some calculations in the other cells, regarding E3 number .
because of avoiding any complicated file, this simple file only sent and explained here.

[Kaper]

So make sure in L10 (ranges below refer to your sample spreadsheet, not your screenshot) is formula

Please Login or Register  to view this content.

and use the code from post above (changed only output locations):

Please Login or Register  to view this content.

[HANA1061]

many thanks Kaper for your time and help.
I wanna know that if there is any way to combine several Excel formula to catch up on the result before going to VB ?

[Kaper]

Sure, try for instance:

Please Login or Register  to view this content.

and let me remind:

1. Make sure that your sample data (in attached workbook not screenshot) are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!