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

%3CLINGO-SUB%20id%3D%22lingo-sub-3376570%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376570%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390664%22%20target%3D%22_blank%22%3E%40ShearaKLC%3C%2FA%3E%26nbsp%3BTry%20this%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(ISBLANK(%5B%40Items%5D)%2C%22%22%2CRANDBETWEEN(100000%2C999999))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376588%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376588%22%20slang%3D%22en-US%22%3EOMG%20thank%20you%2C%20I%20am%20new%20to%20all%20this%20and%20have%20learning%20difficulties%2C%20but%20trying%20desperately%20to%20learn%20to%20hopefully%20work%20in%20digital%20content.%3CBR%20%2F%3E%3CBR%20%2F%3EThink%20I%20was%20missing%20a%20)%20in%20some%20attempts%20or%20putting%20the%20%3D%20by%20Rand...%20So%20every%20time%20I%20tried%20fixing%20one%20place%20I%20had%20it%20wrong%20in%20another.%3CBR%20%2F%3E%3CBR%20%2F%3EMy%20health%20has%20been%20deteriorating%20meaning%20I%20might%20no%20longer%20be%20able%20to%20do%20my%20job%20in%20a%20few%20years%2C%20so%20trying%20to%20work%20on%20my%20skillset%20and%20things%20I%20can%20do%20from%20home%2C%20I%20don't%20really%20have%20higher%20qualifications%20so%20can't%20really%20find%20remote%20work.%20I%20had%20been%20stuck%20on%20this%20one%20for%20hours%2C%20where%20I%20can%20usually%20find%20the%20answers%20within%2030%20minutes.%20I%20do%20try%20finding%20the%20answers%20first%20without%20bothering%20anyone%2C%20but%20I%20was%20trying%20for%20hours.%3CBR%20%2F%3E%3CBR%20%2F%3ETHANK%20YOU%20SO%20MUCH%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376589%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376589%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390664%22%20target%3D%22_blank%22%3E%40ShearaKLC%3C%2FA%3E%26nbsp%3BGlad%20I%20could%20help!%20Don't%20hesitate%20to%20come%20back%20here%20for%20more%20help%20if%20you%20can't%20resolve%20something%20within%2030%20minutes%20yourself%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376591%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376591%22%20slang%3D%22en-US%22%3E%3CP%3ELooks%20like%20the%20formula%20won't%20work%2C%20as%20in%20not%20suitable%20for%20what%20I%20need%2C%20as%20it%20changes%20every%20time%2C%20I%20add%20a%20value%20in%20the%20next%20row%20below%2C%20I%20need%20it%20to%20stay%20assigned%20to%20the%20column%20once%20entered%20%3CLI-EMOJI%20id%3D%22lia_disappointed-face%22%20title%3D%22%3Adisappointed_face%3A%22%3E%3C%2FLI-EMOJI%3E%20coding%20isn't%20my%20strength%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376599%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376599%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390664%22%20target%3D%22_blank%22%3E%40ShearaKLC%3C%2FA%3E%26nbsp%3BRANDBETWEEN%20is%20indeed%20(re)generating%20random%20numbers%20every%20time%20the%20sheet%20recalculates.%20Sounds%20like%20you%20need%20a%20VBA%20solution%20(i.e.%20macro)%20that%20generates%20a%20random%20number%20and%20puts%20the%20value%20in%20a%20cell%20as%20a%20fixed%20value%20every%20time%20a%20new%20value%20is%20entered%20in%20the%20Items%20column%20of%20the%20table.%20Probably%20doable%2C%20but%20not%20by%20me.%20Sorry!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376665%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376665%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1390664%22%20target%3D%22_blank%22%3E%40ShearaKLC%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ERight-click%20the%20sheet%20tab.%3C%2FP%3E%0A%3CP%3ESelect%20'View%20Code'%20from%20the%20context%20menu.%3C%2FP%3E%0A%3CP%3ECopy%20the%20code%20listed%20below%20into%20the%20worksheet%20module.%3C%2FP%3E%0A%3CP%3ESwitch%20back%20to%20Excel.%3C%2FP%3E%0A%3CP%3ESave%20the%20workbook%20as%20a%20macro-enabled%20workbook%20(.xlsm).%3C%2FP%3E%0A%3CP%3EMake%20sure%20that%20you%20allow%20macros%20when%20you%20open%20it.%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20Dim%20tbl%20As%20ListObject%0A%20%20%20%20Dim%20col%20As%20Range%0A%20%20%20%20Dim%20rng%20As%20Range%0A%20%20%20%20Set%20tbl%20%3D%20Me.ListObjects(%22Table1%22)%20'%20change%20name%20if%20necessary%0A%20%20%20%20Set%20col%20%3D%20tbl.ListColumns(%22Items%22).DataBodyRange%0A%20%20%20%20If%20Not%20Intersect(col%2C%20Target)%20Is%20Nothing%20Then%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20False%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20False%0A%20%20%20%20%20%20%20%20For%20Each%20rng%20In%20Intersect(col%2C%20Target)%0A%20%20%20%20%20%20%20%20%20%20%20%20If%20rng.Value%20%3D%20%22%22%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20rng.Offset(0%2C%201).ClearContents%0A%20%20%20%20%20%20%20%20%20%20%20%20Else%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20rng.Offset(0%2C%201)%20%3D%20Application.RandBetween(100000%2C%209999999)%0A%20%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20%20Next%20rng%0A%20%20%20%20%20%20%20%20Application.EnableEvents%20%3D%20True%0A%20%20%20%20%20%20%20%20Application.ScreenUpdating%20%3D%20True%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376669%22%20slang%3D%22en-US%22%3ERe%3A%20Combining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376669%22%20slang%3D%22en-US%22%3EBeen%20avoiding%20Macro%20as%20worried%20about%20compatibility%20issues%20with%20others%20I%20might%20send%20the%20file%20to.%3CBR%20%2F%3ETrying%20to%20see%20if%20I%20can%20have%20preset%20number%20entered%20somewhere%20when%20the%20items%20is%20filled%20in%20it%20prefills%20with%20those%20random%20numbers.%20Or%20add%20the%20numbers%20to%20the%20false%20not%20blank%20itself.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3376531%22%20slang%3D%22en-US%22%3ECombining%20%22If%22%20%22%22%20%2F%20If%20IFBLANK%20with%20another%20complex%20formula%20HELP%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3376531%22%20slang%3D%22en-US%22%3E%3CP%3ESo%2C%20I%20am%20trying%20to%20assign%20random%20codes%20to%20products%20in%20excel%20with%20the%20RANDBETWEEN%2C%20however%2C%20I%20don't%20want%20it%20to%20add%20a%20random%20number%20unless%20there%20is%20text%20in%20the%20Item%20section%2C%20every%20formula%20I%20try%20online%20gives%20me%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20selected%20empty%20first%20Item%20Table%20Cell%20comes%20as%3A%3C%2FP%3E%3CP%3E%3D%5B%40Items%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEmpty%20cell%20to%20the%20right%3C%2FP%3E%3CP%3E%3D%5B%40%5BAssign%20Reference%5D%5D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20combine%20if%20%22%22%20or%20If%2BIFBLANK%20with%26nbsp%3BRANDBETWEEN%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20if%26nbsp%3B%3D%5B%40Items%5D%20is%20blank%20then%20leave%20blank%20but%20if%20not%20add%20Random%20Arrawy%20between%20100000%20and%20999999%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEverything%20I%20try%20gives%20one%20or%20another%20error%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3376531%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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.