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

excel misbehaving

L

linabhatla

New Member
Hi,

I am using your leave dashboard and have amended it for my needs. My problem is in "Vacation Dashboard" the equal to formula is picking the value from the cell below it.

For eg. the formula shows "=Z22" (which is a blank cell), the value it is taking is from Z23. i tried clearing all the cells and putting formulas again, does not seem to be helping. I have already done a lot of work on this to restart. kindly advise.

I have attached the dashboard for your reference.

Regards
Lina
 

Attachments

  • Vacation dashboard.xlsx
    269.5 KB · Views: 5
You can look at any name with multiple colours, AC19 in vacation dashboard sheet is taking value from z23 instead of z22
 
Hi ,

I don't find any misbehaviour.

AC19 has the following formula : =Calculations!Z22

Z22 in the Calculations tab has the value 0 , while Z23 in the same tab has the value 5.

AC19 in the Vacation Dashboard tab has the value 0 , while the cell below it , AC20 has the value 5.

I don't find any problem.

Narayan
 
So, you are saying it is not showing ashok's leaves in pradeep. Kindly resend me the dashboard. Coz in mine it is showing 5 in ac19.

Thanks for your prompt response.

Regards
Lina
 
Hi ,

The problem is the Conditional Formatting.

If you select row 19 , cells G19 through CT19 , and check the CF rules , they are referring to G13 , which is not correct ; they should be referring to G19.

Narayan
 
Yes. This looks good. How did you do it? I still need to learn a lot I guess. Please advise how did you do it? When I select all the rows and see conditional formatting the formulae is on G13, which G13 is it. G13 cell in your sheet as well is blank. are we referring to some other sheet or probably a cell name.

Regards
Leena
 
I am copying the dashboard so carried on with the same convention, G13=1, G13=2, etc. Can you tell me where is G13?

Regards
Leena
 
Hi ,

When you create a CF rule , you should first select a range over which it is to be applied.

Suppose we wish to have our rules apply to the range J10 : M20 ; first , select this range.

In the CF rule , assuming that the rule is a relative rule , which will look at a cell within the range and decide how it should be formatted , the cell reference which will be used should be the top left corner of the selected range. Since we have selected J10 : M20 , we need to refer to J10.

If by mistake we refer to J9 , then Excel will accept it , but the formatting will go wrong , since to color any cell , Excel will look at the cell above that cell ; to color cell L16 , Excel will look at the value in cell L15.

On the other hand , if the range was incorrectly selected as J9 : M20 , and the CF rule correctly referred to J10 , then to color any cell , Excel will look at the cell below that cell ; to color cell L16 , Excel will look at the value in cell L17.

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