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

F

Flexorion

New Member
Hello,


I am trying to figure out how to input a number into a cell, and as a result, a square made up of four squares with a letter in each one will appear in that row, the number of squares according to the number entered.


Thank you!
 
Flexorion


Firstly, Welcome to the Chandoo.org Forums


Can you please explain what you mean by a square made up of 4 squares will appear in that row, means?


Maybe post a sample file with 1 or 2 examples of what your after

Refer: http://chandoo.org/forums/topic/posting-a-sample-workbook
 
Thank you.


I am making a checklist for store renovations in a national chain. They need a four part check list for each unit, which currently is formatted as a square cut into fourths (four tiny squares). So, say if the number 2 is put into one row, I need two squares to pop up in that row. I've been researching, I figure it is probably a conditional formula, but I have no previous experience with VBA.
 
it is difficult to post an examble of this without posting private information, and I would honestly prefer to scrap what's there and start with an entirely new format.
 
Spit balling here, but possible ideas:

Box is some sort of symbol, generated by a REPT function. Maybe using some strange font like Wingdings?

=REPT([symbol],A2)


Is the tiny grid contained all within 1 cell? If not, perhaps conditional cofmrating is being used to generate cell borders based on input cell?
 
I took this to the more knowledgable people in the department, and they all said it required some complicated programming, so I'll be needing to start from scratch. Perhaps I could make each checkmark unit a picture, and when a number is inputted into a cell, then the number of pictures would appear in the row equal to the number inputted into the cell.
 
Hi Flexorion,


Try this:


1. Paste this formula in some cell (Lets say B1):


Code: 
=IF(ISBLANK(A2)=TRUE,"",CHOOSE(A2,"n","nn","nn"&CHAR(10)&"n","nn"&CHAR(10)&"nn")))


You will be entering value betweeen 1 to 4 in the adjacent Cell i.e. A1


2. Now set the font of this cell to be "Wingdings" and font size to 6.

3. Select B1, right click, wrap text the cell and align text to vertical.


Now you will see (upto four) squares, depending upon what value you enter in A1.


..or see this file:


http://dl.dropbox.com/u/60644346/SquareInCells.xlsx


Hope that helps!!


Regards,
 
Hope you're able to find something that works. I have a sneaky suspicion that "complicated programming" is code for "We don't understand it". =P
 
Hi Flesorion,


just few days ago I read the below topic..

http://www.dailydoseofexcel.com/archives/2012/11/01/seven-segment-display/


and found your query is somehow similar..


So just tried to adapt the code by changing few place..

It was not so tiny.. as you expecting.. hope you will like it.. like me..


https://dl.dropbox.com/u/78831150/Excel/Four%20Square%20%28Flexorion%29.xlsm

[pre]
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, CellVal As Variant, Digit As Range
' Checking only [C9]
If Target.Address = Me.Range("C9").Address Then
CellVal = Target.Value
If CellVal Like "*[!0-9]*" Then
MsgBox "The value you entered is not valid!", vbCritical
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
Else
Application.ScreenUpdating = False
Rows("2:6").Interior.ColorIndex = 0
For X = 0 To CellVal - 1
With Range("B2").Offset(, 4 * X)
.Resize(5, 3).Interior.ColorIndex = 1
.Offset(1, 1).Resize(3, 1).Interior.ColorIndex = 0
End With
Next
Application.ScreenUpdating = True
End If
End If
End Sub
[/pre]

Regards,

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