Loop for excel functions

[joshua1990]

Hey community!

I'm just trying to minimize/ simplify a big table. This table summarises a lot of different datasets from diverse other tables in collaboration with a big quantity of LOOKUPS und some basic calculations. This leads to a slow start everytime I open the file.
According to an aim with a very fast start I would like to minimize the functions.

What ideas do you have?

Every column has his own relative function. I would start to place only one function at the beginning and place the other functions as a static value. In connection to a vba procedure I would like to get a loop which runs the function till the last cell.
Do you have an idea for this loop/ code?


Best regards!

[Jan Karel Pieterse]

Excel can tie different tables together too, without the need for any formulas. Are you aware of that?

[joshua1990]

What do you mean exactly?
I dont need the wholes tables. Sometimes I need 1 cell/ value, sometimes the whole column.
Evertyhing according to the same date at the respective columm

[Jan Karel Pieterse]

I mean that Excel is capable of summarising data from different related tables into one pivottable.
So you could create a sales report per store where:
- Table 1 contains columns store ID and store name
- Table 2 contains columns date, turnover and store ID
You use both tables in your pivot table so you can show turnover per store (by store name) per month or date or year. No formulas required.

[joshua1990]

Thanks for the answers!
Unfortunately, it will not work with this approach.
Its not exactly a summary of datasets.
For example:
I need for some calculations the factor for workdays for the respective month. This value is presented in another table.
The data sets have different structures. Of cource, a pivot table would solve this problem perfectly, but not with this structure.

I am looking for an code/macro that solves the following problem:
The table have 25 columns (2-26) and round about 400 rows.
First thing I need a code, which replaces every function with the respective/ presented value ( a static value). Only the first row should have the original function.
Second, I need a code (maybe a loop) which drives thru every row and replace the value with the respective original function.

I think, this is maybe not a professional approach, but it will show me a new perspective and should solve the problem (temporarily)


With best regards