Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Need help with formula
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. |
#2
|
|||
|
|||
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! In article , "Martc" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
IF formula? | Excel Worksheet Functions | |||
writing a formula for a colored value | New Users to Excel | |||
referencing named formula using INDIRECT function | Excel Worksheet Functions | |||
Simplify formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |