User Profile
liverlarson
Brass Contributor
Joined 6 years ago
User Widgets
Recent Discussions
Re: record contact form entries to Microsoft List
Hmm. Ok, hitting up database idea was in order to reduce moving parts - what seemed to me to be more potential points of failure - but I suppose the reliability/complexity balance is to be considered. "Once I have the data" indeed - the reason I'm trying to do this is to bring contact forms together from multiple sites and do stuff with them - automating from Lists has a lot of opportunity. Thanks for comments.2.7KViews0likes0CommentsRe: record contact form entries to Microsoft List
Thanks for reply. Do you know if there is a way to avoid polling the email? I've read that sometimes email doesn't send, even when someone fills out form, and so it's more reliable to hit the database directly, where entry is recorded.2.8KViews0likes2CommentsRe: workbook and sheet name via formula
yeah, VBA is supported in O365 - this question is specific to Excel documents being intereacted with through a web browser (Excel online) though. The CELL function which works on desktop does not work with online (weirdly), and same is true of VBA. It's a pretty small detail (CELL formula not working online), not a critical functionality, just annoying to me, because I literally use the same header on every document as a matter of best practice.300KViews0likes1CommentRe: workbook and sheet name via formula
SergeiBaklan Hmm, wasn't aware of office scripts. From my 30 seconds of research it looks like they are 1) pretty new, 2) basically VBA/macros for online If I did an office script to accomplish this functionality, does that in any way translate to the workbook when I download it? Such a simple thing. Seems weird to have to use an online-specific solution. As an aside, do you happen to know why CELL doesn't work online?300KViews0likes1Commentworkbook and sheet name via formula
I have a standard header I put on almost every excel workbook I create I have been using for years. It includes several standard items, including Workbook name and Worksheet name, which are extracted via CELL("Filename",A1) formula. For Excel workbooks that are being viewed in Excel Online, however, whether through onedrive, sharepoint, or whatever, this formula yields a #VALUE! error. When I open in desktop, it renders fine, just not when looking at browser window. Is there an alternative that I can use to display current workbook and worksheet names via a formula in a cell that is compatible with Excel Online?Solved336KViews1like63CommentsRe: Which code to use?
excelnoob298 ok, so other way around. You've got one dataset, and you want to copy each person within that dataset to their own sheet. From the sounds of it, the names on the single dataset are not known, so you're looking for a way to automatically scrape the names from the main dataset, and use each one to create a distinct page just for that name. If this is correct, than I would say we're talking about automation more than formulas, and that means VBA. This is something I don't typically get into on forums, as solutions tend to be custom and complex, and requires a fair amount of background knowledge for any of it to be helpful. So, I would bow out of this conversation and let someone chime in who likes writing/consulting on VBA and macros. The other option that comes to mind is leveraging PowerQuery and the M language, but again, it's going to be pretty technical to get to a workable solution. The only other option that comes to mind is that instead of copying EACH person to a sheet of their own, create a REPORT page, where you select a person, and the page poulates with that person's data, so you can just select from a drop down of person names, and run them one at a time. That could be done with a pivot table, or PowerQuery, or just a bunch of formulas that look to the selected person control cell you set up on the report page. Forgive me for being obtuse, but there are a lot of options and details, and without a concrete example, all I can do is paint in broad strokes. It is certainly possible, but for a "noob" I would say it is a lot to bite off.1.2KViews0likes1CommentRe: Excell Format Protection and Formula Question.
Richard_James 1) the feature is under review tab on ribbon, called Protect Sheet. When you select that, you will be presented with a bunch of options on what users can and cannot do, including formatting. To ensure that users can enter data, you will want to go to the cell properties of the cells where you want them to be able to enter data, and on the Protection tab of the cell properties window uncheck "locked" BEFORE you protect the sheet. When you protect the sheet, while protected, only those activities that were permitted can be done. You also have the option to set a password to unprotect if you have curious users - otherwise they can easily just unprotect the sheet themselves. 2) There is no simple answer to formatting the ordinal suffix, in my experience. You can either come up with a complex formula to deal with all the various possible outcomes and append the suffix, or just go without it, like "SUNDAY #4". If you have a pretty finite list of positions, a simple lookup table will be the simplest, containing two columns, one with integers, and the other the string counterpart with correct suffix. (and obviously, you can hide this from view so it's doesn't clutter your interface - I use these kinds of reference tables all the time, and usually just stick them on a Reference sheet and hide the sheet, calling them by formula only. If you google "excel ordinal formatting" you'll get all kinds of options, all of which are various levels of ugly.1.7KViews0likes0CommentsRe: Which code to use?
Based on this description, it would be difficult to provide specific options, but based on what I'm reading, there are potentially a lot of ways to handle this. If I'm understanding your situation, you download hours for 30 employees, but it comes as an excel workbook, where each employee has a sheet of their own, and you are trying to combine all of that into one sheet without all the manual navigating and copy/pasting. Is that correct?1.2KViews0likes3CommentsRe: facing issue to pull data from particular website
going to need a lot more detail to get help from here. What is the website? Is it behind a paywall or login? Are you calling an API or just scraping data from the page content? Excel version? Screenshot? copy of workbook (if there's nothing sensitive in there)?650Views0likes0CommentsRe: Please help with a formula
I believe you are not getting responses because of a lack of information. In your screenshot, where is the formula? What is it showing with your current attempt, and what would you expect it to show? (note that in your screen shot, rows and columns are not even visible, so it will be hard to reference that image in terms of cell D8 should be showing XXX. You may want to provide a different attachment for additional context.789Views0likes0CommentsRe: Structured references on tables / with modern excel - need syntax help
just a second parting thought - XLOOKUP will work for anyone with the latest version of Excel, but if I wrote that and someone not completely up to date on Excel version tried to open the workbook, it would not work. @Table1[#Headers] I'm assuming is backwards compatible, which is always an important consideration in a corporate setting for a while until it's unreasonable to not be up-to-date, which will be several years into the future. (Granted, with the wide adoption of O365 that window shrinks!)8.2KViews0likes3CommentsRe: Structured references on tables / with modern excel - need syntax help
lol, That is old-school. Yes, this had occurred to me, but defeats the purpose of structured references. B$2 does not have any implicit meaning, where [#Headers] tells me exactly what the context is without having to look at the table or data or know where it is on the canvas. Thanks for the contributions!8.2KViews1like0CommentsRe: Structured references on tables / with modern excel - need syntax help
I was sure the new way Excel is handling arrays was at root, but couldn't get the syntax to force it to intersect like it used to do by default. @Table1[#Headers] was exactly the syntax I was looking for. Thanks for the contribution!8.2KViews0likes0CommentsRe: Structured references on tables / with modern excel - need syntax help
@Table1[#Headers] is what I was looking for. I had tried Table1[@Headers] and various alternatives. I've not been up to speed on latest features. I did hear about XLOOKUP and the new array functionality which is why I even knew what the spill error was about, but haven't really used latest version of Excel for more than a day yet. Looks like it's good syntax that hearkens back to the pre-2003 LOOKUP function, but with better features (and actually works).8.2KViews0likes0CommentsRe: What formula returns the value of a cell but takes as parameters the output of another formula?
you could use INDIRECT for this. Be careful though, it's a volatile function, and if overused can become quite taxing on CPU. It would look like =INDIRECT("B"&[your function that returns row number])793Views0likes0CommentsRe: CPU USAGE
a lot of factors here. Likely much too much to speculate on. However, if the act of adding a row causes CPU to spike, you probably have a problem of overusing volatile formulas. Plenty of documentation out there on them just google around, but the summary is that due to the nature of a specific set of formulas, they must recalculate when any change is made to the workbook, where other formulas are smart enough to only re-=calculate when a change is made that actually effects their result.900Views0likes1CommentRe: How to automate a timesheet template?
I would take Riny_van_Eekelen 's example and take it a bit further. I prefer to separate form from function in a case like this. What I mean by that is make the data collection/input as straight forward as possible by just entering new records on a table. Perform your calculations on the table (function), and present the information however you want (form). See attached for example of what I mean by that (forgive total lack of formatting finesse). In this case, you would just capture the person, date, task, and time in/out on one record. In the example, the order DOES matter. You can have different people entering their time in between, but the entries for any given person must be chronological. This also does not incorporate any safety for things like timestamps that overlap or are backwards. You would want to use validation or something similar to prevent bad data entry.6.4KViews0likes0Comments- 2.9KViews0likes6Comments
Recent Blog Articles
No content to show