-
Decimal to Binary or Working with IPs
Good morning, all. I'm trying to put together an input form to validate IP addresses we get from our customers. This would be to automatically check if, for instance, the gateway is actually in-range of the provided IP/Netmask combo, which is a common mistake we see. I don't see any built in functions that can work with IP…
-
Random Select Formula?
Hello everyone! Does anyone know if it is possible to randomly select a row? My use case is a drawing for tickets. I want to be able to have people fill out a form and then use a formula to randomly select a winner. I would use "Auto Number" column to identify the person. Looks like it can be done in Excel -…
-
Want to track assigned tasks within date ranges and need formula help.
I'm close I think. Am tracking total tasks per team member (ASLs) and also task that are assigned within the next 5, 5-10, and 10-30 business days. The roadmap sheets this metrics sheet pulls from shows tasks owners and start and finish dates for each task. Current formula for showing all assigned tasks, regardless of…
-
How to write formula to pull current active phase or stage of the project with specific levels?
I found a formula on another post that allows me to pull the most current or active stage of the project with the least percentage done, higher than 0% and lower than 99%, but am only wanting to pull the task below the Phase and sub-phase/stage. I've created a helper column for levels and need to only index the task that…
-
I need help with a formula.
I have 2 sheets. Both sheets have dates and names. I need to compare the names, if both match then I need to compare dates, if they match I need to return a 1 to check a box. How do I go about writing this?
-
Cross-sheet reference limited to under 50,000 references
Excited about the increased cross-sheet reference limit to 100,000, I jumped on the bandwagon and started to replaced the Destination sheet LEFT(SUBSTITUTE(JOIN formulas with the Index/Match formulas using cross-sheet references. In this workflow, we are pulling data from 5 Source sheets into one “Central (destination)…
-
Formula to check a box if it meets multiple criteria in another sheet
Hi! I have two sheets: Sheet A: ID#, Date, and a checkbox Sheet B: ID#, Date Can someone please tell me what should the formula be if I want to Sheet A checkbox to check if Sheet A's ID# and Date match with Sheet B's ID# and Date. I tried a few different formula and it seems like it doesn't work: =IF(AND(MATCH([ID #]@row,…
-
Need Formula Help
Hi There! In need of a formula to that would sum time based on these columns: Resource Name Group Name Week Ending This is my time repository, I need to sum each resource's time based on Group Name and Week Ending (which is actually the Date Column, my Week Ending column has to be a text column) on another Sheet. Any…
-
Vlookup with Another Sheet
I have a sheet where I have two columns I would like to automatically populate based on the "job Code" column I have entered. In Excel, I used vlookup for the list of Job Codes to reference. I have uploaded those to a new sheet- how can I set up my vlookup to return the values I need? I keep getting and Unparseable error.…
-
Hide Error
Hi, I have a sheet with a couple of big weighty formulas. Sometimes the cells containing the formulas are not required to be used in the row and criteria might be missing in the row and so the error #INVALID VALUE appears as expected. How can I hide this error from the sheet as it shows up on reports and makes them look…
