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

Copper Contributor

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

 

 

7 Replies

@ShearaKLC Try this:

=IF(ISBLANK([@Items]),"",RANDBETWEEN(100000,999999))
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

@ShearaKLC Glad I could help! Don't hesitate to come back here for more help if you can't resolve something within 30 minutes yourself :)

Looks like the formula won't work, as in not suitable for what I need, as it changes every time, I add a value in the next row below, I need it to stay assigned to the column once entered :( coding isn't my strength

@ShearaKLC 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!

@ShearaKLC 

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
Been avoiding Macro as worried about compatibility issues with others I might send the file to.
Trying to see if I can have preset number entered somewhere when the items is filled in it prefills with those random numbers. Or add the numbers to the false not blank itself.