worksheet cross reference

[KUDOS]

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.

 

[Jacob Skaria]

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]

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

 

[Max]

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

 

[Max]

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

 

[Jacob Skaria]

OfficeButton-->Save As -->Other Formats-->Select xltx or xlt

 

[Jacob Skaria]

OfficeButton-->Save As -->Other Formats-->Select xltx or xlt