Hi guys,
im doing a favor for a friend, and trying to add a dashboard to a spreadsheet for him
I've said to him multiple times this should really be a database and would make my life easier but it 100% has to be a spreadsheet
He has a work sheet set out like:
Ref | Customer name | address | more address | telephone number| Air con 1 | air con 2 | plastering 1 |plastering 2| Plastering 3| etc etc
So far, ive made a searchable drop down that brings back the Ref and address stuff. He can search the customer and get that info quickly instead of scrolling around a work sheet.
Ive made a second sheet to hold some formulas too make a dynamic search box
=IF(ISNUMBER(SEARCH(Dashboard!$D$5,'Site List'!B2)),MAX($D$1:D1)+1,0)
=OFFSET($H$2,,,COUNTIF($H$2:$H$93,"?*"))
used a few vlookup forumlas on the dashboard to pull back the address info like; =IF(LEN(VLOOKUP($D$5,'Site List'!$B$2:$H$93,2,FALSE))=0,"",VLOOKUP($D$5,'Site List'!$B$2:$H$93,2,FALSE))
Ive made a drop down based on all the other headings in the spreadsheet. making it so there is just one of each type ie Air con, Plastering, CCTV
what im trying to figure out is, how match the rows based on the values set in the first and second drop down and display them on the dashboard Does that make sense?
Many thanks for any help.
im doing a favor for a friend, and trying to add a dashboard to a spreadsheet for him
I've said to him multiple times this should really be a database and would make my life easier but it 100% has to be a spreadsheet
He has a work sheet set out like:
Ref | Customer name | address | more address | telephone number| Air con 1 | air con 2 | plastering 1 |plastering 2| Plastering 3| etc etc
So far, ive made a searchable drop down that brings back the Ref and address stuff. He can search the customer and get that info quickly instead of scrolling around a work sheet.
Ive made a second sheet to hold some formulas too make a dynamic search box
=IF(ISNUMBER(SEARCH(Dashboard!$D$5,'Site List'!B2)),MAX($D$1:D1)+1,0)
=OFFSET($H$2,,,COUNTIF($H$2:$H$93,"?*"))
used a few vlookup forumlas on the dashboard to pull back the address info like; =IF(LEN(VLOOKUP($D$5,'Site List'!$B$2:$H$93,2,FALSE))=0,"",VLOOKUP($D$5,'Site List'!$B$2:$H$93,2,FALSE))
Ive made a drop down based on all the other headings in the spreadsheet. making it so there is just one of each type ie Air con, Plastering, CCTV
what im trying to figure out is, how match the rows based on the values set in the first and second drop down and display them on the dashboard Does that make sense?
Many thanks for any help.