Forum Discussion
excel and access
=IFERROR(INDEX(datasu!$E$2:$E$1000,SMALL(IF((datasu!$F$2:$F$1000=SHEETST1!$F$2)*(datasu!$G$2:$G$1000=SHEETST1!$G$2)*(datasu!$H$2:$H$1000=SHEETST1!$H$2)*(datasu!$I$2:$I$1000=SHEETST1!$I$2)*(datasu!$J$2:$J$1000=SHEETST1!$J$2),ROW(datasu!$E$2:$E$1000)-1),ROW($A1))),"")An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula is in cell H11 and filled down in the attached file.
=IFERROR(VLOOKUP($H11,datasu!$E$2:$J$1000,COLUMN(B$1),FALSE),"")This formula is in cell I11 and filled across range I11:M19.
For the dropdown reference i've created lists of unique items in columns M to Q.
Which version of Excel do you work with and how many rows are there in your actual sheet? The formula should work well with thousands of rows.
=IFERROR(INDEX(datasu!$E$2:$E$1000,SMALL(IF((datasu!$F$2:$F$1000=SHEETST1!$F$2)*(datasu!$G$2:$G$1000=SHEETST1!$G$2)*(datasu!$H$2:$H$1000=SHEETST1!$H$2)*(datasu!$I$2:$I$1000=SHEETST1!$I$2)*(datasu!$J$2:$J$1000=SHEETST1!$J$2),ROW(datasu!$E$2:$E$1000)-1),ROW($A1))),"")An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula is in cell H11 and filled down in the attached file.
=IFERROR(VLOOKUP($H11,datasu!$E$2:$J$1000,COLUMN(B$1),FALSE),"")This formula is in cell I11 and filled across range I11:M19.
For the dropdown reference i've created lists of unique items in columns M to Q.
Which version of Excel do you work with and how many rows are there in your actual sheet? The formula should work well with thousands of rows.
my friend thank you very much for your help. is it possible to do this with acces, so I want to make an access connection with excel.I want my data that is in Access to be fetched according to the data on my sheetst1 page. OliverScheurich
i'm sharing a link because access gave an error when uploading my file.I am also sharing the virus results of my file
https://dosya.co/os7l54d45ikh/Database1.accdb.html
https://www.virustotal.com/gui/file/35fe0702f36005f870976d6cae2000caaa1c6cdcc725ec5b4528eea93bb0a1ab?nocache=1
as access is a relationship database，you can query data by sql like below。
select * from Sayfa1 where OBJECTID=1 and TANIM like 'main artery' and SERIT='2t' and CINS='asphalt' and SHAPE_Length=177;
You are welcome. I don't have Access because i work with Office 2013 Home and Student. Therefore i can't help making an Access connection with Excel unfortunately.
