[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 297
  • Last Modified:

Excel Inrementing

I need to be able to Increment cells in C* and D* based on entries to B*, so that each week it will add to C* and then reset C* at the end of the month and add C* to D*. Is this possible, without adding or subtracting in correctly, and if so what formula or how can it be done? Does my question make sense to anyone?

     A          B           C          D
                 WTD L   MTD L   YTD L
1   Mark                  35        0
2   Elan                   32        0
3   Kory                   95       120
4   Tony                  155      350
5   Marcy                 143      613
                               
TOTAL           0         460      460
0
Lazarus
Asked:
Lazarus
1 Solution
 
Tyler LaczkoCommented:
This would be very complex to do.

You need to have multiple date checks

This could be done using VBA.
0
 
TommySzalapskiCommented:
It's actually very simple to do with formulae. See attached sample.

If you want it to be different for each name, then you just need a couple checks (I'm thinking sumproduct).

Can you give more detail if what I'm posting isn't enough? I just keep the month and year to date totals alongside the regular ones.

I don't see the need to only show on change of week/etc, but that wouldn't be hard.
ytd-mtd-wtd.xls
0
 
LazarusAuthor Commented:
@TommySzalapski, this wont work for what needs to be done. It obviously work they way you have it laid out, but I need to try to show it in the format they have laid out for me. I'll have to see if anyone else has any idea. Thanks though.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
jppintoCommented:
For me, your question is not clear. Can you please explain?

Thanks you...
0
 
LazarusAuthor Commented:
@jppinto, If you look at TommySzalapski's "ytd-mtd-wtd.xls that shows you what I'm trying to do, but i need to do this so that it will show like I have formated in my question. I might need to use several sheets I think to make it work, a Stats sheet and a sheet that does the work behind it, I'm not sure.
0
 
LazarusAuthor Commented:
I've been able to make this work with TommySzalapski's "ytd-mtd-wtd.xls, with one exception. I need to find a way to use this formula: "=INDEX(Workup!B:B, COUNTA(Workup!B:B), 1)" but to find the value of the cell in another column next to it. so the value from B10  will show the value of C10. Hopefully that makes sense.
0
 
TommySzalapskiCommented:
Can you post an xls file that shows what you want it to look like? I'm sure it can be done, I just need more clarity.
0
 
LazarusAuthor Commented:
TommySzalapski, I've attached the file. You will see that that the first sheet "Stats" is where I want the info to show. I have it working from sheet 2 "Workup" . But I'm stuck on trying to get MTD L to work properly so that it shows properly and resets each month as it does in "Workup" sheet 2

ytd-mtd-wtd.xls
0
 
TommySzalapskiCommented:
I don't get where how the stats page is supposed to pull data. Do you just want blanks for every day except the end of the month/year? Like the workup heet on this one?

Again, if you could manually create a small sheet that looks exactly the way you want it to, that would help a lot.
ytd-mtd-wtd.xls
0
 
LazarusAuthor Commented:
TommySzalapski, The real numbers are added in the Workup page the Stats page just shows the number i want. It's already formatted close enough to make it all work with the proper formula. If you look at the Stats page, you can see that it just pulls the info from Workup.
0
 
TommySzalapskiCommented:
Your formula will only ever pull the number from the last week. Do you just want it to pull the last number from each column? What are the L A I E S letters for?

I updated it to use your formula to pull the last item from each column.
ytd-mtd-wtd.xls
0
 
LazarusAuthor Commented:
Thats why I'm looking for help in trying to figure that out. I already know that it will only pull the last in the 2 colums I used it in. I need to figure out how to pull the column to the right of the last entry on "Workup" Column B. So that if the last entry in B is 16, it will look to the Same row Column C Sort of like a lookup table.

0
 
TommySzalapskiCommented:
Oh. Just do this
=INDEX(Workup!C:C, COUNTA(Workup!B:B), 1)
and
=INDEX(Workup!D:D, COUNTA(Workup!B:B), 1)

Index means to go to the column (range technically) in the first argument and the row in the second. So if you use the same second argument for all of them, it will get all the values from the same row.
0
 
LazarusAuthor Commented:
That will not work. When the month resets, it will not grab the correct info from the column, you can see that in your own excel file that you uploaded
0
 
TommySzalapskiCommented:
So that if the last entry in B is 16, it will look to the Same row Column C Sort of like a lookup table.

This is exactly what it does. It is grabbing the same row from C as B. Now, you may need to add a constant since you have blank rows at the top so you may need
=INDEX(Workup!B:B, COUNTA(Workup!B:B)+2, 1)
=INDEX(Workup!C:C, COUNTA(Workup!B:B)+2, 1)
=INDEX(Workup!D:D, COUNTA(Workup!B:B)+2, 1)

But if you had it finding the right row in B then you must have had that already (unless the last few rows were all the same and you just thought you were getting the right one).

If that's not the problem then I think I still don't know what you are trying to do. You're a sage, so you know how hard it is to answer questions when the asker won't give detailed requirements. If I still appear confused, could you manually fill in the data that you want to see and maybe even add comments with the cell address it should be pulling from.

I still don't know why you have several tables with L A I E S. Is each one supposed to pull a different month or something? Please explain so I can help you get this finished.
0
 
LazarusAuthor Commented:
My request is fairly easy, its the formaula that eludes me. I need for the formaula: =INDEX(Workup!B:B, COUNTA(Workup!B:B,1) to give me the cell value to the right of the number it finds.

So that if Workup B1 shows 5, the formula will show the cell value in C1, and if B3 shows 2 it will show th evalue of C3

      A                    B            C          D
1    05/01/2011      5             5          5
2    05/08/2011      5             10        10
3    06/01/2011      2             2          12
0
 
TommySzalapskiCommented:
You just need the +2 then to account for the top two blank rows.
0
 
LazarusAuthor Commented:
I know your trying to understand this, and I am obviously not making it clear here, but that will not work.
 I will try to explain better here if possible:
On Stats B4, the numbers is always changing an must find the last entry in Workup B*
If I use your formula, it will not show the cell value next to that but shows 0. You altered the orginal formulas on the Workup page and they worked fine. All of the info works correctly as you had it in the original Workup 1st attached.
So all I need do is show show the value of Workup column B, C, and D, starting at the last row entered in Workup column B.
Does that make sense at all to you? I dont know any other way to explain it.
0
 
TommySzalapskiCommented:
Okay. If you do
=INDEX(Workup!C:C, COUNTA(Workup!C:C), 1)
then it shows a 0. I agree, If you look at the formulae I posted, you will see that I always used B:B in the COUNTA function. This was intentional and is what makes it work. COUNTA(B:B) gives you the row number that you want for all the columns. The formulae in http:#35731612 should work just fine. I am posting an example of them put in and working that way.

ytd-mtd-wtd.xls
0
 
LazarusAuthor Commented:
Ah jeez, what a DOLT I am. I should have seen that as well. Thank you, for your patience on this one. After looking at it, it does seem awfully simple.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now