- My Forums
- Tiger Rant
- LSU Recruiting
- SEC Rant
- Saints Talk
- Pelicans Talk
- More Sports Board
- Fantasy Sports
- Golf Board
- Soccer Board
- O-T Lounge
- Tech Board
- Home/Garden Board
- Outdoor Board
- Health/Fitness Board
- Movie/TV Board
- Book Board
- Music Board
- Political Talk
- Money Talk
- Fark Board
- Gaming Board
- Travel Board
- Food/Drink Board
- Ticket Exchange
- TD Help Board
Customize My Forums- View All Forums
- Show Left Links
- Topic Sort Options
- Trending Topics
- Recent Topics
- Active Topics
Started By
Message
Excel Quesitons...
Posted on 4/9/15 at 7:30 pm
Posted on 4/9/15 at 7:30 pm
Ive got a few tasks im struggling with. I'll start with the simplest parts (i think).
1)Cells B5 and B6 are drop down menus that change. How do i get the dates in row 3 to be read as full months as opposed to just Jan 1, 2015(without changing the way they appear)? Im trying to put a formula in each month that will(to start) return the amount of days from cell B7 that occur in that month. So in the example below, 26 for May, 31 for June, and 16 for July.
1)Cells B5 and B6 are drop down menus that change. How do i get the dates in row 3 to be read as full months as opposed to just Jan 1, 2015(without changing the way they appear)? Im trying to put a formula in each month that will(to start) return the amount of days from cell B7 that occur in that month. So in the example below, 26 for May, 31 for June, and 16 for July.
2
Posted on 4/9/15 at 7:44 pm to Hester Carries
There's a function in excel that will return the first day of the month of a given date (I belive). You can use that function and subtract the date (using serial numbers), giving you the number of days into the month.
ETA: Can also construct it from your date and Month().
I doubt this is really what you're trying to do. There may be easier ways to do this than trying to piece it together.
ETA: Can also construct it from your date and Month().
I doubt this is really what you're trying to do. There may be easier ways to do this than trying to piece it together.
This post was edited on 4/9/15 at 7:47 pm
Posted on 4/9/15 at 9:22 pm to Hester Carries
The custom format "mmmm" will show just the month. For what follows, it's important that each month use 1 for the day of month.
Then define the names StartDate = $B$5 and EndDate = $B$6.
Then in D7 and to the right:
=MAX(MIN(EndDate, DATE(YEAR(D3), MONTH(D3)+1, 1)) - MAX(StartDate, D3), 0)
Note that you have an off-by-one error in the number of days you wrote for July. Your days add up to 73, but your Contract Length cell contains 72. If you want to include the day the contract ends, add 1 to cell B7, and in the formula above, add 1 to EndDate. I think that's probably what you want to do, so the formula would be:
=MAX(MIN(EndDate + 1, DATE(YEAR(D3), MONTH(D3)+1, 1)) - MAX(StartDate, D3), 0)
The idea is to find the number of days in each month that intersect the contract date range; results <= 0 mean no intersection.
Then define the names StartDate = $B$5 and EndDate = $B$6.
Then in D7 and to the right:
=MAX(MIN(EndDate, DATE(YEAR(D3), MONTH(D3)+1, 1)) - MAX(StartDate, D3), 0)
Note that you have an off-by-one error in the number of days you wrote for July. Your days add up to 73, but your Contract Length cell contains 72. If you want to include the day the contract ends, add 1 to cell B7, and in the formula above, add 1 to EndDate. I think that's probably what you want to do, so the formula would be:
=MAX(MIN(EndDate + 1, DATE(YEAR(D3), MONTH(D3)+1, 1)) - MAX(StartDate, D3), 0)
The idea is to find the number of days in each month that intersect the contract date range; results <= 0 mean no intersection.
Posted on 4/9/15 at 9:50 pm to Spock's Eyebrow
quote:
Spock's Eyebrow
YOURE A GENIUS!!! Thanks man
Tomorrow Im going to try and build on that where I add a VLOOKUP that grabs different client names and references different dates based on the contract. So I may update this thread if I hit snags. Excel can be such a beautiful thing.
This post was edited on 4/9/15 at 9:54 pm
Posted on 4/10/15 at 2:39 pm to Hester Carries
FOLLOW UP QUESTION.
This one is probably going to be much harder.
Currently, using the logic from above as a backbone(with slight modificaitons here and there) ive built a dashboard(pic 1) that populates the data in pic 2 (note that when rows 4-14 are hidden the revenue total line in row 2 reads correctly).
The objective of my question is this:
How do i set it up to where as i create new sections in the dash board, it will populate new sections in pic 2 that picks the cells to reference based on matching customer name?
This one is probably going to be much harder.
Currently, using the logic from above as a backbone(with slight modificaitons here and there) ive built a dashboard(pic 1) that populates the data in pic 2 (note that when rows 4-14 are hidden the revenue total line in row 2 reads correctly).
The objective of my question is this:
How do i set it up to where as i create new sections in the dash board, it will populate new sections in pic 2 that picks the cells to reference based on matching customer name?
Posted on 4/10/15 at 4:35 pm to Hester Carries
Just a shot in the dark here, but maybe add a column in pic 2 (cell A4 in my formula) that indicates the row that the customer name is on (3, 13, 23, etc) then use an INDIRECT formula to reference the pic 1 tab. I may not have it exact, but play around with it some.
=INDIRECT("'pic1'!B" & A4) Returns Customer Name
=INDIRECT("'pic1'!B" & A4+1) Returns Country Code
=INDIRECT("'pic1'!B" & A4) Returns Customer Name
=INDIRECT("'pic1'!B" & A4+1) Returns Country Code
Posted on 4/10/15 at 6:51 pm to Hester Carries
Userform and Macros brah. Restrict and control that input.
What kind of new sections? A simple vlookup should be able to locate the data you need if you're looking for, say, "Revenue 7", in that database. Can also use Match/Index if vlookup causes problems with the left column restriction.
If you're trying to record the data from the database and have it populated in dashboard... A macro/Userform will solve this.
ETA: I'm a bit unclear what you're adding and how you want it added. Doing it with formulas is likely possible, but not very dynamic and limited by certain restrictions in formatting. The database and dashboard will have to be formatted and sorted a particular way everytime for it to function.
quote:
...As I create new sections in the dashboard, it will populate new sections in [pic 2 / database] that picks cells to reference based on matching customer name
What kind of new sections? A simple vlookup should be able to locate the data you need if you're looking for, say, "Revenue 7", in that database. Can also use Match/Index if vlookup causes problems with the left column restriction.
If you're trying to record the data from the database and have it populated in dashboard... A macro/Userform will solve this.
ETA: I'm a bit unclear what you're adding and how you want it added. Doing it with formulas is likely possible, but not very dynamic and limited by certain restrictions in formatting. The database and dashboard will have to be formatted and sorted a particular way everytime for it to function.
This post was edited on 4/10/15 at 6:56 pm
Posted on 4/12/15 at 10:25 pm to LSUtigerME
Update:
My current solution is to name the range in pic 1 and then replace all the cell references in pic 2 with index formulas. I still dont know how im going to make that automatic though.
My current solution is to name the range in pic 1 and then replace all the cell references in pic 2 with index formulas. I still dont know how im going to make that automatic though.
Page 1 of 1
Back to top
Follow TigerDroppings for LSU Football News