Dear experts, I have attached and example spreadsheet which has a number of value which are concatenated. The final purpose is to establish a new formula which will end of in a Business Objects measurement.
Column A contains a name and B a vale. The number of names can be anywhere between 2 and 500, as can the value.
I need a dynamic macro which will do the following in it's output (either as text in any cell or in a prompt window where I can copy it);
1) Start with "="
2) Concatenate all data in column C (starting at C2)
3) End with "N/A"
4) End with a number of close brackets ")" , one bracket per line populated in column A:B
So, if I only had two lines in the attached and column D would display:
If([Employee Name] = "Name1";9636;
If([Employee Name] = "Name2";5289;
The Macro would have to create this:
=If([Employee Name] = "Name1";9636;If([Employee Name] = "Name2";5289;"N/A"))
The second part of my question is less important but would be great as well to solve. As indicated the number of entries in column A an B may vary. I would be great to have a macro drag down the formula in column C as far down as A:B are populated.
Many Thanks in advance for your advice!