Non-Consecutive Cell Referencing
Hi, folks. I'm attempting to create a spreadsheet that contains links from consecutive cells to consecutive cells in another worksheet that are separated by 5 intervening cells. I'll call the original consecutive spreadsheet "Orig" (for original). So, I know that if I put "='Orig'!A3" in cell B3 and then copy that down, it will update the relative formula consecutively, i.e. B3='Orig'!A3, B4='Orig'!A4, B5='Orig'!A5, B6='Orig'!A6.... that much I get. What I need to do is find a way to do the same thing, but to increase the resulting link.....so that if I copied the formula down column B I would get: B3='Orig'!A3, B8=Orig'!A4, B13='Orig'!A6', etc so that the new worksheet is moving down 5 cells relative to the Orig sheet consecutive order. I've read where someone used a formula using the INDIRECT function but that's beyond my beginner level. Many thanks, and merry Xmas to all!
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
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.
My Percentage of total sales is not 100 - Why?
I have 20 categories of Sales Revenue in Column A, the total revenue for each category is in column B, the % of a category, as it relates to the total categories is in column C but the Total Percentage is over 100% when I total all the percentages, what am I missing? Amount % of Sales Backup & Recover-Desktop Suppor 70,808.50 2.2% Barracuda Back Up - Cloud to Cloud 11,220.00 0.35% Barracuda ESS - Security Edition $51,508.57 1.62% Barracuda Sentinel 20,093.52 0.63% Carrier Management / NOC Support Services $239,618.99 7.54% Cust Support Srvs/Provisioning/vCIO Svcs $98,514.00 3.10% Field Services Support-Single $1,447,941.00 45.56% FTE Tech 159,227.50 5.01% Hardware Lease Revenue 11,114.22 0.35% Help Desk Services 1,604,649.85 50.49% IH-New User_Laptop Setup $230,523.19 7.25% IH-Remote Work Support Services 60,172.85 1.89% MRR - Data Services/Ticket Overage $40,057.00 1.26% Network & Security Hardware $985,825.90 31.02% Network Monitoring Service-Devices 153,696.60 4.84% NRR - Carrier Management / NOC Services 11,245.00 0.35% NRR - ESS Email Security Services 28,865.00 0.91% NRR - Help Desk 46,500.00 1.46% NRR - Migration Implementation 98,595.00 3.10% NRR - Monitoring Services 15,000.00 0.47% Remote Server Monitoring 102,455.03 3.22% Remote Work/Sharepoint Migration $84,026.55 2.64% Shipping & Delivery Fee 49,970.63 1.57% Software/Licenses 491,422.97 15.46% TOTAL $3,178,015.20 192.35%If calculation returns a negative number, then it needs to be a 0; 2nd part is to show Max number
1. For row 45, I have calculation for 30% of row 42 ex. =F42*.30. If the result is a negative number, then I would like it show a 0 instead of the negative number. 2. Next, for row 48, the result of =F9-F45 can be no greater than F9. So if it is greater than F9, then F9 is the highest number that it should show, in this case $281, but should still show values 0 up to the amount of F9. Thank you
Excel formula help... again!
I have different tabs for each month. I need the total of the last day of month to start in the opening balance on the next tab in the beginning of the month. but some months obviously has 30 days and some 31 and then there's Feb too doing whatever it wants ;-) the total here in AH has to be the last day of the month - here it's the 30th and 379 the opening balance for the next tab (yellow block) has to be whatever the previous month's closing total was. I don't know how to create a formula to choose the last day's total and pull it to the new sheet in the opening balance. Thanks so much
move spaces to a given column via worksheet command
i am 81 yoa and my lady is sickly. Among other things she has type 1 diabetes and heart problems. I am building a worksheet containing foods and their calories and carbs. i want to blank out input a column when she has completed her food menu and is done inputting. How can I do that? I also want to protect all columns in the worksheet except the one for her input. I am using Microsoft Professional + 2013 Office on a pc via wireless in Windows 11. can anyone help me? I would be happy to attach a copy of the worksheet but i don't see how! Thanks in advance!
