How to link separate Excel files to auto-populate cells

Can you upload sample csv file(s) and the workbook clearly showing what you are trying to do?

Thanks TMS, I think that would be the plan. These files will be created, the data entered, saved and then not modified again, so I think it could work, just need help figuring out what formulae I need to apply.

Take care,
chrlyd

chrlyd,

Too hard to even guess.

csv file might have duplicate Band_Name etc, so not one to one relations.

In my real-life use, there are no duplicate Band_Names or Song titles.

Originally Posted by chrlyd

In my real-life use, there are no duplicate Band_Names or Song titles.

Create a new upadated csv file in the same folder.
Import updated csv in a new worksheet for demo.

Thanks, Jindon. I don't see the formulae that's being used to achieve this. To make use of this I would be entering my data in a second sheet on the Mastersheet workbook and then copy and paste the data into a new csv file?

Thanks for your help!
chrlyd

Originally Posted by TMS

There is a slight problem here. A cell can have a value, or a formula, but not both. So, if you provide the Band_Name, you could have a formula to lookup the Band_Name and return the other three values . . . but to separate cells. Similarly, if you provide the Song you could have a formula to return the other three cells. Effectively, you'd be relating one or the other of the search elements.

You could use a FILTER to get the details from the Master workbook.

Are you ever missing both the Band Name and the Song? Or, do you ever have both? What if more than one band have recorded a song with the same name? Does that ever happen (in your data)? Obviously it does IRL.

Thanks, TMS. I'll look into the FILTER option. I'm never missing both Band Name and Song, and I can have both but am trying to simplify the data entry so that I don't need to type both (and the corresponding album name and release year cells). In my real-life use I won't have any duplicate band names or song titles. Sounds like I would be better off if I could try to always enter band name and have the other cells auto-populate from that information?

Okay, see attached files with an example more aligned with my real-life application.

Thanks for your help,
chrlyd

The newly created csv file is the one updated the original csv file from workbook data in the same folder. (main job)
And new sheet crated is the imported data from newly created csv for the purpose of demo.

I don't understand about formula etc.

Originally Posted by jindon

I don't understand about formula etc.

Thanks Jindon,

The demo works well. My question regarding the formula is that I don't know how to apply this to my actual data. What formula do I enter and where to achieve these results with my data and as a I create new data sheets.

Originally Posted by TMS

Corrects an error and makes the formula a little more resilient.

Formula:

=LET(Instructions, "Amend Workbook, Worksheet and Range, as required",  Mdata, [Example_Mastersheet.xlsx]Sheet1!$A$1:$D$1000,

 MA, INDEX(Mdata,SEQUENCE(ROWS(Mdata)),1),  MB, INDEX(Mdata,SEQUENCE(ROWS(Mdata)),2),

 COut, IF($F2<>"",FILTER(Mdata,MA=$F2,FILTER(Mdata,MB=$G2,"no match")),

       IF($G2<>"",FILTER(Mdata,MB=$G2,"no match"),"no match")),

 I_34,IFERROR(INDEX(COut,,{3,4}),""),I_3412,IFERROR(INDEX(COut,,{3,4,1,2}),""),I_34)

Thanks, TMS

It doesn't look like this formula is filling in the name columns, though it works well for filling out the other data. Is there a way to add this capability to the formula? I tried adding the formula to my datasheets, and it worked initially but is now just filling the blanks with "0"... I would appreciate a bit more explanation on the formula you created so I could understand it a bit better to adjust it to my uses as needed. I can see where to amend the workbook, worksheet, and range, but what part of the formula is specifying which cells of the mastersheet to autofill in the datasheet?

Take care,
Chrlyd

My understanding to your requirement is:

1) Enter data in Sheet1 in master.xlsm
2) When button clicked, updates csv directly from the data in Sheet1. (no need to open csv)
Currently, creates new csv instead of overwriting the original csv.
If it is confirmed, it can be overwritten the original csv.
4) Showing the updated result in new worksheet only for your visual demo.

So, I thought 2) is the main purpose.

Am I missing something?

Originally Posted by jindon

My understanding to your requirement is:

1) Enter data in Sheet1 in master.xlsm
2) When button clicked, updates csv directly from the data in Sheet1. (no need to open csv)
Currently, creates new csv instead of overwriting the original csv.
If it is confirmed, it can be overwritten the original csv.
4) Showing the updated result in new worksheet only for your visual demo.

So, I thought 2) is the main purpose.

Am I missing something?

So, the mastersheet should remain relatively unchanged with maybe a few additions as new plant species are found (I do have other headings from the example file I sent, so thanks for the additional code). I think I understand what to do now - just edit the same .csv file and run the code which will create a new csv. I have never used macros before, so this is new to me. I would like to better understand how to apply this on my own, as I have other data that will be entered in a similar way, but with a different format to the example datasheet.

I am running into an error with the additional code you sent, "Run-time error "9": Subscript out of range, showing here on the code: If temp(ii - 1) Like "*,*" Then

Thanks again for all your help!

Originally Posted by TMS

Just change the last entry from I_34 to I_3412

Formula:

=LET(Instructions, "Amend Workbook, Worksheet and Range, as required",  Mdata, 'C:\Test\[Example_Mastersheet.xlsx]Sheet1'!$A$1:$D$1000,

 MA, INDEX(Mdata,SEQUENCE(ROWS(Mdata)),1),  MB, INDEX(Mdata,SEQUENCE(ROWS(Mdata)),2),

 COut, IF($F2<>"",FILTER(Mdata,MA=$F2,FILTER(Mdata,MB=$G2,"no match")),

       IF($G2<>"",FILTER(Mdata,MB=$G2,"no match"),"no match")),

 I_34,IFERROR(INDEX(COut,,{3,4}),""),I_3412,IFERROR(INDEX(COut,,{3,4,1,2}),""),I_3412)

Thanks TMS,

I made that adjustment and it kind of worked, though the information was not in the correct columns. When I adjusted the formula to refer to my actual mastersheet, rather than the example sheet, it just had a lot of "0"s and "no match"s. I assume I must need to adjust the columns it's referring to within the mastersheet and where that info will be autopopulated within the datasheet, but I'm not sure what to edit.

OK, can you replace "test" sub procedure with the below and see how it goes.
You can add/remove column(s), but Col.B & C should always stay there as the key columns.

Sub test()
    Dim fn As String, a, b, s, x, y, temp
    Dim i As Long, ii As Long, dic(1) As Object
    For i = 0 To 1
        Set dic(i) = CreateObject("Scripting.Dictionary")
        dic(i).CompareMode = 1
    Next
    a = Sheets("sheet1").[a1].CurrentRegion.Value
    b = Application.Index(a, 1, 0)
    For i = 2 To UBound(a, 1)
        If a(i, 1) <> "" Then dic(0)(a(i, 1)) = Application.Index(a, i, 0)
        If a(i, 2) <> "" Then dic(1)(a(i, 2)) = Application.Index(a, i, 0)
    Next
    fn = ThisWorkbook.Path & "\example_datasheet.csv"
    x = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fn).ReadAll, vbNewLine)
    y = Split(CleanCSV(x(0), Chr(2), Chr(3)), ",")
    For ii = 1 To UBound(b)
        b(ii) = Application.Match(Replace(b(ii), ",", Chr(2)), y, 0)
        If IsError(b(ii)) Then MsgBox "Field " & a(1, ii) & " is missing", vbCritical: Exit Sub
        b(ii) = b(ii) - 1
    Next
    For i = 1 To UBound(x)
        If x(i) <> "" Then
            y = Split(CleanCSV(x(i), Chr(2), Chr(3)), ",")
            For ii = 0 To 1
                s = Replace(y(b(ii + 1)), Chr(2), ",")
                If dic(ii).exists(s) Then temp = dic(ii)(s)
            Next
            If IsArray(temp) Then
                For ii = 1 To UBound(b)
                    If temp(ii) Like "*,*" Then temp(ii - 1) = Chr(34) & temp(ii - 1) & Chr(34)
                    y(b(ii)) = temp(ii)
                Next
            End If
            x(i) = Replace(Replace(Join(y, ","), Chr(2), ","), Chr(3), """")
        End If
    Next
    fn = Replace(fn, ".csv", "_Updated.csv")
    Open fn For Output As #1
        Print #1, Join(x, vbNewLine);
    Close #1
    ImportCSV fn
End Sub

Originally Posted by TMS

I can only create solutions for what I am given, not what you are working with IRL.

If you provide samples of the csv and master file that are truly representative of your live data, I’m sure it can be adapted.

Last point: your sample Master file indicated the lookup columns were A:B. If that's not the case, then the formula will not be looking in the right place.

Thanks, TMS

I understand you're limited to what I can provide. I was naively hoping the solution would be simpler than it turned out to be. I thought I would be able to learn a new formula or tool to auto-populate the information I needed and I would be able to modify the solution to fit my live data as well as a couple other data sheet formats I have that can be auto-populated from the same master sheet. I can link all my real data, if you think that's the best option

Yes, the lookup columns will always be A:B. I just have one other column in there. One issue is this is a new study, and there may be additional data added or removed depending on what we find useful. So, I was hoping to learn how the auto-populate function works a bit better so I can make adjustments as needed.

Thanks for all your help so far.

Chrlyd