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
|
|||
|
|||
worksheet cross reference
I use a excel workbook for quotes. A new worksheet for each quote. Worksheets
a numbered sequentially from 5000 onward. I want to create a summary worksheet within the workbook, one row for each worksheet. Column A in the summary is the quote number. The summary needs to take data from fixed cells in each worksheet, eg cell A17 is name, A18 is address. I also want to include the quote amount. This is in a named cell called TOTL. What I can't figure out is how the get the formulas the read column A in the summary to get the worksheet page. |
#2
|
|||
|
|||
worksheet cross reference
Suppose you have the sheet name in 'Summary' worksheet Col A cell A1
A1 = 5000 the formula in B1 will get the name from cell A1 of sheet specified in A1 = INDIRECT("'" & A1 & "'!A17") the formula in C1 will get the address from cell A1 of sheet specified in A1 = INDIRECT("'" & A1 & "'!A18") the formula in D1 will get the quote amount from cell A1 of sheet specified in A1 = INDIRECT("'" & A1 & "'!TOTL") In A2 enter the next sheet name (5001) and copy the fomulas from B1,C1,D1 down.. If this post helps click Yes --------------- Jacob Skaria "KUDOS" wrote: I use a excel workbook for quotes. A new worksheet for each quote. Worksheets a numbered sequentially from 5000 onward. I want to create a summary worksheet within the workbook, one row for each worksheet. Column A in the summary is the quote number. The summary needs to take data from fixed cells in each worksheet, eg cell A17 is name, A18 is address. I also want to include the quote amount. This is in a named cell called TOTL. What I can't figure out is how the get the formulas the read column A in the summary to get the worksheet page. |
#3
|
|||
|
|||
worksheet cross reference
You could use INDIRECT ..
In the summary sheet, Assume the quotation sheetnames are listed in A2 down, eg: 5000, 5001, etc In B2: =INDIRECT("'"&$A2&"'!A17") In C2: =INDIRECT("'"&$A2&"'!A18") Copy B2:C2 down as far as required to return the names and addresses Success? Click YES button below -- Max Singapore http://savefile.com/projects/236895 Downloads:25,000 Files:370 Subscribers:68 xdemechanik --- "KUDOS" wrote: I use a excel workbook for quotes. A new worksheet for each quote. Worksheets a numbered sequentially from 5000 onward. I want to create a summary worksheet within the workbook, one row for each worksheet. Column A in the summary is the quote number. The summary needs to take data from fixed cells in each worksheet, eg cell A17 is name, A18 is address. I also want to include the quote amount. This is in a named cell called TOTL. What I can't figure out is how the get the formulas the read column A in the summary to get the worksheet page. |
#4
|
|||
|
|||
worksheet cross reference
OfficeButton--Save As --Other Formats--Select xltx or xlt
-- If this post helps click Yes --------------- Jacob Skaria "KUDOS" wrote: I use a excel workbook for quotes. A new worksheet for each quote. Worksheets a numbered sequentially from 5000 onward. I want to create a summary worksheet within the workbook, one row for each worksheet. Column A in the summary is the quote number. The summary needs to take data from fixed cells in each worksheet, eg cell A17 is name, A18 is address. I also want to include the quote amount. This is in a named cell called TOTL. What I can't figure out is how the get the formulas the read column A in the summary to get the worksheet page. |
Thread Tools | |
Display Modes | |
|
|