Help help help please please please

[helpme1992]

Hi all

In need of help! I have a spreadsheet containing dates in columns (12/09/16 in cell A1, 19/09/16 in cell A2 etc). Underneath each date (which is effectively the column header) I have ten rows of numbers. I need to be able to type a date into a cell on another sheet and have a formula that automatically brings up those ten rows of info onto the other sheet. Any Excel wiz's out there that could help I would be SO SO SO SO SO appreciative!!!

THANK YOU

M

 

[steve the fish]

You could use something like:

=INDEX(Sheet1!$1:$11,ROWS($A$1:A2),MATCH(A$1,Sheet1!$1:$1,0))

Where Sheet1 houses the data and A1 houses the date you need to search for.

 

[GTO]

...I have a spreadsheet containing dates in columns (12/09/16 in cell A1, 19/09/16 in cell A2 etc)...

If you have dates in both A1 and A2,...

...Underneath each date (which is effectively the column header) I have ten rows of numbers...

...then how can you have ten rows of numbers? For instance, if there are ten rows of numbers under the date in A1, then A2 no longer has a date.

Maybe I am terribly misreading your post, but it is not making sense to me

Mark

 

[helpme1992]

Hi Steve,

Thank you for your response! I made a slight error in my question; the columns are formatted - 12/09/16 in A1, 17/09/16 in B2 (NOT A2). The ten rows of data are underneath each date (column header). Would your formula still work?

THANK YOU! LIFE SAVER!

 

[steve the fish]

Date in A1 then B2??

 

[helpme1992]

OMG sorry im such an idiot! A1 then B1 then C1 etc, ten rows of data underneath each header

 

[steve the fish]

Yes ok my formula will work there. You just need to change the sheet name to whatever sheet holds your data.

 

[AlanY]

this is how steve's solution works

Excel 2012
	A	B	C	D	E	F	G	H
1	12/09/2016	19/09/2016	13/09/2016	20/09/2016	14/09/2016		date	13/09/2016
2	20	20	11	10	16			11
3	20	10	16	19	15			16
4	16	15	10	11	20			10
5	12	18	10	14	10			10
6	19	11	12	19	15			12
7	11	18	18	11	19			18
8	17	14	13	16	19			13
9	10	12	10	19	17			10
10	20	20	11	16	19			11
11	15	19	18	10	16			18
Sheet6

Cell Formulas
Range	Formula
H2	=INDEX($A$2:$E$11,ROW(H2)-1,MATCH($H$1,$A$1:$E$1,0))

 

[helpme1992]

Thank you Steve!

So when a date is inputted into a specific cell, the formula will pull those ten rows of data that corresponds to the date? Effectively there is to be one cell at the top of a sheet that a date is inputted, this then pulls that info. When that cell containing the date is changed to a different date -the data relating to that new date will automatically show replacing the previously inputted date and data?

Thank you for you on-going help!!

M

 

[steve the fish]

You will need 10 formulas but if you copy it down ten times then that is precisely what will happen. If the date is changed the formula results will change.