User Profile
lardo5150
MicrosoftJoined Aug 03, 2020
User Widgets
Recent Discussions
How to Lookup a column and match in another list?
Can someone tell me if this is possible, and if so, how to do it. I have two SPO Lists. Assignments and Engineers Engineer List holds all my engineer data (email address, name, etc). Assignments List is where we add assignments. Assignments List has a column called AssignTo. This has the email address of the engineer in it. I want to create a lookup column in Assignments called Engineer_UPN. I want to look at the Assignments List column called AssignTo, find the email address that is there and match it with the one in the Engineer List, and take a value from Column TestABC in the Engineer List and put it in Assignment List Column Engineer_UPN. Is this possible?3.9KViews0likes1CommentRe: Conditional Formatting with Scripts and Power Automate
Is there a limit with scripts? I found if I run the following my conditions work up to a certain column, then they dont work. If I clear the rules for the entire sheet, and enter the formatting into a column that was not working, it will work. let selectedSheet = workbook.getActiveWorksheet(); // Clear conditional formats from range M:AG on selectedSheet selectedSheet.getRange("M:AG").clearAllConditionalFormats(); let conditionalFormatting: ExcelScript.ConditionalFormat; // Change custom from range M3:M55 on selectedSheet conditionalFormatting = selectedSheet.getRange("M3:M55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+1,2)>5"); // Change custom from range N3:N55 on selectedSheet conditionalFormatting = selectedSheet.getRange("N3:N55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+2,2)>5"); // Change custom from range O3:O55 on selectedSheet conditionalFormatting = selectedSheet.getRange("O3:O55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+3,2)>5"); // Change custom from range P3:P55 on selectedSheet conditionalFormatting = selectedSheet.getRange("P3:P55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+4,2)>5"); // Change custom from range Q3:Q55 on selectedSheet conditionalFormatting = selectedSheet.getRange("Q3:Q55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+5,2)>5"); // Change custom from range R3:R55 on selectedSheet conditionalFormatting = selectedSheet.getRange("R3:R55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+6,2)>5"); // Change custom from range S3:S55 on selectedSheet conditionalFormatting = selectedSheet.getRange("S3:S55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+7,2)>5"); // Change custom from range T3:T55 on selectedSheet conditionalFormatting = selectedSheet.getRange("T3:T55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+8,2)>5"); // Change custom from range U3:U55 on selectedSheet conditionalFormatting = selectedSheet.getRange("U3:U55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+9,2)>5"); // Change custom from range V3:V55 on selectedSheet conditionalFormatting = selectedSheet.getRange("V3:V55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+10,2)>5"); // Change custom from range W3:W55 on selectedSheet conditionalFormatting = selectedSheet.getRange("W3:W55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+11,2)>5"); // Change custom from range X3:X55 on selectedSheet conditionalFormatting = selectedSheet.getRange("X3:X55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKDAY(Today()+12,2)>5"); // Change custom from range Y3:Y55 on selectedSheet conditionalFormatting = selectedSheet.getRange("Y3:Y55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+13,2)>5"); // Change custom from range Z3:Z55 on selectedSheet conditionalFormatting = selectedSheet.getRange("Z3:Z55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+14,2)>5"); // Change custom from range AA3:AA55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AA3:AA55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+15,2)>5"); // Change custom from range AB3:AB55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AB3:AB55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+16,2)>5"); // Change custom from range AC3:AC55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AC3:AC55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+17,2)>5"); // Change custom from range AD3:AD55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AD3:AD55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+18,2)>5"); // Change custom from range AE3:AE55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AE3:AE55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+19,2)>5"); // Change custom from range AF3:AF55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AF3:AF55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+20,2)>5"); // Change custom from range AG3:AG55 on selectedSheet conditionalFormatting = selectedSheet.getRange("AG3:AG55").addConditionalFormat(ExcelScript.ConditionalFormatType.custom); conditionalFormatting.getCustom().getFormat().getFont().setColor("#9C0006"); conditionalFormatting.getCustom().getFormat().getFill().setColor("#000000"); conditionalFormatting.getCustom().getRule().setFormula("=WEEKEND(Today()+21,2)>5");8.9KViews0likes1CommentRe: Conditional Formatting with Scripts and Power Automate
Ok, looks like the record action for the second section (marking it as weekend) is working. i was able to record that, delete the condition, and run the script, that worked. I still need assistance with the text conditions in the first section9.2KViews0likes0CommentsConditional Formatting with Scripts and Power Automate
I have an Excel Spreadsheet that two users use each day. They do a LOT of ctrl C and ctrl V. Because of this, it starts messing with the conditional formats we have. My idea was to create a script inside of Excel Online, save it, then user Power Automate each night to run that script to fix all the conditional formats. The script would delete the CFs from two sections of the sheet, and then recreate them. I have zero idea how to write these scripts, so I was using the record actions feature. It records me creating the CF, but when I run the script to test it, it always errors. See attached. What I am not understanding is I am not changing these scripts after I stop recording so I am not sure what is happening. Goal: Script the following: Delete all rules in section J3:L55 Delete all rules in section M3:AG66 For section J3:L55, create a rule that highlights cells that have text like: Sun-Wed OO* OFF* Wed-Sat Med* etc.. For the other sections, I have a rule for each column that turns the cells black if a certain cell is a Weekend. The actual condition looks like this: Apply to range M3:M55 =WEEKDAY(R2C13,2)>5 Then I do that for every other column (the C changes in the formula above). When I try to create these with the recorder they are failing.9.7KViews0likes3Comments
Recent Blog Articles
No content to show