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

Conditional Formatting of a cell based on another cell

P

Partha Misra

New Member
Hi,

I have created a combo box with a list of 2 items. 1 item is a Percentage and another number.So when I select the percentage category, the percentage data is selected and a chart is created based on that data, similarily for the next item i.e number. Now the problem I am faced with is the formatting of the cell which shows the data depending on the item selected from the combo box. The Percentage shows as 0.98 instead of 98% and the chart created also does the same. If I change the formatting of the cell to percentage, the data when is a number shows as 200% instead of 2.

How do I solve this issue and present data on the chart as it is.i.e if it is a percentage it would format as percentage and if it is number it would format as a number?

Please refer the attached workbook for your reference.
2zivtl0.jpg


Your help would be highly appreciated.

Regards,

Partha
 

Attachments

  • Formatting workbook.xlsx
    15.4 KB · Views: 5
Hi Somendra,

Thank you very much. Really appreciate your help.

The problem still persists with the chart though. If I enable the data labels, the labels show in correct format but the vertical axis figures changes. Check the attached file?



Thanks again.

Regards,

Partha

Partha Misra said:
Hi,

I have created a combo box with a list of 2 items. 1 item is a Percentage and another number.So when I select the percentage category, the percentage data is selected and a chart is created based on that data, similarily for the next item i.e number. Now the problem I am faced with is the formatting of the cell which shows the data depending on the item selected from the combo box. The Percentage shows as 0.98 instead of 98% and the chart created also does the same. If I change the formatting of the cell to percentage, the data when is a number shows as 200% instead of 2.

How do I solve this issue and present data on the chart as it is.i.e if it is a percentage it would format as percentage and if it is number it would format as a number?

Please refer the attached workbook for your reference.
2zivtl0.jpg


Your help would be highly appreciated.

Regards,

Partha
Click to expand...
 

Attachments

  • Formatting workbook.xlsx
    17.2 KB · Views: 2
Good day Partha
Is the upload what you want
 

Attachments

  • Formatting workbook.xlsx
    20.4 KB · Views: 2
Hi bobhc,

No. Its conditional formatting. The data changes according to the data type. Somendra solved one part of my problem by conditionally formatting the data..(i.e if its a percentage, it will show as a percentage and if its a number it will show as a number). You have formatted the cells as percentage irrespective of the data type.

Regards,

Partha
 
Hi Partha ,

See your file now. There were some unnecessary CF rules in place.

Narayan
 

Attachments

  • Formatting workbook.xlsx
    16 KB · Views: 7
Hi Partha,

The file I uploaded changing the cell values, vertical axis as per selection. I had not tried Labels.

The file you uploaded in comment #3 is working as intended. The same is done by @NARAYANK991 Sir above in comment #6.

Please have a look of above file and advise.

Regards,
 
Hi,

Thanks everyone for the fix. A new problem arises when I create a chart from scratch in another workbook using Narayank991 's method. The 0 and 1 on the vertical axis change to 0.00% and 100% and also if I have a decimal number like 0.75 the custom format converts it into 75%.

So the chart's y-axis starts with 0.00%, 100%, 2, 3, 4 when the data pulled is a number. With percentages it works fine.

And if the data is 0.75, 0, 0.20 and 4 - the chart shows the data as 75%, 0.00%, 20 % and 4.

How do I fix this?

Regards,

Partha
 
Partha Misra said:
Hi,

Thanks everyone for the fix. A new problem arises when I create a chart from scratch in another workbook using Narayank991 's method. The 0 and 1 on the vertical axis change to 0.00% and 100% and also if I have a decimal number like 0.75 the custom format converts it into 75%.

So the chart's y-axis starts with 0.00%, 100%, 2, 3, 4 when the data pulled is a number. With percentages it works fine.

And if the data is 0.75, 0, 0.20 and 4 - the chart shows the data as 75%, 0.00%, 20 % and 4.

How do I fix this?

Regards,

Partha
Click to expand...

P.S the file uploaded by Narayank991 works fine..but the above problem occurs when I do it another file. Maybe I am missing something. I just used the formula and the custom format in the cell as done by Narayan Sir.
 
Hi Partha,

Just check whether the chart series data in your new file is same as named range ChartData in my and Narayan Sir file.

Regards,
 
Hi Somendra,

Yeah I had missed that. Could you please explain exactly what is being done with the OFFSET formula?
I am not able to apply it in my new data.

Regards,

Partha
 
@Partha Misra

Actually OFFSET formula is picking the data for chart from the original data set. Earlier you were using the data set from down which were fetched from the INDEX formula. This is the technique to change formatting of labels & axis in chart.

Regards,
 
@Somendra ..Thanks a lot.

I got the entire picture now.

But say instead of 1 combo box there is another dependent combo box.. How would we create the chart then?

Say now we have 2 items in the list which returns value of 1 and 2 when selected from the dropdown.

If I had another combo box with the 2 teams name (Credit Sales and Retail Collections) and each team will have a different metric list (but same mix of percentage and number data). Then how would we approach the problem.

Selecting Credit Sales in the dropdown will give the user the option to select the metric related to credit sales only and same for Retail collections. So when I select Credit Sales the linked cell will show '1'. And from the metric dropdown when i say select 2nd metric the linked cell will show '2'.

How would we make the metric list unique then. I have tried concatenating the selections to give them a unique number viz 11,12,13,21,22,23,24.

But if we use OFFSET in this context it wont work I guess because there are total 7 items in the metric list combining both teams. Now if I use the concatenated cell in the OFFSET formula then when the cell shows 11, the formula would search for the data in the 11th cell and would return zero. Other alternative being I make data available only in the concatenated valued cells i.e in the 11th,12th,13th,21st ...

Please advise.

Regards,

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