Excel question

[homer]

I have spreadsheet A with hidden columns, some columns with formulas etc. 

I need to update the data in spreadsheet A with raw data from Spreadsheet B. Easy enough to copy and paste...but what I might have to do later is update A when the data from B is updated (let's call that C). and then again for D, E,F and so on. 

wat I would love to do is simply download a new version of B and then A automatically updates. So link A to B and everytime B changes, A does as well. 

I was able to get it to work but then I couldn't sort in A because I created an array I guess and sort didn't like that. 

There has to be a semi easy way to do this right? I'm not an excel expert by any means.

[homer]

I think I got it...I was almost there and didn't know it. I didn't realize the data I imported into A was still tied to the external source B. changed B and it updated A. 

this fantasy baseball research has gotten complicated

edit: gah! sort still doesn't work. I can connect to external data but I can't sort it.

[Vgmastr]

How are you doing it?  A vlookup should work for what you're trying to do.

[nate82]

If you have them linked it will save the filename and location into the cell.  All you have to do then is keep the filename the same every time and then refresh in the workbook you want the information to be refreshed in.  I believe you can select refresh on open in the settings of Excel.  

You can also create a pivot table off of the information and that will allow you to sort and filter without limitations.  

[zurch1818]

I'm not exactly sure I follow what you are doing but I think a vlookup or better yet index match would accomplish what you want.

Side note...if you are a fan of vlookup, I highly recommend learning index match. The format is pretty much the same, but way more powerful.  It can actually accomplish an hlookup too and you can choose your lookup column (instead of the first column all the time and you can insert columns without breaking the formula. 

[zurch1818]

https://youtu.be/yH_ArqoB0no

[homer]

thanks for the suggestions. I got it to work but in retrospect I think I was making it more difficult than it had to be. 

I went to the data tab, selected Get Data and chose my CSV. then it takes you through a sort of wizard and you can exclude columns from importing, add columns, formulas, sort etc before you even bring the data into your spreadsheet so that's what I did and it worked out great. Now when my CSV file updates on the front end, everything auto updates in the spreadsheet which is what I wanted.

I think a vlookup would have done the same thing. In the beginning I thought I was going to have to use data from two different databases so I think that's why I went the route I did. I like excel but I am a design guy so I don't get into it too often. Part of the reason I tried this out is because I think it'd be useful for some work projects. Yeah  I do dorky stuff like this on the weekends.

[nate82]

3 hours ago, homer said:

thanks for the suggestions. I got it to work but in retrospect I think I was making it more difficult than it had to be. 

I went to the data tab, selected Get Data and chose my CSV. then it takes you through a sort of wizard and you can exclude columns from importing, add columns, formulas, sort etc before you even bring the data into your spreadsheet so that's what I did and it worked out great. Now when my CSV file updates on the front end, everything auto updates in the spreadsheet which is what I wanted.

I think a vlookup would have done the same thing. In the beginning I thought I was going to have to use data from two different databases so I think that's why I went the route I did. I like excel but I am a design guy so I don't get into it too often. Part of the reason I tried this out is because I think it'd be useful for some work projects. Yeah  I do dorky stuff like this on the weekends.

If you were doing this for work an application like Alteryx is a better option even R, Python or SAS would be better options if you want to go the coding route.

If you needed more analysis you could then have Alteryx export the information as a hyper file which is what Tableau uses.  Alteryx creates the hyper file which would then update your Tableau workbook.