populating list and formatting

[Matt]

I am trying to create a spreadsheet that will be used for billing purposes.
Part of the print out page needs to show equipment used for the job. On
another tab I have the list of all of my available equipment. When I begin to
create the bill I want to go to the tab that lists all of my equipment and
hourly rates and either put a "Y" or "n" or somehow note which items were
used in this case. I then want to be able to go back to my main sheet (the
one to be printed) and see in my equipment section, only the pieces used and
not all of the other ones. I have been able to get the active pieces of
equipment to display using if statements but I want to delete the rows
automatically which are left blank due to the IF statement. Is this possible?

 

[Brad Vogt]

This is a little involved, but worked for me to eliminate blank cells that
result in the if function returning a "".

Next to the first cell that is blank or could possibly have a value "W7 in
this case":
=IF(COUNTBLANK(W7)=1,0,1)

The next cell below that in the vertical list:
=IF(COUNTBLANK(W8)=1,0,SUM($V$7:V7)+1)

Then fill that formula down. The result is 1 next to the first value, then
2, then 4, then 8...etc.

Use the following formula to determine how many values you are going to have
in the list to lookup. Replace 50 with the exact number of possible values.
=50-COUNTBLANK(W7:W56)
in cell W60 for this example.

You can then use the vlookup function to lookup the those values in the
table, which would exclude the blank cells. Place the following formula to
the right of the 1 in the table that you enter below.

=IF(U61>$W$60,"",VLOOKUP($V61,V$7:W$56,2,))
U V
50
1 1 formula here
2 2
3 4
4 8
5 16
6 32
7 64
8 128
9 256
10 512
11 1024
12 2048
13 4096
14 8192
15 16384
16 32768
17 65536
18 131072

Fill the formula down and you have your list without blank cells in between.
Just hide all of the extra cells that you needed to make this happen and use
a simple =W61 formula and fill down to whereever you want the data to appear.
Good luck!