Program a cell reference from another sheet
I need to be able to take the formula "=sheet2!c12" and be able to change the "C" in the formula many times in the worksheet so I don't have to change it manually across the rows. I want a cell that will be able to change the "=sheet2!c12" C reference throughout anywhere I've programmed this. I was going to use "find and replace" but that's not only nooby, it's going to wind up being buggy. Is there where an INDIRECT formula works? I think the answer should be simple. Thanks!
show a due date using data from multiple columns
I have a spreadsheet in excel, such as the Table 1 below. For the 5 Due Date columns, I have conditional formatting set up to format the cells. See Image 1 for a list of rules applied to all Due Date columns. I'd like for another table to populate, showing only columns, Laboratory ID, Description, and any column that is highlighted per the conditional formatting rules. For example, I'd like the return table to look something like Table 2. Table 1. populated in cells G9:N12 ID Description Due Date 1 Due Date 2 Due Date 3 Due Date 4 Due Date 5 Out of Service? 1234 Instrument 1 11/17/2025 N/A 1/31/2027 3/11/2026 11/17/2027 FALSE 1235 Instrument 2 1/14/2026 2/17/2026 3/31/2027 1/23/2026 12/29/2027 TRUE Table 2. Conditional formatting rules applied to cells I9:M12 This Week = White font color, Red fill This Month = Red font color, Light Red fill Next Month = Dark Yellow font color, yellow fill Table 2. If today is 11/10/2025, the table will display columns ID, Description, and only column show for due dates within This Week: ID Description Due Date 1 1234 Instrument 1 11/17/2025
Finding time duration between a start date & time with end date & time
Hi all! I'm looking for any formula or power query to calculate a total time duration within a day, given the start date, start time, end date, end time. Most of the dates will equal the same but there are some with the end date being the next day. I'd like to be able to exclude any overlaps as well. Currently, I have a large embedded IF formula: =IF(AND($G4=$O4,$H4<$H5,$P4>=$H5,$P4<$P5,$H4<$H3),$P4-$H4,IF(AND($G4=$O4,$G4>$G3,$H4<$H3,$P4<$P3,$H4<$P3,$P4<$P5),$P4-$H4,IF(AND($G4=$O4,$H4>$H3,$H4>=$P3,$P4>$P3),$P4-$H4,IF(AND($G4=$O4,$H4=$P4),0,IF(AND($G4=$O4,$H4<$P3,$P4<=$P3),0,IF(AND($G4<$O4,$H4<$P3,$P4>$H4),($P4+1)-$P3,IF(AND($G4=$O4,$O4<$G5,$O4<$O5,$H4<$P3,$P4>$P3),$P4-$P3,IF(AND($G4=$O4,$G4>$G3,$H4>$H3,$P4>$P3,$H4>$P3),$P4-$H4,IF(AND($G4=$O4,$G4<$O5,$P4>$P3,$P4>$H5,$H4>$H3,$H4<$H5),0,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3,$P4>$P5,$P2>$P3),$P4-$P2,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$P3,$P4>$P3,$H4<$H5,$P4<$P5),$P4-$P3,IF(AND($G4=$O4,$H4<$H5,$H4<$P3,$P4>$P3,$P4>$P5),$P4-$P3,IF(AND($G4=$O4,$O4=$G5,$H4<$P3,$P4>$H5,$P4>$P3),$P4-$P2,IF(AND(ISBLANK($O4),ISBLANK($P4)),0,IF(AND($G4<$O4,$H4<$P3,$P4<$H4),($P4+1)-$P3)))))))))))))))) This seems to work for the most part but there are a few that I just can't get. I also pulled up my query and started to enter in the time durations manually and it couldnt come up with anything automatic for me. There must be an easier way for me to do this other than trying to create an IF formula for each answer that turns up incorrect. I have a screen shot below.Data Pulling
I'm trying to figure out the best way to pull data together from multiple tabs within an excel file. The file is made so that it can track issues within reports that are discovered. I'll try to break it down the best I can to explain what I have and what I'm trying to do. Within the file there are 5 tabs that are considered locations and they are named: LX, LV, CR, HR, FL Each tab is set up the same way with the only real important information in these columns of a table Column A - PCR Date Column C - PCR Owner Column G, I, K, M - Modification Category (1-4) these fields are actually drop down options from the Category Master List Within the file there is a 6th tab that has at able in it that is called Category Master List. Column A - Options for the dropdown in the above tab Modification Category columns Column B - Breakdown into one of three main categories (Billing, Compliance, Quality) Now this is what I'm trying to do: Fiscal Year Data (September 2025-August 2026) - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs. Monthly Data - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs along with how many times there is an entry with a date within that month. I would like to try and figure out how to put everything into one data table/chart type thing so it's easier to see and compare. Currently, everything needed is across a few different pivot tables and I would really like the data to pull itself automatically instead of me having to go in and copy all the data from one spot into another just to get the pivot results. Below is what I'm having to look at currently.How to write a script or any PQ or in Excel to download the zip files from a Webpage
Dear Experts, Greetings! https://www.etsi.org/deliver/etsi_ts/138300_138399/138306/ Could you please help me on how to download the pdf.zip files from above for all the versions? Using a single command in Excel or PQ-option. Thanks in Advance, Br, Anupam
TOCOL/TOROW Treat Thunks as Errors
I just reported a bug to Excel in which the TOROW and TOCOL functions, if asked to delete errors, will also delete valid thunks. ISERROR returns FALSE when presented with a thunk, so, arguably, TOCOL shouldn't treat it as an error either. I'm running Office 365 on Windows 11 on a Samsung Yoga laptop. Steps to reproduce: Paste this into a single cell in a spreadsheet: =LET(th, VSTACK(LAMBDA(4)), (@TOCOL(th,2))()) Expected behavior: should return 4. (Delete the ",2" and it does exactly that.) Actual behavior: #CALC error Impact: Breaks workflows for SCAN, MAP, BYCOL, and BYROW Workaround: Use FILTER and ISERROR Details: An Excel "thunk" is a degenerate LAMBDA with no parameters. So if we used LET to make f equal to LAMBDA(4), then f() would always return 4. If a thunk is the final result of a function, it generates a #CALC error, but it's fine for intermediate results. This seems useless, but it's the only way to get functions like SCAN, MAP, and BYROW/COL to return anything but a scalar. For example, I have a piece of code where I need to repeatedly square a large matrix and save the values for further processing. If I get a zero value, I can save a lot of processing by "aborting" the operation. Since you can't abort a SCAN, I just return #NA. Then I'd like to use TOCOL(result, 2) to strip off the unnecessary values. But TOCOL discards everything. I can work around this by using a combination of ISERROR and FILTER, but I shouldn't have to.Advanced Excel Formula discussion - Problem with dynamic range
Let's assume I want to rent a house. For each house, if the landlord has an agent, I'll contact the agent; If not, I contact the landlord directly. As below: Landlord Agent House Landlord James Mary W James Linda Michael X David Y Linda Z James Column F: =IFERROR(XLOOKUP(E2:E5,A:A,B:B,E2:E5),E2:E5) This is correct Column G: =XLOOKUP(E2:E5,A:A,B:B,E2:E5) #VALUE! for X-David Column H: =XLOOKUP(E2:E5,A:A,B:B,CHOOSECOLS(E2:E5,1)) Wrong value for X-David, because it returned the first value in range of XLOOKUP([if_not_found]) field My question is: the only difference between G and H is CHOOSECOLS(). If I put =CHOOSECOLS(E2:E5,1) or =E2:E5 in a separate cell, it gives me same result. However, in XLOOKUP, they are recognized differently. What is the logic behind this? Thank you for spending time on reading this.
