Working with workbooks shared via Teams/Sharepoint
Hello Excellers, I need some insight on an issue that I am not sure what the source is... A) We sometimes share workbooks via Teams. You know when you are in a particular chat and next to the name of the chat at the top of the screen you see Shared and then you see Files button a bit below the Shared menu and when you click on that Files button you will see a list of what workbooks are shared. B) So I wrote some VBA code to download a copy to the Downloads folder like that: Dim RetVal As Long Dim SharePointFileURL As String Dim LocalDownloadPath As String Dim FileName As String RetVal = URLDownloadToFile(0, SharePointFileURL, LocalDownloadPath & FileName, 0, 0) If RetVal = 0 Then MsgBox "File successfully downloaded to: " & LocalDownloadPath & FileName, vbInformation This will message be replaced by the code we need to run... but for now I needed an indicator that it finished downloading. Else MsgBox "Failed to download the file. Please check the URL or your network connection.", vbExclamation End If So RetVal is = 0, and I do see the workbook in the \Downloads folder and it has a size of 4KB and the correct Date Modified time stamp, BUT When I try to open the workbook via the Excel desktop application I get the message: Excel cannot open the file "File name here" because the file format or file extension is not valid. Verify that the file is not corrupted and that the file extension matches the format of the file. The workbook should be 34KB in size and that 4KB file is not what I need??? Any ideas how to manage this, saving a workbook from a SharePoint / Teams file location to the computer so that we can run VBA code on it. GiGi
Autocompletar em vez de trazer dados acima da célula, traz os que estão abaixo.
Olá Tenho especificamente uma planilha que no Excel web (Microsoft 365) em que o autocompletar não está funcionando da forma normal, isto é, trazendo os dados que estão na mesma coluna, acima da célula. Em vez disso, traz os dados que estão abaixo. Exemplo: Ao digitar "b" em A5, em vez de o autocompletar sugerir "beta", traz "ball". Alguém tem esse problema? Conseguiu corrigir? Como?Autocomplete has stopped working
I'm using the Office 365 (web version) of Excel to keep track of my paper trades. Until recently, I was able to populate the ticker name and symbol columns with previous entries by just starting to type their name. It would then "autosuggest" based on those previous ticker names/columns. Now, it doesn't do it anymore. I've tried to find a solution online but they often reference menu items that don't even exist. How do I fix this? I can always copy and paste but it's so much easier and convenient to be able to use this auto complete feature to populate with pre-existing entries.
SUMIFS function won't give me a sum for 1 text criteria and date range
I have been trying to create a formula that will pull the sum of my various expenses/income according to the their category and within a month's range. Here is the formula I wrote: =SUMIFS('Transactions'!E:E,'Transactions'!C:C,'Gifts','Transactions'!A:A,'>='&'1'!G2,'Transactions'!A:A,'<='&'1'!H2) I want the sum to populate on the sheet named '1' and I'm having it pull the info from the sheet named 'Transactions,' which has a masterlist of all my transactions for the year (both income and expense) which includes the columns date, paymentmethod, type, description, and amount. I have data validated date, paymentmethod, type, and amount. Column E is amount, C is type (which includes 'Gifts'), and A is date. G2 and H2 are start date and end date which I typed out as 1/1/25 and 1/31/25. They are formatted as dates and data validated. Every time I press enter on this it just shows what I typed with red dotted lines around it. Other basic functions work (e.g. sum) but this one won't. Any idea how I can get this to work?
How to resolve formula discrepancy Excel/SharePoint
Good afternoon, could someone help me? The formula =IF(ISERROR(INDEX(Form1!F:F, ROW()-90)), "", IF(INDEX(Form1!F:F, ROW()-90) = "", "", INDEX(Form1!F:F, ROW()-90))) works in offline Excel, but in SharePoint/Excel Online it shows an error. It appears there is an error in the formula. I am using Portuguese in both versions.
Excel For Web: "Picture > Place In Cell" is Gone
Hi, I have Excel for the web. A few months ago, when I was using Excel for the web, the Insert > Pictures > Place In Cell option became no longer available on my Windows 11 laptop. I don't think it has anything to do with any new updates to Excel for the web, as I'm able to use Place In Cell on my iPhone (where none of my desktop images are stored). Here is a reference of what I'm trying to do: https://www.youtube.com/shorts/V0tX83Zm-kY?feature=share How can I update my excel for the web to return it to it's original settings, allowing me to use the place picture in cell feature?
Excel online - is there a possibility to preserve cell formatting on update of a pivot table?
Dear all, In standard MS excel desktop app there is a possibility to preserve cell formatting on update of a pivot table. The procedure is easy, I need to go to pivot table options and make sure that the field "preserve cell formatting on update" is ticked. However in MS excel ONLINE version I could not find this option. That is problematic, because at my work we are using pivot tables in excel ONLINE version and after every refresh we have to restore formats of pivot tables manually and that is time consuming. Please, does anyone know if there is a possibility to preserve cell formatting on update for excel ONLINE and how to achieve this? Best Regards VitCreate a drop down list in Excel on Sharepoint
I keep trying to create a drop down list in Excel on Sharepoint by going to data validation, select list and then I select the data for the list in the 5 rows I created on another tab (just like you do in excel when it isn't on sharepoint) but I keep getting a data validation error. What the hell is going on?
Create 'parent' table to update data in 'child' worksheets + data in 'child' worksheets must tally
Hi all - I am working on a Cloud Excel file to collaborate with many other parties (who are all basic users of Excel). The purpose of the worksheet is to create an assessment/checklist template, to assess a large volume of submissions/projects. Hence, I require the parties to provide their assessment criteria to a 'parent' worksheet. This 'master' worksheet should be automatically duplicated to other 'child' worksheets to assess individual project submissions. In the 'child' worksheets, there will be additional columns for the collaborators to fill out, to assess the individual project submissions. The problem is that the 'parent' worksheet is still subject to changes e.g. adding/deleting rows, re-arranging rows, etc. At the same time, the collaborators are giving inputs in the additional columns in the 'child' worksheets. Is there a way to ensure that the additional data in the 'child' worksheet still tallies with the correct row of the referenced data/table from the 'parent' worksheet? An example of what I am trying to do in the 'child' worksheet (i.e. specific is as below. Thanks!! A B C D 1 Referenced from Parent sheet) Collaborator's Department (Referenced from Parent sheet) Assessment Criteria (Child sheet) Remarks specific to individual project submission (Child Sheet) Checkbox for collaborators to tick off if assessment criteria is applicable to project submission 2 Dept A Criteria 1(1) [For Dept A to remark] <Checkbox> 3 Criteria 1(2) [For Dept A to remark] <Checkbox> 4 Dept B Criteria 2(1) [For Dept B to remark] <Checkbox> 5 Criteria 2(2) [For Dept B to remark] <Checkbox> Above is a 'child' worksheet. Columns A & B will automatically be updated from a 'parent' worksheet (a "Master" template) Columns C & D will be editable in the 'child' worksheet. Inputs in columns C & D will be unique to the individual 'child' worksheet. If Dept A adds a new row between Rows 3 & 4 in the 'parent' worksheet, I need the 'child' worksheet to create the new row between Rows 3&4, so that the custom inputs in Columns C & D (e.g. Cells C4, C5, D4, D5) will still refer correctly to Dept B's criteria, after Dept A adds the new row.
