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

Occasional Visitor

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

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

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

@ShearaKLC Try this:

``=IF(ISBLANK([@Items]),"",RANDBETWEEN(100000,999999))``

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

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

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

@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

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

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

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

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

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

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``````

# Re: Combining "If" "" / If IFBLANK with another complex formula HELP

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.