User Profile
bryen79
Copper Contributor
Joined 3 years ago
User Widgets
Recent Discussions
Re: How To Use VLOOKUP Using Date, To Find Corresponding Holiday With Additional Criteria
Hi I'm sorry! My ADHD is in overtime trying to figure out various ways to display what I need it to. I've since updated my original post with strikethrough and re-uploaded the photo and book. Since posting, I realized that if the holiday is on a Sunday, the formula can't look at the day before (Sat) because it wouldn't be on this report as it's the week prior. So I've now reduced this down to simply documenting that that day itself is a holiday. Instead of displaying the checkmark box, would it be possible to highlight the row orange? Then the user can manually be triggered by the colouring to manually review the previous week and current week to see if they qualify for holiday pay?13KViews0likes3CommentsHow To Use VLOOKUP Using Date, To Find Corresponding Holiday With Additional Criteria
I am looking for a way to do the following: display a checkmark box (using the corresponding cell reference in Sheet2) if the day is a holiday. 1. Use a given date in Sheet1 2. Go to Sheet2 and lookup the exact date from Sheet1 in table1, column1 3. If the date isn't listed in the table, end. (aka "") 4. Using date from Column 2, go back to Sheet1 5. If there is data present for the day before and the day after, display a checkmark box (I've already created cell reference for this which is located on Sheet2) 6. If there is data missing on either side of date, end. (aka no checkmark box will be displayed) Here's what I am thinking so far: =IF(VLOOKUP($B5,Sheet2!$D$3:$F$11,1,FALSE)="XXXX",Sheet2!$B$3,"") **The XXXX part is where I am stumped. I have tried entering $B5 but I get an error. I don't know how to use the value it's looking up to find it in the table and if found, simply display the checkmark box, otherwise, display nothing. Essentially the returned value (date if there is one), is then needed to look up on Sheet1 Column B, as a point of reference to see if both adjacent row (above and below) have data (time) in them, in order to display the checkmark box in Cell Q15. I've attached an illustration with a red arrow to show what I would like to appear if the conditions are met; as described above. Any better way of doing this? This sounds pretty logical to me but if someone has a better, easier, foolproof way to do this, I'm all ears! I've included the spreadsheet here on this forum, for ease of use in hopes it will or can be a simple solution. Link to another forum I have posted this question: https://www.mrexcel.com/board/threads/how-to-use-vlookup-using-date-to-find-corresponding-holiday-with-additional-criteria.1221584/Solved15KViews0likes5CommentsRe: How to duplicate a block of cells without losing formula references
Hi, sorry, if I may, could I ask for you to explain what these numbers do in the formula please? -14,31)+3,COLUMN(C13)-3) Once I understand them, I can tweak them for a different excel file I am working on. Thank you!3.4KViews0likes1CommentRe: How to duplicate a block of cells without losing formula references
OMG you're the best!!!!! I've never used INDEX or QUOTIENT before but this is perfect!! I texted it by adding another person for each day and it pulled the correct data as I used unique but random times to verify! Works like a charm! My hats off to you!!3.4KViews0likes2CommentsHow to duplicate a block of cells without losing formula references
Please note: I have posted this question on another website a few days ago and have not received any answers to it. I am hoping on of you kind folks here might know the answer. I am creating time cards and need 260 of them. I want to simply create one, and then copy paste, duplicating each copy/paste until I have 260. The problem is, formula references are taking account for cells in between each other. In other words, If I have a formula in A1 and A6, and data in the cells in between that also needs to be copied. I have attached the sample workbook and a photo showing what I want to copy and where. I need the formulas I copy down without loosing reference (staying sequentially correct) despite other rows in between have data. As it stands now, when I try to paste formula, it calculates all the lines in between the yellow boxes so numerically, it's not referencing the correct cell in the different sheet. I found this formula listed on another website post and wonder if this would work for what I need to do: =INDIRECT("sheet1!B"& ROW(A5)/5) I am using VLOOKUP and I tried this code: =IFERROR(VLOOKUP(B6,Sunday!$A6:$K6,6,FALSE),"") However I can't figure out how to combine INDIRECT with the VLOOKUP. In my original (real workbook) there are 29 rows between B6 and B35. Same between G5 and G34, and also between D12 and D41. Can someone help me figure out a way to be able to copy the whole timesheet and duplicate it again and again? I need 260 of them. Links to other forums I have posted my query on: https://www.excelforum.com/excel-formulas-and-functions/1392282-how-to-duplicate-a-block-of-cells-that-contain-formulas-without-losing-reference.html#post5747504 https://techcommunity.microsoft.com/t5/excel/how-to-duplicate-a-block-of-cells-without-losing-formula/m-p/3665675#M168868 https://www.msofficeforums.com/excel/49890-how-duplicate-block-cells-contain-formulas-without.html#post171318 https://www.mrexcel.com/board/threads/how-to-duplicate-a-block-of-cells-without-losing-formula-references.1220639/Solved3.7KViews0likes8CommentsRe: How to duplicate a block of cells without losing formula references
HansVogelaar Hi thanks for catching this... I've been staring at my screen for so long I am getting sloppy now. So... For Mary, D45 should be =Sunday!C4 For Susie, D76 should be =Sunday!C5 For Peter, D107 should be =Sunday!C6 I've updated the sample book and made those lead in examples for you. Thank you by the way, for helping me on this!3.4KViews0likes4CommentsRe: How to copy a certain number of cells based on a value?
Thanks to both of you for your responses!! I am so grateful! You've saved me a ton of time!!! Both answers are great! I marked the one that is the easiest for me to use but wanted to point out that both are great! Thanks again!!!!3KViews1like0CommentsHow to copy a certain number of cells based on a value?
Hello! I have 6 columns of timestamps, with numerous rows, if I want to only copy (by random selection) the number of cells in each row, by a given number (in column 7), pasting the data starting in column 8, what IF formula could I use? Here is a photo of what I mean. https://ibb.co/YD1httX Count 8:45 AM 9:51 AM 10:58 AM 3:27 PM 5:25 PM 8:23 PM 2 8:45 AM 9:51 AM 1:42 AM 12:30 PM 8:19 PM 8:30 PM 8:48 PM 8:59 PM 1 8:48 PM 3:57 AM 5:26 AM 6:08 AM 8:31 PM 10:12 PM 11:13 PM 5 5:26 AM 6:08 AM 8:31 PM 10:12 PM 11:13 PM 1:31 AM 4:34 AM 6:15 PM 7:05 PM 8:05 PM 8:28 PM 3 6:15 PM 7:05 PM 8:05 PM 2:33 AM 2:49 AM 9:43 AM 11:20 AM 3:52 PM 9:56 PM 4 2:49 AM 9:43 AM 11:20 AM 3:52 PMSolved3.2KViews0likes3Comments
Recent Blog Articles
No content to show