Help with IF Formula & Randbetween

[Yankeetribe]

Hello everybody,

I made a tab with a drop down menu in A1 with 3 Companies and 3 other tabs with the text products for each company.

I'd like to know how to select a random text (a product in my case) from another tab depending on the Company selected.

For exemple I tried this, but it doesn't seem to work:

=IF(A1="Company 1";"RANDBETWEEN('Products 1'!A2:A50)";"FAILED")

I know it's only for one Company, I'd like the formula to regroup all companies.

Also the products have a price in the cell next to them, I'd like to show the price from the random product selected!

Thank you for helping me! Here is the file:

[Roel Jongman]

To attach your file you cannot use the paperclip icon, it is broken..

you can however still attach a sheet bij clicking Go Advanced downright on the quickreply box
then scroll-down to "Manage Attachments" there you can upload your sheet.

That will be necessary to help you based on your description it is very hard to build a working formula

[Yankeetribe]

Thank you! I added the file.

[Roel Jongman]

One way to do it is with the attached method
Indirect function will help you build the tab and columnreference
the randbetween will handle selecting a random row
the substitute function replaces the word "Company" from cell A1 into "Products"

in the 2nd column the vloopup will lookup the price for the random item

(Press F9 to recalculate the sheet and have excel pick another random item)

For an easier to read formula you might want to call the product tabs after the company so company 1 instead of products 1 that will make it easer to go to the right worksheet

[JohnTopley]

Removed by JT

[Yankeetribe]

Wow thank you Roel that's it! I might have more questions in the future, but you solved it!

[Yankeetribe]

Is there a way to expend the formula on 10 rows but never have any duplicate in the randomly selected products?

Also I kinda understand how the formula works but if I change the companies name and tabs name of the tabs how can I adapt the formula? (They are all different)

[JohnTopley]

Changing tab names (to "Company 1" etc) AND getting random selection:

In G4 down enter numbers 1 to 50

in H4

=RAND()

copy down

in A4

=INDIRECT("'"&$A$1&"'!A"&INDEX($G$4:$G$53,MATCH(SMALL($H$4:$H$53,ROWS($1:1)),$H$4:$H$53,0))+1)

in D4

=VLOOKUP($A4,INDIRECT("'"&$A$1&"'!$A$2:$B$50"),2,0)

Using Roel's file .....

[Yankeetribe]

Ok I get it! Thank you very much!!

[Yankeetribe]

Hello again!

Is there another way to not have duplicates? The numbers in the same tab are no good for me, I guess I can move them in an another tab, right?

Anyway thank you very much guys, you are my heroes!