Pre fill data to sheet based on date input

[michaelharvey10]

hi all,

New here and fairly new to excel. Can do all the basic sum and countif formulas but not much after that.

I am trying to make sheet "daily summary" fill in all the data to match the data on the sheet "stats" dependant on the date that is entered at the top. So if I were to input the 16/12/2019 on the daily summary, it would pull all the data from the stats sheet from that dates column.

If any one could point me in the right direct to sort that would really appreciated.

Thanks
Michael

Daily Summary.JPG

stats.JPG

[Aardigspook]

Welcome to the forum.

You can use Index-Match for this. The format is this:
=INDEX ( the table you want to get information from , MATCH ( data to look up to find the right row , the column of the table to find this in , 0 ) , MATCH (date to look up to find the right column , the row of the table to find this in , 0 ))

1. In this case, the table you want to get information from is: Stats!$A$1:$Z$24 (change the 'Z' to the last column of your table - or further to the right if you want to be able to extend it later without having to go back to amend your formulae).
2. The data to look up to find the right row changes for each cell on the Daily Summary sheet. For example, for D6 it is the cell containing the label 'Total Calls' - that is, cell C6. This will change with each lookup, so you can leave it as a relative reference - that is 'C2' not '$C$2'.
3. The column you want to find that data in is: Stats!$A$1:$A$24 (the first column of the table - note that the number of rows must be exactly the same as the whole table in point 1 - in other words don't make it $A$3:$A$24 for example).
4. The date you want to search for, in other words cell D2 - this is a fixed cell, so needs to be an absolute reference $D$2.
5. The row you will look up the date in is: Stats!$A$2:$Z$2 (as for point 3, the number of columns must be exactly the same as the whole table in point 1 - don't make this $B$2:$Z$2 for example).
6. The zeroes in the two Match parts of the formula tell Excel to search for exact matches.

Putting this all together gives you this formula for cell D6:

Please Login or Register  to view this content.

You can then copy and paste the formula to the other cells (D7, D8, ... G12, G13).

Note that for the row lookup to work (point 3) the cells on your Daily Summary sheet must use exactly the same labels as on your Stats sheet. For example, if you change 'Total Calls' to 'Call Total' on one sheet but not on the other, the formula will return an '#N/A' error.
Similarly for the column lookup to work (the date) the dates on your Daily Summary sheet and Stats sheets must either both be real dates or both just text - don't use real dates on one sheet and text on the other. The display format doesn't matter.
(Explanation if you're unsure about Excel dates: A 'real' date in Excel is just a number, with '1' meaning 1st Jan 1900 - so today, 17th Dec 2019, is 43816 - which you can see if you format a date cell as 'General'. If you format a date cell as 'General' and it keeps showing (for example) '17/12/2019' then it's text, not a 'real' date.)


Index-Match is a good formula to learn - it's very good for pulling information out of a range of data. There are loads of tutorials on the internet if you want to learn more.


Hope that helps (and makes sense).