I am using the =today() function to places todays date in a cell...how can I get the next to cells to automatically display dates that are 2 months and 4 months later without having to change them manually every day. FWIW, this is for an certification expiration chart.
View 4 RepliesI have a table upon which I wish to perform vlookups, however I need to be able to adjust the range that the vlookup searches to include any data that may be added in the future without having to go into the sheets and change the vlookup.
Example
Name DOB
Tom 01/01/81
Dick 02/02/82
Harry 03/03/83
if someone then adds
Fred 04/04/84
I would like the vlookup to automatically accommodate the extra entry without needing any changes.
I've seen this done on data validation with the following formula:
=OFFSET($a$1,0,0,MATCH(REPT("z",255),$A:$A))
It only needs to do new rows, not columns.
This is for a call centre and sales office. there are 3 tabs. Tab 1 represents the values that we update at the end of each week which then gives a running total over an 8 week period and then an average below. there is a seperate table for each staff member. Tab two is for one team of people and tab two is for the other team. Namely LG team and Sales Team. the tab 2 and 3 are to show league tables that both collect data from tab 1, but then create a league table showing who is top dog over the 8 week period. But this also has to self adjust to be in order.
on tab 2 and 3, the tables on the left will be hidden once complete, but are there to work out the league. the table on the right is then there to put and show the league in the right order. I HOPE THAT MAKES SENSE, COS EVEN I'M LOST NOW. I've followed previous advice on this and inputted what I think are the right formulas, but for some reason it's coming up with the wrong answers and several names are being repeated which are wrong. For example even when ben perhaps is number 2, it will show rahim as number 2 and number 3. why is this?
also, how do I get by the problem of on the odd occassion when perhaps ben and rahim have the same stats and are joint second, but it will only show ben as 2 and ben as 2 again, leaving rahim out of it. WOW. I'm glad I've got that out of my system. I was doing the washing up yesterday donig Excel formulas in my head. But the washing up got done at least.
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
I currently have this formula to deal with this:
=SUM(J5:INDEX(J:J,ROW()-1))
However, whenever I delete the top row...I get a reference error and have to re-adjust...
Moving Colored Text within a automatically adjusting table containing More Than 3 Colors, from Coloum to Coloum. i have managed to put this little chart together to use on our gaming wedsite but i would like to go one step further with it. may table automatically moves the colums up and down the table as i enter the infomation in to it,, "but" I the colours in the text do not stay the same as they move up and down the coloums. how to make the coloured text keep its colour in the table as it alters its position in the table
View 9 Replies View RelatedI have this spreadsheet that I am creating schedules for my employees since our scheduling software at work is horrible to work with. It calculates total hours for each employee at the far right of the rows, and calculates total hours for each day in the columns. The formulas that calculate the hours for each day is set up to automatically deduct 30 minutes from a shift, for lunch, if they're scheduled for more than 5 hours (the gray shaded employee rows are for managers and that only deducts 30 mins if they're shift exceeds 8 hours).
I'm familiar enough to put somewhat complex formulas together, but I don't understand it enough to always make them work correctly. What I'm wanting to be able to do is add or subtract entire rows (add new employees or remove old), and have my formulas for calculating the total hours to still work. Right now if I delete a row the cell with the formula in it says #REF!. I can fix this by going through the formula and manually deleting everything in that contains those other cells, but the formula is huge, and it's pretty tedious going through the whole thing when it feels like there should be a simpler way of doing it.
TLDR: I want to be able to add/remove a row and have my cells currently on row 36 update the formula correctly.
I tried to search the forum for my unique problem but had no luck. As you will see attached, I have a series of excel buttons I need to duplicate and have target a different set of cells. I am hoping there is a way to avoid manually doing this.
The goal of the document is to push the button when both colours in the row and column interact (research on birds). There are two additional behaviours with an exact same set of buttons but they need to target "Body Rush" and "Food Displacement" tables underneath. I need all the buttons on one page since multiple behaviours happen simultaneously that need to be recorded.
I have something like 100+ modules in VBA I have created, I am hoping there is an easier way to do this so I don't have to create another ~200 modules in order to get the last two behaviours setup.
I am hoping there is either an easier button system or way to make buttons adjust somehow.
I am not the most advanced VBA user
I have a sheet with a date and the number of months on it which will change. I need the sheet to list the dates in a column for each month automatically: e.g. Two cells contain date “jan07” and the period “10” months. The rows A1 to A10 should have jan07…jan16 listed automatically. If I change then change the number of month to 11 I would like the rows A1 to A11 to update automatically.
View 6 Replies View RelatedI've asked this similar question on another message board (the Microsoft help community), but failed to follow-up quickly enough for the support I've come here to ask for. My situation involves a spreadsheet that is used to track days worked over the course of a week. I have the days of the week listed in one row (Sun - Sat) and in the row above, a place to put the day (i.e. - 3/28). I asked for help with a macro that would allow the user to place the date into any of the cells above the days of the week, check to ensure that the corresponding day of the week is correct, and if so, populate the other remaining empty dates automatically. Below is the macro that was kindly provided. It works perfectly as long as my data is in the referenced ranges (A1:G1, etc); however, my actual data is in range L6:R6 for the dates and L7:R7 for the day of the week. (example below). I've been able to pick apart the code to understand how to change most of the macro to accept input from the different range; however, I'm unable to comprehend what I'm missing to complete the modification.
View 6 Replies View Relatedsetting the dates so that they update by 30 days once they have passed.
For example I need a cell that has an upcoming date to add 30 days to it once it has reached the upcoming date, so it is April 4th today, and I need it to change a cell that reads 'April 14th' to 'May 14th' once it is April 14th.
how i can automatically add a week to a start date i have in a rotas for the whole year?.
View 3 Replies View RelatedI'm attempting to get a column of due dates to update automatically every time a date changes. The due dates are in B3:B15. There is a header in B3. The day after the actual due date, it is automatically changed to the same day on the next month (showing the next due date). I need the spread sheet to automatically sort the due dates from the closest due at the top, to the furthest due date from today at the bottom. Running it manually, or sorting it manually every time won't work. This is for an elderly couple who are having problems keeping track of everything each month. I just need something that will show them the upcoming bills and how soon they are due when they open the workbook. Anything other than opening the workbook is going to be too complicated. I'm new to macros. I've tried to modify some I've found in other threads, but no luck (lots of error messages). I don't understand it enough yet to figure out what I need to change. I've attached a sample sheet that shows the part of the workbook I'm having trouble with.
View 4 Replies View RelatedI am trying to automatically hide columns based on dates. I have a set of dates ranging from the beginning of August this year until the end of December 2019. I am trying to hide all columns that are at least a day ahead of the current date. Today on 8/4/13 it would show all columns up until today along with tomorrow 8/5/13.
8/1/2013
8/2/2013
8/3/2013
8/4/2013
8/5/2013
Is there a way to find cells that have a date in them that has a text digit with a 2 digit year to 'xx07' rather than having to click on the error and selecting it manually.
it's playing havoc when I'm pulling some data from another workbook so converting en masse would sort the problem out!
Anyone know how to do this?
I have a range (M6:BM6) which consists of week end dates = every Friday (M6 =
5/8/09; BM=5/7/10). I would like to have the range adjusted based on when a
cell (B3) is updated. For example, currently B3=5/5/09 and M6=5/8/09 (week
end date of B3). If B3 was updated to 5/12, I would like to have the range
automatically updated to M6=5/15/09, N6=5/22/09....BM=5/14/10. I could do
this with excel formulas in the sheet but would like to macro code adjust the
values in the range rather than formulas.
These ranges are used in other macros and works better with values rather than formulas.
I have a marketing calendar that I need to update just about daily with new promotional plans because the dates change very often and there are a lot of markets that we are keeping track of. Because the format of the excel is basically a bunch of merged cells to show the range of dates of a particular promotion, it takes a very long time to unmerge and remerge everything when one date changes.
I have an example of what the graph looks like below, as well as a grid of what I would like to use to generate this graph automatically using the start and end dates so thats all you need to change. If you changed the start dates, the market name, or the promo name, the chart would reflect the updates.
I have a spread sheet that is used to show the dates for the days in the week that my students are in class. I want it to be able to take the date from a cell that has the class start date and then show what the date will be for day 1, day 2, day 3, etc... I am not a beginner with excel but i can't figure out how to get it to skip calendar weekends.
View 9 Replies View RelatedI have multiple tabs for each month (Jan, Feb, etc) for the 2008 year and I have a Summary tab.
For the individual months, I have each calendar day in a column and whether the day was for sick, vacation, etc in the rows. So if someone was sick in April, I would mark the day they were out of the office. And so on...
Well now I need to figure out how I can take those individual dates and total them up in the summary page automatically.
I am making a holiday form with names down the page and dates along the top, what I am trying to do is create a user form so it will automatically fill the dates to which they are on holiday. What I would like the code to say to find the cell at a specific date (column) and person (row) so it will enter a custom text or number.
The data that is submitted on the user form would be a start date and end date and the person’s name.
I have been perusing various Excel sites in hopes of finding a macro or function that can create columns based upon two dates input into specific cells. My ultimate goal is to use this macro or automated function so that when the user inputs the dates, the columns would automatically be created for them, rather than having them do it manually.
ideally, the 'start' and 'finish' dates are named cells and the macro could determine the difference between the two and automatically insert columns to the left of a ' Totals' column.
If you look at the file I attached you will see the tabs at the bottom are months of the year. I know how copy a sheet but every time I make a new month tab I have to retype the month in the top left corner of the table, in the tab, and other places. This gets really repetitive when I have to do this for literally around 20 other spreadsheets like this.
What I'd like is a way to create new month tabs that automatically rename the month and year in chronological order on the sheet and tab if possible. 2 months it's because I'm constantly having to tweak spreadsheets for my boss. It'd be pointless to go ahead and make a template because then I'd have to go back and change the code for every month. So I just create tabs and edit code month by month.
I have columns of dates and I'm looking to automatically highlight dates that are older than 5 business days from today. Is that conditional formatting?
View 3 Replies View Related1- I dont know why but sometimes excel 2010 converts numbers to dates automatically in my workbook. I use this macro to solve the problem, but since last week it is not working. I get an error: "Method 'NumberFormat' of object 'Range' failed".
2- because it should work in all cells of the workbook, the macro must make changes only in the cells with absolute numbers (ex: 1223) but must not change if in the cell there is a date like 12-may-2012 (numbers with hyphens).
This is my function:
_____________________________
Sub PuxaDatas()
Dim sht As Worksheet
For Each sht In ActiveWorkbook.Sheets
[Code].....
I need to use the VLOOKUP feature from the most latest files, I am using VLOOKUP in VBA code which is working fine, however the problem is that the files from where I do VLOOKUP are added randomly in a week.
Ex:
There are 2 Files, 1st File where the data would be looked up from the 2nd File, Now the 2nd File has names like this "Offline Data as on 10th May 14", "Offline Data as on 13th May 14", "Offline Data as on 23rd May 14", etc.
Now these files are send by another department, and each time the most recent data has to be searched from these files.. All of these files have to be maintained separately for record purposes.
Hence in the VLOOKUP, Lookup file keeps on changing...so is it possible to make a code in such a way that the code picks up the most recent file for reference, from the above example we would need to pull the data from the most latest file which is "Offline Data as on 23rd May 14" , if any other file is added in that folder more recent than the one mentioned than the code should automatically link it to that file.
I have managed to codify the lookup but due to this filenames being changed always I have to do this manually..as this has to be done at several columns in several sheets its extremely time-consuming.
So, logic code to pick the most recent date which is embedded in the filename...
I'm working on a workbook to track staffing patterns. I have two userforms included in my workbook. The first userform (userForm2) initiates upon opening the workbook. It's intended to allow the user to enter a date range and an office location for the report. The second userform (userform1) initiates when a command button (Weekly Summary) is clicked. I've linked texts boxes in userForm2 to cells in a hidden worksheet; this is where I'm holding the dates and office location until userform1 is initiated. I also have labels in userform1 linked to the same cells in the hidden worksheet so that when userform1 is initiated the office and date range appear at the top of userform1.
Here's the problem, when I click command button "Weekly Summary" the office location shows up perfectly however, the labels I have linked to cells in the hidden worksheet that contain dates do not update (i.e. they show the dates that were previously in those specific cells. I have to close userform1 and re-open it to get the dates to update.
I need the user to be able to choose a date range and office location when they enter the workbook. Then, I need userform1 to show the date range and office location (without having to open it, close it and re-open it) that the user chose on opening the workbook.
I am trying to set up a spreadsheet which automatically calculates the start and end dates of project tasks, by looking at the order in which tasks need to be completed. I have attached a spreadsheet to show what I am trying to do.
View 3 Replies View RelatedI have class monday, tuesday, wednesday & friday but no class thursday. How can I make automatically fill in dates for weekdays only and skip thursday using excel 2013's "Fill" function then "series" ??
View 6 Replies View RelatedI've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.
FTE Calculator non nursing.xlsx
I've attached the file. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.
I am trying to figure out a formula for the boss, but I just cannot wrap my mind around what I need to do. I’m sending out an S.O.S.! If you can figure this out, please respond.
=MIN(b4*12-25%,b4*12-500) + MIN(c4*12-25%,c4*12-500) fills in the cell with the correct answer, but not quite the answer that I need. Let me illustrate:
The first part of the formula is to take b4 times 12 minus 25% --if this amount is less than or equal to 500 then use the amount. If it is more than 500 then use 500.
(b4*12-25%)
234.25 * 12 = 2811 – 25% = 2811
2811 - 25% = 702.75 500 Since this amount is more than 500, 500 should be used instead. ~~this is actually the amount that I want to show in the cell, the amount saved~~
2811 - 500 = 2311 ~~this is the answer that is showing up in the cell, which is not quite what I need. ~~
I want to find the answer to this question: b4 times 12 equals ?, subtract 25% or 500 from that, insert this answer into cell.
One of my biggest problems with this is that I can’t even figure out how to say what it is that I need. The formula is doing exactly what I'm telling it to do and I can't even figure out what it is that isn't working. This formula is confusing my brain…!