excel and access

Occasional Contributor

Good day, I have a large amount of data (for example, I put 1000 lines of data, my normal file is 70 mb in size)I search the data in cells B9, C9 , D9 , E9 , F9 in Sheetst1 in the data sheet and call the same data from the cells A11 , B11 , C11 ,D11 , E11 , F11 in the datasu sheet but my file normally crashes because it's too big.I want to do this through ACCESS but I don't know how to do it. How can I load my data into ACCES application and call it from there to my sheets1? I have attached the sample file



5 Replies
best response confirmed by John_BloggsJohn_blk1405 (Occasional Contributor)



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.


This formula is in cell I11 and filled across range I11:M19.

main artery.JPG


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.


You can create a table in a new Access database with the same fields as the table in the datasu sheet, and import or copy/paste the data.

See the attached sample database.

I imported the data and built a simple filter form.

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. @Quadruple_Pawn 


i'm sharing a link because access gave an error when uploading my file.I am also sharing the virus results of my file


VirusTotal - File - 35fe0702f36005f870976d6cae2000caaa1c6cdcc725ec5b4528eea93bb0a1ab


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.

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;