1

I have a number in A1 (let's say it is 10). This represents the number of answers in a test.

In B1 I have the number of correct answers from the test (let's say this number is 6).

In B1 I want to be able to type the number 6 and for it to display as 6/10 (the /10 bit being drawn from cell A1).

The value of the cell is still 6 only however. This is just for the visual effect and the actual cell can have formulas done on it and such.

If I select B1 and type the number 7 it should update visually as 7/10

Is this possible without fancy VBA stuff? Many thanks

Excellll
  • 12,847
Thomas Clayson
  • 388

3 Answers3

1

You could use the next cell and add '/10 in it to show /10 So you can see this as visual. Alternatively, you can use this formula in a new cell =TEXT.CONCATENATE(A1,"/10") which will give 6/10 as value, and update this when A1 is changed.

Note that you can have a different sheet where the text.concatenate formula is entered, so that you can have a sheet with all values, and one to view the results. Just make sure both sheets are part of the same workbook.

LPChip
  • 66,193
1

I'm afraid this isn't possible without "fancy VBA stuff". The best you can do without VBA is to assign a custom format to B1 with the value from A1 hard-coded in the format.

To set the format this way, right-click B1 and select Format Cells.... Choose category Custom and type in the following format.

#"/10"

But of course you can achieve what you want with VBA without much hassle.

You'll just need to add the code below to the module associated with the worksheet with these cells.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
    Me.Range("B1").NumberFormat = "#" & Chr(34) & "/" & CStr(Me.Range("A1").Value) & Chr(34)
End If
End Sub

This code is triggered every time a value changes on the worksheet. If the value that changes is A1, it resets the number format for B1. If the changed value is in a different cell, it does nothing.

For instructions to add this code to your workbook, see my answer here. Be sure to follow the steps for adding code that is triggered by an event.

Community
  • 1
Excellll
  • 12,847
1

As a work around, you could try to do a custom format

#"/10"

The bad thing though, is the /10 is manual, you can't refer to a cell. However, you can simply select a group of cells and apply this formatting!

To do this, right click on a cell and select format cells.

At the bottom of Category (under the Number tab) select custom

Type in the above code under the type box (over write what is already there as it will automatically save it as a new entry).

Now, apply this formatting to the cells you want. Of course, it won't detect if the values are accurate, such as 12/10!

enter image description here

Dave
  • 25,513