Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cross reference | Excel Discussion (Misc queries) | |||
Cross reference | Excel Discussion (Misc queries) | |||
cross reference two | New Users to Excel | |||
Cross reference | Excel Worksheet Functions | |||
Cross Reference | Excel Worksheet Functions |