Wayne88
asked on
Excel: using the result of a formula into another formula
Need help in doing the following: I have a MASTER worksheet that contains the customer info such as shown on the worksheet.
Then on the second worksheet is a QUOTE sheet where I would like to simply enter the quote number in cell J7 then Excel will extract info from the MASTER worksheet to carry over to the QUOTE sheet so this way I will not need to manually type the information into the QUOTE sheet for printing.
For example on the QUOTE sheet I would like to simply type in "3" in cell J7 and the CompanyName, Contact and Qty from the MASTER sheet in row 4 to get copied into the appropriate fields in the QUOTE sheet. If "2" was entered in J7 then it will grab the info in row 3 of the MASTER sheet and so on.
For each field in the QUOTE sheet (e.g. the company name), I thought about first using the MATCH function to get the row number of the info in the MASTER list (see cell L8 in the QUOTE worksheet) then prepending the column letter and row number into cell M7 which now can dynamically change according to the quote number entered. Here is when I get lost because I want to use the value in M8 for the following formula I will enter in B10 of the QUOTE worksheet:
=MASTER! VALUE_OF_M7 from the QUOTE worksheet
Sample File Attached.
If possible I would like to have this done without using VB scripting which I am not familiar with. If not, please suggest a detailed alternative.
I need your help with a clear step by step instruction that I can follow.
Thanks in advance
sample.xls
Then on the second worksheet is a QUOTE sheet where I would like to simply enter the quote number in cell J7 then Excel will extract info from the MASTER worksheet to carry over to the QUOTE sheet so this way I will not need to manually type the information into the QUOTE sheet for printing.
For example on the QUOTE sheet I would like to simply type in "3" in cell J7 and the CompanyName, Contact and Qty from the MASTER sheet in row 4 to get copied into the appropriate fields in the QUOTE sheet. If "2" was entered in J7 then it will grab the info in row 3 of the MASTER sheet and so on.
For each field in the QUOTE sheet (e.g. the company name), I thought about first using the MATCH function to get the row number of the info in the MASTER list (see cell L8 in the QUOTE worksheet) then prepending the column letter and row number into cell M7 which now can dynamically change according to the quote number entered. Here is when I get lost because I want to use the value in M8 for the following formula I will enter in B10 of the QUOTE worksheet:
=MASTER! VALUE_OF_M7 from the QUOTE worksheet
Sample File Attached.
If possible I would like to have this done without using VB scripting which I am not familiar with. If not, please suggest a detailed alternative.
I need your help with a clear step by step instruction that I can follow.
Thanks in advance
sample.xls
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Thanks for the points.
Best regards!!
Best regards!!
ASKER
sisiliano - thank you for your quick answer (200 points)
manijaganathan - thank you for your clarification (100 points)