Is this function possible in excel template?

[dagoof]

I use an old excel 2000 invoice template (I know, I'm sorry but my uses are simple and I hate the newer bloat-ware versions ). I currently have 3 radial check buttons which I've set for payment options; cheque, credit card, bank transfer. Depending which is selected I have a standard line of relevant text in the 'fine print' box below.

I was wondering if it was possible to add a function whereby the 'fine print' would change automatically, relevant to whatever option I check?

 

[Saagar]

It can be done.

I assume you know how to tie the radio buttons to show a value in a cell.

Build a table with the option number and the associated fine print. Using the value in the cell above, you can use VLOOKUP to get the associated fine print.

 

[dagoof]

Thanks Saagar - actually, I'm pretty clueless about VB but I can follow directions if anyone would be kind enough to provide them.

I should also maybe note, not sure how I've screwed up the template in the past but when I change radio button selection I get a 1004 runtime error.

 

[Saagar]

There is no VB involved in the way I suggested. Are the radio buttons in your template "Control Toolbox" radio buttons or "Forms" radio buttons?

If you are unsure, right click on the radio button, if a menu pops up showing "Assign Macro.." option, it is "Forms" radio button.

 

[dagoof]

Looks like they're 'Forms' Saagar.

 

[Saagar]

Let's do it in steps.

1. Right click on one of the radio buttons and select "Format Control". In the "Control" tab, in the Cell Link field, choose any cell in the sheet or another sheet and click on "OK". Now if you select different radio buttons, the cell value should change. Lets call this cell "linked cell".

2. Build a table with two columns. The first column should contain the values that each radio button puts in the linked cell. Second column should be the fine print for that radio button.

3. In the cell that shows the fine print on your invoice template, put a VLOOKUP formula.
=VLOOKUP([linked cell from step 1]<linked cell="" from="" step="" 1="">,[table range from step 2],2,0)

This should get you what you want.<table range="" from="" step="" 2=""> </table></linked>

 

[dagoof]

Thanks - can I just clarify, is the table to be a new workbook or something constructed using the data>table function?

 

[dagoof]

Edit - I've done a table on the next sheet - a column with 1,2 & 3 (which is what the linked cell shows) and the corresponding text in the next column.

I'm guessing for the vlookup I need a way of telling it it's on the next sheet (if that's correct).

 

[Saagar]

Correct.

Excel does it for you. Start typing the formula in the cell, when you get to the second parameter, just go to next sheet and select the table range and resume typing the formula.

If you are having issues typing the formula, the way to refer to the cells in an adjacent sheet is "sheetname!A1:B5".

 

[dagoof]

Thanks, I'll try that tomorrow and let you know how it goes.