excel
42548 TopicsSpilling an array with hyperlinks
I want to spill an array that includes hyperlinks to various places in the workbook. In principle something like this: =MAKEARRAY(3; 3; LAMBDA(Row; Col; HYPERLINK("#R" & Row & "C" & Col; "click") ) ) But something is off. A 3x3 array is spilled with clickable hyperlinks in each cell - but the hyperlinks all take me to A1 (R1C1). Clicking on the bottom right cell should take me to R3C3, for instance. What's wrong?6.5KViews1like4CommentsMicrosoft File Not Opening/Missing/Deleted OneDrive
I think my laptop may have crashed the last time I had this file open. When I search bar my computer, this file (sweets) shows, but won't open - following screenshots show error messages. It does seem to show the syncing logo on the left, but nothing happens when I click. The file doesn't show naturally in any folder, only when I use the search bar. I desperately need to restore it as there's lots of important info on there. Any help would be appreciated please.32Views1like1CommentPulling Data From ~300 Individual Sheets Onto a Master Sheet
Any all all help is appreciated! I’m operating out of a company SharePoint, as multiple people need to be able to access these sheets. I want to have a Master sheet, that tracks information from ~300 individual site portfolios. I want the Master sheet to be able to automatically pull about 30+ data points off each portfolio and drop them into a single row, in a specific order. One row for one portfolio. In my head, this works by me pasting a hyperlink (or something equivalent) in Column A, and all the needed data points automatically populating in the same row. This link serves two purposes. One, to provide a source for the Master to pull data points. Two, easy access to each site portfolio from the Master. Here’s a look at what I’ve tried so far: VLOOKUP with HYPERLINK: This one I’ve had a little bit of success with, but I’m running into a logistical issue. I can get this to work in the sense that data points pulled get from the linked portfolio in Column A and get dropped into the same row. The problem is the data points only update when I “Open in Desktop”. If I edit a data point in a site portfolio while in Sharepoint, nothing gets updated on the Master. This won’t work for me, as most of the time my team will be editing portfolio sheets in Sharepoint. Power Query: This one has promise, but I’m unfamiliar with PQ. Biggest hurdle I’m running into here is getting all wanted data points into a specific order in a single row. Each individual site portfolio has a ton of information, much more than what I want displayed on the Master sheet. With how much information in on each site portfolio, the data I want to pull is “all over the place”. Any advice on how to consolidate dispersed data into a single row using power query would be excellent. Using VBA: This is by far the option that I have the least amount of experience in and would like to avoid if possible. I’ve never been skilled in any sort of coding or messing with macros. However, I’m always willing to try and learn, so if this is the best option, please let me know what I can try. I appreciate the help from everyone. Please let me know if there are any questions or anything that I need to clarify. Thank you!28Views0likes1CommentCount of duplicates in a column
Good morning! Is there a formula that can be used to give me a count of duplicate values in a column? To be clear, I'm not trying to count known duplicate values that I can include in a COUNTIF formula, such as =COUNTIF(A:A),"Specific Text". I'm looking for Excel to simply count how many times it sees a duplicate of any kind. See attached.Solved222KViews0likes5CommentsCumulative Average of other percentages (non-contiguous range)
Please help me figure out a formula to calculate the cumulative average of the semester averages. Keep in mind that many of the semester grades will be 0 because we haven't reached those semesters yet. I need a formula that will show " the real time" program cumulative average.72Views0likes8CommentsTable of Data: Is Countifs my best solution?
Hi, I have a very large table showing models and PNs. A model may have multiple PNs. What I need to have is a concise list of all model numbers with the PNs they have in the same row. For example: Model A: PN1 Model A: PN2 Model B: PN1 Model B: PN3 What I need is: Model A: PN1, PN2 Model B: PN1, PN3 After I get the data in that format, I need to say, what models have PN1 and PN3? I have several hundreds of thousands of rows, so the countfs are really slow with calculating. Thanks!Solved97Views0likes10CommentsSelecting Data Based on Multiple Criteria
I need an Excel Formula Wizard to help me with data selection. Following is an example of my data: It is a list of leads that have been received through two different marketing channels (ECO Portal and Seminars and Roadshows). This is what I'm trying to accomplish: If an 'Account Name' has leads from both of the 'Lead Generators' of 'Eco Portal' and 'Seminars & Roadshows' (not just one of them) and the 'Closing Date' falls within the date ranges of the 'Created Date' and the 'Plus 6 mos' date, and the 'Status' is 'Won', then select the data and include 'Sales $' in total. Data should be summarized by Account Name. My spreadsheet has 32K records, so it is extremely slow going through these one by one to get what I am looking for. Thank you!142Views0likes13Comments