Feb 15 2021 09:01 PM
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.
Feb 15 2021 10:03 PM
@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.
Feb 15 2021 10:22 PM
@Riny_van_Eekelen thank you for your reply.
Kindly find attached my sample file.
best regards,
Kashif
Feb 15 2021 10:43 PM
@KashifRiyadh I chose INDEX/MATCH. See column D on the Entry Sheet.
Feb 16 2021 01:14 AM
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
XLOOKUP is more concise but INDEX/XMATCH/XMATCH has the advantage of being able to return multiple columns as an array.
Feb 17 2021 10:54 AM
@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
Feb 17 2021 11:29 AM
@KashifRiyadh Sorry! Don't understand what you want. Perhaps @Peter Bartholomew .
Feb 18 2021 03:47 AM
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.
Feb 18 2021 04:33 AM
Thank you @Peter Bartholomew & @Riny_van_Eekelen