Lookup values based on Tab name

[tallgeese980]

Hi,

I am currently trying to create a data sheet that pulls data from a specified tab based on the users selection. I have 9 tabs that are all exactly the same but each one is named for a specific product. These tabs have the months of the year running accross the top from B1 to M1 and the three types of accounts in A2 to A4. And of course the balances populating B2:M4

My goal is to have the user select the tab name from a drop down list on my data sheet and then select and account and a month (also from drop down lists). This would then populate the account balance cell accordingly. However, based on the numerous criteria every look up formula i have tried has failed.

Any ideas would be greatly appreciated!

 

[doublej_jj]

So on your report tab:

Say your drop down in in cell A1, and they are the names of your sheets.

In cell B2 =INDIRECT($A$1 &"!B2",1)

See if that works for you.

Jeff

 

[T. Valko]

Hi,

I am currently trying to create a data sheet that pulls data from a specified tab based on the users selection. I have 9 tabs that are all exactly the same but each one is named for a specific product. These tabs have the months of the year running accross the top from B1 to M1 and the three types of accounts in A2 to A4. And of course the balances populating B2:M4

My goal is to have the user select the tab name from a drop down list on my data sheet and then select and account and a month (also from drop down lists). This would then populate the account balance cell accordingly. However, based on the numerous criteria every look up formula i have tried has failed.

Any ideas would be greatly appreciated!

Are those column headers the month names as TEXT entries?

 

[tallgeese980]

Column headers are just the first three letters of the month ex. Jan, Feb etc.

So on my data tab if i wanted to know how much of product 1 (tab name) was sold in January for account 10000 it would look like this:

<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=301 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 82pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=109 height=18></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64> </TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=301 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 82pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=109 height=18>Product Name</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Account</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Month </TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Balance</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>Product 1</TD><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">10000</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Jan</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

 

[T. Valko]

Column headers are just the first three letters of the month ex. Jan, Feb etc.

So on my data tab if i wanted to know how much of product 1 (tab name) was sold in January for account 10000 it would look like this:

<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=301 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 82pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=109 height=18></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64></TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right></TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 226pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=301 border=0><COLGROUP><COL style="WIDTH: 82pt; mso-width-source: userset; mso-width-alt: 3986" width=109><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 82pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=109 height=18>Product Name</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Account</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Month </TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=64>Balance</TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" height=22>Product 1</TD><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">10000</TD><TD class=xl72 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Jan</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR></TBODY></TABLE>

Try this...

A2 = Product 1
B2 = 10000
C2 = Jan

Enter this formula in D2:

=VLOOKUP(B2,INDIRECT("'"&A2&"'!A1:M4"),MATCH(C2,INDIRECT("'"&A2&"'!A1:M1"),0),0)

 

[tallgeese980]

That formula seems to just return the account number instead of the balance of the account number.

 

[tallgeese980]

Wait that was my fault...I think it works fine actually! Thanks so much for the help!

 

[T. Valko]

Wait that was my fault...I think it works fine actually! Thanks so much for the help!

You're welcome. Thanks for the feedback!