Hi Guys,
It's me again ...
I have a random name generator using this formula:
Is there any way of making the criteria determined by a month and year entered in B2. (instead of hard coding the 1,69 in the formula).
Please Login or Register to view this content.
So, if I enter 'March 2017' in B2 it looks column D of tab 'List of Names' and only randomly generates a return from Namelist if the date in column D is March 2017.
Ta!
Terry
Some sort of Match could probably do it, but without seeing a file, it's difficult to know.
So, please attach a sample workbook with just enough data to demonstrate your need, including a 'before' and 'after' sheet and/or comments to show what you're trying to achieve. Make sure you don't include any confidential data.
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Regards,
Aardigspook
I recently started a new job so am a bit busy and may not reply quickly. Sorry - it's not personal - I will reply eventually.
If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark this Thread as Solved'.
If you use commas as your decimal separator (1,23 instead of 1.23) then please replace commas with semi-colons in your formulae.
You don't need to give me rep if I helped, but a thank-you is nice.
I think you are going to need to provide your file. Your description leaves out some important details.
"Is there any way of making the criteria determined by a month and year entered in B2. (instead of hard coding the 1,69 in the formula)." What do you mean by "criteria" in this case? Using 1,69 seems to be a completely different issue that checking the date. The date example you gave seems unrelated to what numbers you use in RANDBETWEEN.
"a month and year entered in B2" Is the value in B2 a date formatted to display month and year? Or just a text string with a month and year?
"...if the date in column D is March 2017." Do you mean if the date is found anywhere in column D, or if it matches a particular row of column D?
Cheers 6StringJazzer (I can't even begin to imagine where that name came from)
Here's a file with Random data generated (from an online name generator - who knew!)
Just so you're aware, there's a macro in the workbook (that enables me to change the criteria of x axis to suit my needs).
Since you already have macros, I have provided a macro solution.
Column D is not a list of months, it is a list of dates. I assume that what you mean is that 'Random Selector'!B1 indicates a month (it's really a date, but you are just displaying the month) and you want to select at random from the names in 'List of Names'!E:E that have a date in D:D with a month that matches B1.
I have written function RANDNAME to do this.
You can also use this array formula to generate a random name from the designated month
=INDEX('List of Names'!E1:E500,SMALL(IF(EOMONTH('List of Names'!D1:D500+0,0)=EOMONTH(B1,0),ROW('List of Names'!E1:E500)),INT(RAND()*SUM((EOMONTH('List of Names'!D1:D500+0,0)=EOMONTH(B1,0))+0)+1)))
confirm with CTRL+SHIFT+ENTER
Audere est facere
6StringJazzer,
You Sir, are a gentleman and a scholar. I shall add to your rep. (as I am now viewed as the 'Excel Geek God' at work
Daddylonglegs, I thank you for your response too.
Just goes to show, there's more than one way of skinning a cat!
I started out trying to develop an array formula, because although I like VBA, I prefer formulas when possible. But I did not get as far as daddylonglegs, who provided an excellent solution. The choice of which to use is mostly a matter of preference for how you want the user interaction to work.
I'm glad to help and thanks for the rep!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks