i know how to add a drop down list (using validation).. but i am having a problem adding a formula to it... i want the drop down list to display "25% OF TOTAL, 50% OF TOTAL, 75% OF TOTAL, 100% OF TOTAL"... than when one of those 4 are selected... it automatically finds that value in the cell where the total is, multiplies it times .25, .5, etc.. then puts in that dollar amount in the cell.. thanks for the help

tony

Hi, This would best be asked in the Office Forum,

but here goes anyway:

You can't have the cell with the drop-down list (data validation) also hold a formula, so your formula must be in another cell.

Example:

Total in Cell A1

Drop down list in cell B1

Formula in cell C1Create your validation list with multiply values in the next column:

G H 1 Drop-down text Value 2 25% of total 0.25 3 50% of total 0.5 4 75% of total 0.75 5 100% of total 1In cell C1, the formula will lookup the value in the table and return the corresponding value from the next column. The VLOOKUP function will do this.

The formula is:=A1*VLOOKUP(B1,G2:H5,2,FALSE)When you select, say 25% of total in cell B1, this formula will lookup the 25% message in column G2 to G5 and return the value in the second column of the table, returning 0.25 which is multiplied by the Total in cell A1.

To stop this returning an error if you don't select anything in cell B1, wrap the function in an IF(ISERROR() function:

=IF(ISERROR(A1*VLOOKUP(B1,G2:H5,2,FALSE)),"",A1*VLOOKUP(B1,G2:H5,2,FALSE))

This only returns a calculated result if there is no error.

As A1 * blank returns an error, this formula returns a blank instead.Here is the 25% result for a 'total' of 88.4 in cell A1

A B C 1 88.4 25% of total 22.1

Regards

Wow.. that was it.. fantastic... since the formula is behind the scenes.(drop down text and value info) only there to be displayed in another cell .. can i hide those cells.. cause when i goto print, all those show up.. i realize i can put on another worksheet.. but if i can keep on the same sheet, but just blank/hide em.. that would be great

thanks

tony

Hi, For some reason validation lists must be on the same worksheet as the cell(s) with the validation, but you can hide them.

Just select the two columns with the table and right click and hide.

As far as I can tell they will not show or print.

You could move the table (validation text/values to a higher column or a lower row, well off your normal view, but you will still need to hide the columns or rows to stop them printing, unless you define your print area.

Try moving the table to say column X, then go back to the main working area of your spreadsheet.

Select the area that you will be printing, then File Menu - Print Area - Set print area.

Now, when you print, only this area will be printed.

Also Print Preview will be restricted to this area and you can setup the print to fit a page, to have headers and footers, such as file name, date printed and to be centered, (File - Print Preview, then Setup... button at top).

Regards

Ask Your Question

Weekly Poll

Do you think Google Fiber has a strong future?

Discuss in The Lounge

Poll History