Share this content

Can I convert a whole worksheet into a function?

I have a spreadsheet with 2 tabs. Tab 1 cell A1 I enter the profit and cell D45 displays the result (a figure based on a comination of tax etc). Tab 2 column A I have a list of profits levels, column B is the result from D45 for each profit level. At the moment I have to go to Tab 1, type each profit into box A1, note the result in D45, switch to Tab 2 and type the D45 result into Column B. Is there a way I can turn Tab 1 into a function so that excel will automatically put each profit figure from Tab 2 Column A into Tab 1 cell A1 and then automatically put the result in Tab 2 Column B?


Please login or register to join the discussion.

By ACDWebb
14th Dec 2011 09:55

Create a User Defined function in VBA based on the formula in Tab1 Cell D45Create a macro that will loop through all the cells used on Tab2 copy the profit from T2 An to T1 A1 and then copy by PasteSpecialValues T1 D45 to T2 Bn

Thanks (0)
By shurst
16th Dec 2011 09:53

Data tables?

It might also be worth investigating Data Tables. These allow for the creation of a table that substitutes a set of values into a formula to return a set of results. You can have a one column data table for one variable or a two dimensional table for two variables. I do have a short explanation of Data Tables on my own website (at the bottom of the long list of topics):

Thanks (0)
By edhy
17th Dec 2011 05:37

Data Table will work

As Mr. Hurst pointed out, one variable data table with Column input cell will work. However in Excel 2003 you have to have your calculations and the table in one sheet.


Zubair Edhy

Thanks (0)
17th Dec 2011 22:03

Forgive me if I am oversimplying this but could you not do this via a simple columnar format?


Tab 2 contains formulas relating to Cell A1, A2, A3 and D45, D46, D47 etc., and as soon as the values in these are updated they appear in the next relevant row in there?


If needs be you can move things around in Tab 1 to accommodate this.

Thanks (0)
By edhy
19th Dec 2011 05:45

It is Simple

(Just refer to Tab 1 only)

Your input cell is A1 and the result cell is D45.

Now go to an empty area on tab 1, say G1.

Keep G1 empty, from G2 to downwards in column G put variables (list of profit levels). Now refer H1 to result cell, (formulae in H1 will be =D45).

Now (Excel 2003) select table area (G1:H9, upto the rows you have values in column G) and  from menu select Data > Table..., in the table dialogue box refer column input cell to A1, (keep row input blank) and click OK, the results for all profit levels will be displayed.

In Excel 2007 use appropriate menu,I hope this explains the issue.


Zubair Edhy.



Thanks (0)