Transferring data based on specific date

Copper Contributor

Hi,

I am creating a data base, but I am stuck at point where I want data to be copied to another sheet based on date. e.g. if I enter a data on 02/12/2021 then in the database sheet it should find the date 02/12/2021 and then paste all the details in that specific column. 

8 Replies

@KashifRiyadh Without seeing the file you are working on, I suggest you look into one of Excels lookup functions. LOOKUP, VLOOKUP, INDEX/MATCH or XLOOKUP. Which one to choose depends on the structure of the data and the Excel version you have. You might even have access to the new FILTER function.

@Riny_van_Eekelen thank you for your reply.

 

Kindly find attached my sample file.

 

best regards,

Kashif

@KashifRiyadh I chose INDEX/MATCH. See column D on the Entry Sheet.

@KashifRiyadh 

If the database were a database table rather than a worksheet name some of the referencing would be more descriptive.  There is a 'gotcha' with using dates as field names though, in that they get converted to text.  So, following up on @Riny_van_Eekelen 's suggestions in the Excel 365 context

image.pngimage.png

XLOOKUP is more concise but INDEX/XMATCH/XMATCH has the advantage of being able to return multiple columns as an array.

@Peter Bartholomew @Riny_van_Eekelen 

Guys first of all thank you and sorry for late reply.

 

I have attached the sheet again, kindly look into it. 

 

Regards,

Kashif

@KashifRiyadh Sorry! Don't understand what you want. Perhaps @Peter Bartholomew .

@KashifRiyadh 

You will need a VBA macro to perform such tasks.  The technical reason is that worksheet formulas implement a functional programming style in which values may be read and calculations performed, but nothing that already exists may be changed.  By way of contrast, VBA implements an imperative programming style that allows the state of the information held by the workbook to be changed.  In particular VBA will allow you to replace data within a database table as you require.