Take input from one worksheet and display output in another worksheet

Hi,

I am fairly new to excel functions and macros. I want to take input value from a worksheet and then put them into formulas and populate the output in the third sheet. I don't know how to start with it. Could someone please guide me on this. Any help on this is deeply appreciated

Kindly look at the attached file for better understanding of my problem

Thanks
Alka

Hi Alka,
Any specific reasons why you are not using excel formulas directly in your output sheet?

Warm regards,
Jewel

Hi Jewel,

I am developing this mathematical model for users who have almost no knowledge of Excel.. I thought it would be better if they can change only the input sheet and not tamper with formula sheet.

As a workaround I have actually incorporated formulas using excel sheet . Please have a look in the attached excel "Sheet 1" & "Sheet 2".
For the data given as input in sheet 1 .. The output is reflected in sheet 2.
But currently its static.. I want excel to first read the no. of rows in "sheet 1" and produce the output in "sheet 2". Is there a way to do that ??
Thanks for all your help

Regards
Alka

Hi Alka,

As a workaround I have actually incorporated formulas using excel sheet . Please have a look in the attached excel "Sheet 1" & "Sheet 2". For the data given as input in sheet 1 .. The output is reflected in sheet 2.

This seems to be working fine now.

But currently its static.. I want excel to first read the no. of rows in "sheet 1" and produce the output in "sheet 2". Is there a way to do that ??

As for this, depending upon your idea of "Dynamic", you can have two approaches

• Formula based
• VBA based

For a formula based approach, you can create a very large sheet (such that it covers sufficient number of rows, say 100 or 1000) and then populate Sheet2 based on the inputs of Sheet1; by modifying the formulas with IF statements. For e.g. in Cell A2 use the Formula so that if the corresponding value in Sheet1 is blank, the value in Sheet 2 would also be blank; else it will be equal to the value in Sheet1!A2.

Formula:

Please Login or Register  to view this content.

Similarly, you can modify the formula of other cells in the Sheet2. For e,g, Cell B2 use Formula so that if Item No. in A2 is blank, then this cell (i.e. B2) will also be blank, or else equal to the formula you provided. Apply this approach for all cells; and then Output Sheet will auto-calculate the rows that have Item No. in Input Sheet or else display a blank.

Formula:

Please Login or Register  to view this content.

For VBA based approach, you can determine the last Item No. row of Sheet1 using

Please Login or Register  to view this content.

and then use a For-Next loop to work between Row 2 and Last Row to calculate all the cells. I'm not sure how familiar you/your users are with VBA. Let me know what options works for you & we can develop that solution.

Cheers!
Jewel

Thanks a ton for your help Jewel I adopted the easy method of using "IF" formulas instead of VBA. It works just fine for me .. Thanks again