Bingo/Housie Ticket Generator

[amitadhiya]

http://chandoo.org/wp/2008/07/16/bingo-housie-ticket-generator-excel/

Hello all,

I'm a novice in Excel & this is my first visit to the forum. I need to generate Housie Tickets (see link above) but want to print customised text instead of the standard bingo numbers.

Can someone help me replace the random numbers generated in the fabulously written excel sheet - with the content I want. I mean, if I have numbers 1 - 90 in Column A and customised text against these cells in Column B, then how can I generate tickets and replace the content of the cells in one action?

I tried to Copy+Paste Special (values) to separate sheet and use VLOOKUP (after reading few tricks on the site) BUT (1) this seems to be a manual/mechanical process if I want hundreds of such tickets (2) more frustratingly, the blank cells return a #N/A error.

Also want to have an option if it is possible to keep only 6 columns (instead of 9 in the generator sheet) and have only 1-60 numbers instead of 1-90 with random 4 numbers (instead of 5) in each row - of course keeping the basic rule of a housie game intact of generating numbers 1-10 in 1st column, 11-20 in 2nd column, 21-30 in 3rd and so on, in ascending order.

Can someone be of help. (I have uploaded my attempted file)
Thanks a bunch in advance.

Amit

 

[Luke M]

I'd suggest adding a row to you lookup table, with formulas in the two cells:
=""
for both the lookup value and what to return. Now your VLOOKUP won't error out.

Alternatively, in the sheet that chandoo created, you could take his formulas and put it inside the VLOOKUP, so you don't need to copy/paste. Structure would be:
=VLOOKUP(OriginalFormula,NewList,2,FALSE)

 

[amitadhiya]

Dear Luke M

You guys are awesome. I jumped out of my seat on seeing the errors vanish by just by entering the formula ="" in two cells.

I still can't understand the structure of the formula you suggested to avoid the copy/paste. Do you suggest I edit the formula in the cells in Chandoo's sheet itself and place it within VLOOKUP - after keeping the NewList ('lookup' and 'returning' values) somewhere in his sheet? Or vice versa?

Thanks in advance
Amit