Row / Array

[Altzz]

Hey,

This is my first post on here and I hope someone can help me as this is making my brain hurt!

So I want to make an array formula that returns the row number if someone has entered a number 1 in a cell. The problem I'm having is i need the formula to look at different columns depending on a drop down box list.


so far I have {=IFERROR(SMALL(IF(1=N:N, ROW(N:N)-ROW($N$6)+1), ROW(6:6)),"")}

This is the first few columns of my sheet it goes on for the full school year my and the data in the drop down boxes will be the dates from this sheet

Maths	English	Cre	Gego	science	etc
18th sep	19th sep	20th sep	etc	etc	etc
1		1			1
	1
1				1
			1

<tbody>
</tbody>

I'm knew to posting so if you need anymore information just let me know

 

[Worf]

Re: Row / Array - Help!!

Welcome to the Board

Your first column has two filled rows, is this going to happen?


team

	A	B	C	D	E	F	G	H
1	Math	Geog	Sci	Eng			Row number
2	18/09/2018	19/09/2018	20/09/2018	21/09/2018			5	Desired date:
3				1				20/09/2018
4	1
5			1
6		1

<tbody>
</tbody>

Spreadsheet Formulas

Cell Formula G2 =MATCH(1,INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(H3,2:2,0),4),"1","")&":"&SUBSTITUTE(ADDRESS(1,MATCH(H3,2:2,0),4),"1","")),0) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4




<strike></strike>

 

[Aladin Akyurek]

Re: Row / Array - Help!!

@ Altzz

Is this input? If so, care also post the output that goes with that input?

 

[Altzz]

Re: Row / Array - Help!!

Hey,

Thanks for getting back to me and I sort of worked it out another way (with the help of a number of other threads)

I used =IFERROR(AGGREGATE(15,3,ROW($F$2:$F$180)/($F$2:$F$180<>""),A2),"")

if anyone wants it explaining I'll try my best :D