• 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.

Offset named range with helper cell for multiple worksheets

D

djphatic

New Member
I am trying to achieve the following and wondered if it is possible.


I have a spreadsheet that contains monthly data on different worksheets with the worksheet name as the monthname "Jan", "Feb", "Mar" etc. Each of these worksheets has the same structure but different number of rows due to number of days in the month.


What I would like to be able to do is use a helper cell to input the monthname which would be used in an offset formula in a named range to determine what worksheet to look at. This named range would then be used in charts. This would allow the charts to update automatically on a month by month basis.


For example:

=OFFSET(Jan!$A$1,0,0,COUNTA(Jan!$A$A),1)

=OFFSET(Feb!$A$1,0,0,COUNTA(Feb!$A$A),1)

=OFFSET(Mar!$A$1,0,0,COUNTA(Mar!$A$A),1)


The worksheet in the formula would change dependant on the value in the helper cell.


Other than creating multiple named ranges and changing the range in charts each month, I see no other solution outside of using VBA.
 
I found a different way since different sheets are being used.


Have to use indirect formula with a list of your named ranges.


So what do you have to do?

Step 1. Type out a list of your named ranges in excel, each with their own cell.

Step 2. Type out a list of the months.

Step 3. Create a combo box. right click the box, format control, set a cell link. Additionally set your input range to the list of months.

Step 4. Put a index formula above the list of named ranges. =INDEX(rangeofnamedranges,linkedcelltothecomboBOX)

Step 5. Make one last named range. This will be used for your graph.

The formula is. =indirect(linkedcellwiththeindexformula)

Step 6. Put this named range in your chart for the xvalues.


DONE!
 
Not to mess with Montrey excitement, but I think a named range of:

=OFFSET(INDIRECT(HelperCell&"!$A$1"),0,0,COUNTA(INDIRECT(HelperCell&"!$A$A")),1)


would work just fine, assuming the HelperCell reference is absolute ($A$1), and not relative (a1).
 
Luke's works as well, mine allows ranges to be anywhere on the sheet because the solution uses the named ranges u define.
 
You must log in or register to reply here.
Back
Top