If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to retrieve data from a table
I have built a Table (sheet 1) that includes 7 columns (A – G) and 11 rows
with the first row being titles. The titles in order are # of Users; Code 1; Monthly Pmt; Code 2; 3 Pmts; Code 3; and Annual Pmt. Under #of users, I have numbers 1 – 10 in numerical order. Under Code 1 the number, 1 is in each cell. Under Monthly Pmt, there is a dollar value in descending order. Under Code 2, the number 2 is in each cell. Under 3 Pmts there is a dollar value in descending order. Under Code 3, the number 3 is in each cell. Under Annual Pmt, there is a dollar value in descending order. On sheet 2 there are six columns (A – F), the titles in order are Date; Code; Customer Pay Type; Customer Name; Number of Users; and Sales Price Per User. The titles Code and Number of Users are input cells. In the Code cell I would input a 1 (Monthly Pmt), 2 (3 Pmts), or 3 (Annual Pmt). In the Number of Users cell I would input a 1, 2, 3, 4, 5, 6, 7, 8, 9, or 10. I need a solution for the Sales Price Per User using (matching) the Code and Number of Users on the Table (sheet 1). Thank you in advance for helping me. -- GRK |
#2
|
|||
|
|||
How to retrieve data from a table
Please stay within the original thread. If your question is misunderstood,
try restating it rather than starting another thread. With your table on Sheet1 beginning with "USERS" in cell A1 (data in A2:G11), Sheet2 "Code" in Col B and "#of Users" in Col E. Place this standard formula in Sheet2!F2 and copy down as needed. =INDEX(Sheet1!$A$2:$G$11,Sheet2!E2,(Sheet2!B2*2)+1 ) Best Regards, Dave "GRK" wrote in message ... I have built a Table (sheet 1) that includes 7 columns (A - G) and 11 rows with the first row being titles. The titles in order are # of Users; Code 1; Monthly Pmt; Code 2; 3 Pmts; Code 3; and Annual Pmt. Under #of users, I have numbers 1 - 10 in numerical order. Under Code 1 the number, 1 is in each cell. Under Monthly Pmt, there is a dollar value in descending order. Under Code 2, the number 2 is in each cell. Under 3 Pmts there is a dollar value in descending order. Under Code 3, the number 3 is in each cell. Under Annual Pmt, there is a dollar value in descending order. On sheet 2 there are six columns (A - F), the titles in order are Date; Code; Customer Pay Type; Customer Name; Number of Users; and Sales Price Per User. The titles Code and Number of Users are input cells. In the Code cell I would input a 1 (Monthly Pmt), 2 (3 Pmts), or 3 (Annual Pmt). In the Number of Users cell I would input a 1, 2, 3, 4, 5, 6, 7, 8, 9, or 10. I need a solution for the Sales Price Per User using (matching) the Code and Number of Users on the Table (sheet 1). Thank you in advance for helping me. -- GRK |
Thread Tools | |
Display Modes | |
|
|