Need Help!!!

  • Thread starter Thread starter brad
  • Start date Start date
B

brad

This might be hard to explain, but here goes nothing.
I am creating a quote template which will consist of many
worksheets, which once completed only one sheet is visible
to the customer. There are 3 levels of service the rep can
select, from the "Options" Sheet. Each Level can contain
additional options. For Example: If Level one is selected,
then:

A1 B1
1 Option 1 (Y/N)
2 Option 2 (Y/N)
3 Option 3 (Y/N)
4 Option 4 (Y/N)
5 Option 5 (Y/N)

If Level 2 is selected, then it might look like:

A1 B1
1 Option 2 (Y/N)
2 Option 4 (Y/N)
3 Option 6 (Y/N)
4 Option 7 (Y/N)
5 Option 8 (Y/N)

The problem that I run into is if the user selects an
option, it will then appear on the "Quote" Sheet. But it
will not show up line after line. For Example if Options
2, 6, 8 are selected (from level 2) on the "Options"
sheet, the "Quote" Sheet will display it as:

F1
1 Option 2
2
3 Option 6
4
5 Option 8

Since the quote is for the customer, I need it to look
like:

F1
1 Option 2
2 Option 6
3 Option 8
4
5

I have tried using IF Statements to try to get this to
work, but it's not. Please help!! Thanks.
 
You could add a hidden column, to the left of the Option numbers on the
Options sheet. In cell A1, enter the formula:

=IF(C2="Y",MAX($A$1:A1)+1,"")
and copy down to the last row that would contain an option.

ON the Quote sheet, in cell F1, enter the following formula:


=IF(ISNA(VLOOKUP(ROW(),Options!$A$1:$B$9,2,0)),"",VLOOKUP(ROW(),Options!$A$1:$B$9,2,0))

and copy down as far as required, to display the selected options.
 
Back
Top