Vlookup based on individual name of worksheet - is this possible?

[mimih23]

Hello,
This is my formula. =DSUM('No Superchain'!U:U,C1,'No Superchain'!U:U)
The text is based on the name of an individual tab within my workbook. Is it possible to cause the formula to automatically generate the tab name based on a running list of tab names so that I'm not having to key the name for each to pull the sum total? I hope that made sense. Basically, I have an analysis tab and I want to streamline the process of pulling individual data from each tab. Let me know if you can help.

I've attempted this =DSUM('[=A2]Analysis'!U$1:U$65536,C1,'[=A2]Analysis'!U$1:U$65536) but it does not work.
Running list of tab names are in column A. Need Total Volume results to populate in Column C.
Your help is greatly appreciated!!!
MiMi

Also, is it possible to create a formula to where I can automate the creation of hyperlinks to each individual tab based on the tab list OR will I need to continue to manually update that. Let me know. Thanks!!!!!

 

[Snakehips]

MiMi,

If I understand correctly then the following use of the indirect function may well do it for you.


List Sheet names in A Hyperlinks by formula in B DSUB in C Data in Sheets 2 & 3


Copy formulas down as required.

Excel 2007

	A	B	C
1	SheetNames	Link to Sheet	Test
2	Sheet2	Link	14
3	Sheet3	Link	96

<TBODY>
</TBODY>

Sheet1​

[TABLE="width: 85%"]
<TBODY>[TR]
[TD]Worksheet Formulas
[TABLE="width: 100%"]
<TBODY>[TR="bgcolor: #e0e0f0"]
[TH="width: 10"]Cell
[/TH]
[TH="align: left"]Formula
[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]B2
[/TH]
[TD="align: left"]=HYPERLINK(CELL("address",INDIRECT("'"&A2&"'!A1")),"Link")
[/TD]
[/TR]
[TR]
[TH="width: 10, bgcolor: #e0e0f0"]C2
[/TH]
[TD="align: left"]=DSUM(INDIRECT("'"&A2&"'!A1:A5"),$C$1,INDIRECT("'"&A2&"'!A1:A5"))
[/TD]
[/TR]
</TBODY>[/TABLE]
[/TD]
[/TR]
</TBODY>[/TABLE]



Excel 2007

	A

<COLGROUP><COL><COL></COLGROUP><THEAD>
</THEAD><TBODY>
[TD="align: center"]1[/TD]
[TD="align: center"]Test[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]5[/TD]

</TBODY>

Sheet2​

Excel 2007

	A
1	Test
2	3
3	88
4	5
5

<TBODY>
</TBODY>

Sheet3​

Hope that helps.

 

[mimih23]

Aaahh thank you so much!
The =DSUM(INDIRECT("'"&A2&"'!A1:A5"),$C$1,INDIRECT("'"&A2&"'!A1:A5")) worked like a charm.
I do have a couple of questions regarding the Hyperlink formula.
=HYPERLINK(CELL("address",INDIRECT("'"&A2&"'!A1")),"Link")
It does not appear to work. Per your example, I would like to see the link text actually be the name of the corresponding sheet.
What does "address" reference? How can I cause the formula to automatically populate the corresponding sheet name in place of Link? Let me know. You've been a great help!!!

 

[Snakehips]

MiMi,

Thans for thanks.

Re the hyperlinks.

To replace 'Link' with the sheet name from column A then edit formula as below and copy down.

Excel 2007
	A	B
1	SheetNames	Link to Sheet
2	Sheet2	Sheet2
3	Sheet3	Sheet3
Sheet1

Cell Formulas
Range	Formula
B2	=HYPERLINK(CELL("address",INDIRECT("'"&A2&"'!A1")),A2)

You do have to have the independent list of sheet names. Although currently in A, they could be anywhere in the workbook but edit formula ref to suit. Could hide column if you do not want the list visible as well as the list of hyperlinks.

'address' is one of the items of info that you can return by using the CELL function. It returns the full worksheet address of a cell in the form that is required in the HYPERLINK formula.

Hope that helps.

 

[mimih23]

Thanks.
However, the links are not linking.
The hyperlink text is populating well but when I go to click them, the error "Cannot open the specified file" pops up.
[TABLE="width: 192"]
<TBODY>[TR]
[TD]Super Chain</SPAN>[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]No Superchain</SPAN>[/TD]
[TD]No Superchain</SPAN>[/TD]
[/TR]
[TR]
[TD]SC0000OPAY</SPAN>[/TD]
[TD]SC0000OPAY</SPAN>[/TD]
[/TR]
[TR]
[TD]SC00CUBECO</SPAN>[/TD]
[TD]SC00CUBECO</SPAN>[/TD]
[/TR]
[TR]
[TD]SC00PAYTEK</SPAN>[/TD]
[TD]SC00PAYTEK</SPAN>

help!!!
[/TD]
[/TR]
</TBODY><COLGROUP><COL><COL></COLGROUP>[/TABLE]

 

[Snakehips]

MiMi,

I think the problem is caused by having spaces in the sheet name????

My previous examples had no spaces in the sheet names and so worked ok.

With no spaces, the CELL("address",ref) function returns the address without any single quotes.

When there are spaces in the sheet name the CELL("address",ref) function returns the address within single quotes as per....

'[TestHypers.xlsx]Sheet 2'!$A$1 which is not acceptable to the HYPERLINK function.

What we need is to remove the leading ' and insert it at the start of the sheet name as per....

[TestHypers.xlsx]'Sheet 2'!$A$1

So here is a revised formula that will manipulate the sheet names as necessary.

Excel 2007
	A	B
2	Sheet 2	Sheet 2
3	Sheet3	Sheet3
Sheet 1

Cell Formulas
Range	Formula
B2	=HYPERLINK(IF(LEFT(CELL("address",INDIRECT("'"&A2&"'!A1")),1)="'",SUBSTITUTE(MID(CELL("address",INDIRECT("'"&A2&"'!A1")),2,255),"]","]'"),CELL("address",INDIRECT("'"&A2&"'!A1"))),A2)

Fingers crossed, that should sort it.