User Profile
Winterkid
Copper Contributor
Joined Oct 10, 2022
User Widgets
Recent Discussions
need help on making a VBA/Macro script for searching and compiling data.
Hi All, thank you for taking your time to see this message. I hope it make sense. i need help in create a VBA/Macro for searching data and compiling data. i have 2 excel, Excel Test is where all the monthly report and sample consolidation report is From Excel Test SheetName PL_CompanyA, BS_CompanyA, PL_CompanyB, BS_CompanyB, PL_CompanyC, BS_Company C. Each Sheet contain table as below not limited to the table A. For PLSheet it contain such information for each month 2023, every month when the report generated, the New month will be added. For example, Feb 2023 is the latest month. For this Excel,in PL_Company A, i will need to search for the latest month follow by Sales (Column A / Row 3) , then i will need to get the amount from (Column C / Row 3). Next i will need to search for Cost of sales (Column A / Row 6) , then i will need to get the amount 6.00 from (Column C / Row 6). There will be alot of search in Column A for getting the different amount in each month and i will need to do for the same for PL_Company B and PL_Company C Sheets. Some have additional information to search from Column A. i believe i can modify once it. For BS sheet will contain such information. I believe i can do it once i know how to search and get the data from PL sheet. 2nd Excel sample consolidation report 2023_PL sheet,it contain table like this. From the data gotten from Excel Test PL sheet, i need to put them into here. I will need to search for Company A, follow by the month latest month. For example from the Excel Test the latest month is Feb, so i need to search for 2/28/2023. then from Sales that is the amount 6.00 from (Column C / Row 6) in in Excel Test PL Sheet, I will need to put 6.00 into this 2023_PL sheet Column C Row 4. and this goes on for the rest. In my testing excel, i have done up to this code, which i am planning to get into the consolidation report excel. Sub Get_Data_From_File() Dim FileToOpen As Variant Dim OpenBook As Workbook Application.ScreenUpdating = False FileToOpen = Application.GetOpenFilename(Title:="Browse for excel file", FileFilter:="Excel Files(*.xlsx*),*xlsx*", MultiSelect:=False) If FileToOpen <> False Then Set OpenBook = Application.Workbooks.Open(FileToOpen, ReadOnly:=True) OpenBook.Sheets(1).Range("A5:D55").Copy ThisWorkbook.Worksheets("Sheet1").Range("A5").PasteSpecial xlPasteValues OpenBook.Close False End If Application.ScreenUpdating = True End Sub Thank you And many thank for taking your time to read and understand. please do reach out to me if you need to explaining.Re: Question/Solution on Archive backup for Sharepoint Files/Folder that is last modified * 1 year ago
Hi DaveMehr365 , thank you for your reply. is there ways to have it backup to a local storage rather than another sharepoint site. Even after moving from the production sharepoint site to archive site, correct me if i am wrong, this still is in the overall total sharepoint storage space and does not reduce the storage even after archive.Re: Sharepoint Online Calculated column
HI RobElliott thank you for you reply. understanding your recommendation on creating a data list for the exchange rate. Due to the timeline i have which i need to start pilot testing with user start of next month, i maynot have enough time to make it. For now, i will still get my finance colleague to manually enter the exchange rate. in the TotalAmountSGD i have set formula in and it work for now. =IF(Currency="SGD",Cost,ConvertedSGDRate) if you have idea on how to create it please do let me know. attached picture is from my finance colleague for today exchange rate and they only see the middle rate.Question/Solution on Archive backup for Sharepoint Files/Folder that is last modified * 1 year ago
HI All, can i check whether is there a solution whereby we can only archive backup sharepoint online files/folder that was not touch like 1 year ago?? understand there we are to backup sharepoint online as microsoft only provide the recovery for DR. but my director does not have the intention of increasing the sharepoint online space. as of now we are at 1TB out of 2.5TB. and he would like to only archive and remove any files/folders that is not touch for 1 year. thank you!Re: Sharepoint Online Calculated column
RobElliott thank you for your fast reply. the exchange rate will be key in from powerapp application itself from my finance members. as our currency default is SGD my finance colleague will not be going into the application to manually key 1 into the exchange rate as it serve no purpose for them by doing so. If i set the default value 1 to the exchange rate then it will appear and calculate all the value which will provide false data to the employee before finance colleague update the exchange rate.Sharepoint Online Calculated column
Hi All, I am currently trying to use calculated columns whereby to do calucaltion. I want the calculated Column TotalAmountSGD, to calculated IF the Currency is SGD then Calculate the Cost of the, Else Currency is Not SGD then Calculate ConvertedSGDRate. can i know what is the correct formula should i put Currency Cost ConvertedSGDRate TotalAmountSGD(Calculated Column) SGD 100 100 SGD 200 200 YEN 18000 176.4 176.4 USD 50 71.78 71.78 YEN 8000 79.01 79.01 SGD 50 50 MYR 100 25 25 USD 150 1545.71 1545.71
