Combining "If" "" / If IFBLANK with another complex formula HELP

Question

So, I am trying to assign random codes to products in excel with the RANDBETWEEN, however, I don't want it to add a random number unless there is text in the Item section, every formula I try online gives me an error.

When selected empty first Item Table Cell comes as:

=[@Items]

Empty cell to the right

=[@[Assign Reference]]

I need to combine if "" or If+IFBLANK with RANDBETWEEN

So if =[@Items] is blank then leave blank but if not add Random Arrawy between 100000 and 999999

Everything I try gives one or another error

riny_van_eekelen · Answer

ShearaKLC&nbsp;Try this:=IF(ISBLANK([@Items]),"",RANDBETWEEN(100000,999999))

riny_van_eekelen · Answer

ShearaKLC&nbsp;Glad I could help! Don't hesitate to come back here for more help if you can't resolve something within 30 minutes yourself 🙂

riny_van_eekelen · Answer

ShearaKLC&nbsp;RANDBETWEEN is indeed (re)generating random numbers every time the sheet recalculates. Sounds like you need a VBA solution (i.e. macro) that generates a random number and puts the value in a cell as a fixed value every time a new value is entered in the Items column of the table. Probably doable, but not by me. Sorry!

hansvogelaar · Answer

ShearaKLC&nbsp;
Right-click the sheet tab.
Select 'View Code' from the context menu.
Copy the code listed below into the worksheet module.
Switch back to Excel.
Save the workbook as a macro-enabled workbook (.xlsm).
Make sure that you allow macros when you open it.
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim tbl As ListObject
    Dim col As Range
    Dim rng As Range
    Set tbl = Me.ListObjects("Table1") ' change name if necessary
    Set col = tbl.ListColumns("Items").DataBodyRange
    If Not Intersect(col, Target) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        For Each rng In Intersect(col, Target)
            If rng.Value = "" Then
                rng.Offset(0, 1).ClearContents
            Else
                rng.Offset(0, 1) = Application.RandBetween(100000, 9999999)
            End If
        Next rng
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

shearaklc · Answer

OMG thank you, I am new to all this and have learning difficulties, but trying desperately to learn to hopefully work in digital content.

Think I was missing a ) in some attempts or putting the = by Rand... So every time I tried fixing one place I had it wrong in another.

My health has been deteriorating meaning I might no longer be able to do my job in a few years, so trying to work on my skillset and things I can do from home, I don't really have higher qualifications so can't really find remote work. I had been stuck on this one for hours, where I can usually find the answers within 30 minutes. I do try finding the answers first without bothering anyone, but I was trying for hours.

THANK YOU SO MUCH

Forum Discussion

Combining "If" "" / If IFBLANK with another complex formula HELP

Share

Resources