• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Name Range formula

blcexcel

blcexcel

Member
I'm creating a report template that will be used by many people. So it has to be easy to use. It is a large file. There are 8 data tabs where the data is the same but different time frames. I created one report template with formulas to pull in the data from the data tabs and create calculations. I want to have a Data Validation drop-down list so users can choose which tab to pull the data from. The formulas to pull the data from the data tab are a combination of sumif and index/match. I want to use one set of named ranges in the formulas on the report tab but have formulas in the named ranges that will change to reference the different data tabs based on the user data validation drop-down selection. I haven't been able to accomplish this however and could use some help. I currently have the below formula in the reference for the Named Range and it does what I need. But I need to add another 4 data tabs and I want to see if there is a better way. I'm not sure if this method would be efficient and stable with the formula twice as long. Thanks in advance for ideas! I don't want to use VBA.

=IF(Template!$I$2="Last Week",'Last Week'!$A$1:$EZ$1,
IF(Template!$I$2="MTD thru LW",'MTD thru LW'!$A$1:$EZ$1,
IF(Template!$I$2="Week Select",'Week Select'!$A$1:$EZ$1,
IF(Template!$I$2="YTD thru LW",'YTD thru LW'!$A$1:$EZ$1,0))))
 
Last edited:
blcexcel said:
I'm creating a report template that will be used by many people. So it has to be easy to use. It is a large file. There are 8 data tabs where the data is the same but different time frames. I created one report template with formulas to pull in the data from the data tabs and create calculations. I want to have a Data Validation drop-down list so users can choose which tab to pull the data from. The formulas to pull the data from the data tab are a combination of sumif and index/match. I want to use one set of named ranges in the formulas on the report tab but have formulas in the named ranges that will change to reference the different data tabs based on the user data validation drop-down selection. I haven't been able to accomplish this however and could use some help. I currently have the below formula in the reference for the Named Range and it does what I need. But I need to add another 4 data tabs and I want to see if there is a better way. I'm not sure if this method would be efficient and stable with the formula twice as long. Thanks in advance for ideas! I don't want to use VBA.

=IF(Template!$I$2="Last Week",'Last Week'!$A$1:$EZ$1,
IF(Template!$I$2="MTD thru LW",'MTD thru LW'!$A$1:$EZ$1,
IF(Template!$I$2="Week Select",'Week Select'!$A$1:$EZ$1,
IF(Template!$I$2="YTD thru LW",'YTD thru LW'!$A$1:$EZ$1,0))))
Click to expand...
Hi,

Have you tried the INDIRECT function?
With indirect you can use cell contents as part of a string of text in your formula...

Please see example in attachment

Hope this helps
 

Attachments

  • Ex1.xlsx
    8.9 KB · Views: 4
Hi,

=IF(Template!$I$2="Last Week",'Last Week'!$A$1:$EZ$1,IF(Template!$I$2="MTD thru LW",'MTD thru LW'!$A$1:$EZ$1,IF(Template!$I$2="Week Select",'Week Select'!$A$1:$EZ$1,IF(Template!$I$2="YTD thru LW",'YTD thru LW'!$A$1:$EZ$1,0))))

is simplified to >>

=INDIRECT("'"&Template!$I$2&"'!$A$1:$EZ$1")

Regards
 
You must log in or register to reply here.
Back
Top