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

Using Dropdown list in the Offset Equation

Chelala

Chelala

New Member
Hi All,

I use below formula for gathering figures from sheet1 as a tool for presenting monthly results or year to date results;
=SUM(OFFSET(INDIRECT("2015!A"&MATCH(A52,'2015'!$A:$A,0)),0,IF(ytd,1,$E$4),1,IF(ytd,$E$4,1)))

the case now I've five sheets for 5 branches. each sheet contains same headers and items details while the results during the 12 months is different.
I'm trying to add a dropdown list in the summary sheet "main sheet" to define the branch and it's results.

thanks in advance for your time and advice.

Cheerily,

Chelala
 
Hi ,

Can you upload a sample workbook , which shows where you have your dropdown , and your data layout ? Sheet1 does not figure anywhere in your posted formula , so where is it to be incorporated ?

Narayan
 
NARAYANK991 said:
Hi ,

Can you upload a sample workbook , which shows where you have your dropdown , and your data layout ? Sheet1 does not figure anywhere in your posted formula , so where is it to be incorporated ?

Narayan
Click to expand...

Hi Narayan,
Thanks for your reply, I uploaded a sample file. appreciating your help on that file.

thanks in advance for the time and efforts.

Chelala
 

Attachments

Chelala said:
thanks Narayan very much!!
I need to incorporate same in the 2014.

thanks again!!

Cheerily,

Chelala
Click to expand...
NARAYANK991 said:
Hi ,

Is it not already present ?

Narayan
Click to expand...

Hi,

it's really great and informative.

Yes, 2014 is present in the template. I did little amendments on the sheet and added more branches. however, when I changed the sheets' name and the dropdown list, I got #ref# error. I think it's related to reference cell in the equation.
please check the attached file and let me know how can I change update the equation with the real branch name.

Many thanks for your time and efforts.

Cheerily,

Chelala
 

Attachments

thanks Narayan very much it works. could you help me on the indirect reference incorporated in the equation, I need to change the starting reference "INDIRECT(Prefix & "!" & "$B$3")" in the sheet.


Chelala
 
Hi Narayan,

I incorporated an equation to the attached template - Sheet "States Conso.." I couldn't figure out why it gives "#REF!", I checked my formula line but no luck.
could you please help me on it??


thanks in advance,
Chelala
 

Attachments

Hi ,

See the attached file.

The problem is that when you have a space character or any other special character within a worksheet tab name , the worksheet tab name has to be enclosed in single quotes.

Thus you can have the following :

INDIRECT("Sheet1" & "!" & "$A$3")

when you are referring to a worksheet tab named Sheet1.

But when you have a worksheet tab name such as California 2014 , where there is an embedded space character , then you need to use :

INDIRECT("'" & "California 2014" & "'" & "!" & "$A$3")

I have separated the individual components so that you can see what is common between the two representations and what is different.

Narayan
 
Thanks Narayan!!

I works perfectly but I don't know why the YTD equation doesn't work I changed the range of the column in offset formula using "IF($C$2,1,$E$3)-1"but still bring the wrong figures.

thanks again!!
Cheerily,
Chelala
 
You must log in or register to reply here.
Back
Top