Need help with formula

M

Martc

Hi
I have a spreadsheet and I am trying i am trying to pull out certain data.
It shows the registrations of the lorries in our fleet with a cell for each
day of the week. If a cell has a certain symbol we want it to copy the
registration for that row to a different table. An example of the sheet is
below:

M T W T F S S M T W
Reg1 /
Reg2

So as Reg1 has a "/" we want Reg1 to show on a different table. Please
help!!Thanks for your time.
 
D

Domenic

Assumptions:

A1:K10 contains your data

First row contains the day of the week

First column contains the registration number

Formulas:

A15:

=SUM(--(MMULT(--(B2:K10="/"),TRANSPOSE(COLUMN(B2:K10)^0))>0))

....confirmed with CONTROL+SHIFT+ENTER

B15, copied down:

=IF(ROWS($B$15:B15)<=$A$15,INDEX($A$2:$A$10,SMALL(IF(MMULT(--($B$2:$K$10=
"/"),TRANSPOSE(COLUMN($B$2:$K$10)^0)),ROW($B$2:$K$10)-ROW($B$2)+1),ROWS($
B$15:B15))),"")

....confirmed with CONTROL+SHIFT+ENTER

*Adjust the range accordingly.

Hope this helps!
 
Top