# Posts by EnginerdUNH

• ## Calculate days active for cells with varying number of dates

Hi,

I have a spreadsheet where I need to keep track of different tasks that need to be completed where I work. The list has a task name column, who has action, what the action is, date the action when active and a days active column. I need to use VBA to calculate the number of days active because as you will see the in attached test file with dummy data, sometimes the task will have no current action (i.e. listed as N/A), one action (i.e. single current action and current action start date), or multiple actions (i.e. two or more current actions and current action start dates). I have gotten the code below which is also included in the attached file to work for the N/A case since the code simply just checks to see if the date column says "N/A" and if yes, assigns "N/A" to the days active column in that row. When I attempt to calculate the number of days active for rows which have one or more dates, I get a type mismatch error and I'm not sure why.

• ## SUMIF by date and value

Hi Carim,

Thanks for your quick response. When I went to get a copy ready to upload here for a test case, I found some hidden rows that total up the total payments by year so I am able to use the formula

=INDEX(E24:E56,MATCH(E3,B24:B56,0))

in order to find the year value in E24 to E56 which matches the current year (based on today's date) in E3 and then outputs the total payments from the same row in B24 to B56. Now I think I can make this work for what I need.

• ## SUMIF by date and value

Hi,

I am working on a loan payment calculator and I have a cell which currently has the amount per year that needs to be paid extra on the loan in order to pay off the loan sooner. What I need this

[SIZE=13px] cell to represent is the cumulative amount left out of this total based on extra payments already made. In other words, let's say the starting value is \$3750 for extra payments needed and the total monthly payment required is \$500. In one month, an additional \$200 is paid on the loan for a total of \$700. The extra payments needed cell should now reflect \$3550. The trick is that when the next year starts, the amount extra needed to be paid should reset back to \$3750. if it's any help, there is a payment date cell so the year can be extracted from that.[/SIZE]

• ## Display integer and decimal as fraction

Hi,

i am am looking for a way, whether formatting or coding, to display an integer and decimal pair as a fraction. For example, a whole number like 2 would display as that number over 1, i.e. 2/1. A number like 2.5 would display as 5/2 and not 2 1/2. Not sure if there is a simple way to make this work or not.

Thanks,

Morgan

• ## Reference sheet number in formula/VBA code

Ahhh yes!! Thank you so much!!

• ## Reference sheet number in formula/VBA code

We can solve half the problem by removing the self-referencing sheet names from the formulas, and just have the cell address. To make your formulas more robust on looking things up from the refrence sheet, you could use the power of lookup formulas to let XL figure out which column and row it needs to be looking at.

In the attached, I first changes all your dates to be real dates, rather than just strings. Then, I setup a lookup range on your Monthly EV sheet. Last, I changed the main formula in col F to use lookup formulas to figure out which column/row to extract data from. All of the year sheets have the exact same formulas, no need to change anything when you copy the sheet. There's even a formula on the worksheet that pulls in the sheet name, so it knows what year you're trying to find.

Hi Luke,

First off let me thank you for helping me out with this. I am having a few issues with the solution that you provided, however. The solution provided completely disregards in column F any data entered in columns C and D on the year tabs. Also, column B should only be compared to the 4 ER exclusive columns (Columns C through F on my example spreadsheet) on the Monthly PV sheet. Columns C and D are applicable to comparison with the remaining columns (Columns G through I on my example spreadsheet) on the Monthly PV sheet. How does the formula in column F need to be modified to account for this?

• ## Reference sheet number in formula/VBA code

Hopefully my explanation of what I am looking to do is pretty understandable but I will do my best to answer an questions you have. I have a spreadsheet where each tab represents a different year with one tab being a reference information tab for use in formulas/VBA on each year tab. Right now, when I create a new year tab, I have to copy the information from a previous sheet and modify the formula to the reference the correct year sheet and the to call out the correct row on the reference information sheet. What I am looking to do is see if there is a way to modify the formula I am using, or create VBA code, to change dynamically with the creation of new year tabs. Let me try to break down what I envision the formula/VBA being able to do:
1. use VLOOKUP to search the reference tab for the correct row and column corresponding to data that I provide
2. use a formula to determine which rows on the reference tab match the year (sheet name) that I am working with
3. determine the row on the reference tab that corresponds to the month I am working with on that same sheet
4. search that row for the column that matches the criteria I provide
5. sum up all determined values
6. multiply value by .76
[ATTACH]n1197583[/ATTACH]
I have provided a test spreadsheet that I have been working with for your reference of how I am doing it so far which doesn't involve the method I described in steps 1 - 6 as of yet. The column which has the formula in question is column F on sheets 2018, 2019, 2020 and 2021 (for right now). For your reference, what I am doing right now is when I create a new sheet, I copy and paste the previous year's sheet and I have to then change the sheet name in all references in the formula as well as determine the correct corresponding row on the reference numbers sheet (2 - 13 for 2018, 14 - 25 for 2019, 26 - 37 for 2020, 38 - 49 for 2021, etc.) I have done some research already and found that I can potentially use the INDIRECT formula within VLOOKUP to reference the sheet name but i'm not 100% confident that I know how to do it. Please feel free to modify/tinker with the attached spreadsheet and provide any solution you think will work for me or ask questions on something that doesn't make sense.

• ## Count number of non-blank cells in rows and columns

Re: Count number of non-blank cells in rows and columns

Quote from KjBox;795089

Still a bit confusing.

Can you attach your workbook showing the desired results (manually enter those in the cells where you want them to be displayed) and explain the logic behind each result.
[sw]*[/sw]

forum.ozgrid.com/index.php?attachment/72963/

I may have come up with a solution to my problem but I'll run it by you first. Since every person will have data from their first month onward, even if it's zero, can I just used the Count function to count the number of filled cells in a particular row and then subtract it from the number of filled cells in the previous row to get the number of new cells that are filled?

As for the distinction between the totals for returning and new people, I want a way for the spreadsheet to tell me what the total is by adding up just the values corresponding to returning people in a month and a separate column telling me the total amount pertaining to the new people. 9/10 the value for the month corresponding to each individual person will be 100 and therefore one would assume that I can just go ahead and say ok we have 2 new people this month so the sum is 200. However, I need this to be dynamic in the sense that if I have one person whose total is 100 and another who is 150, the spreadsheet isn't just going to assume it's as simple as 100*2 but would actually be 100+150.

I have manually entered in the information as it should be calculated into the example spreadsheet.

Does all of this make sense?

• ## Sum up cells in a row that contain 3 cells or less of data in previous rows

Hi,

Lets say I have the following table:
[TABLE="class: grid, width: 500"]

[tr]

[td]

Month

[/td]

[td]

Person 1

[/td]

[td]

Person 2

[/td]

[td]

Person 3

[/td]

[td]

Person 4

[/td]

[td]

Person 5

[/td]

[td]

Person 6

[/td]

[td]

Person 7

[/td]

[td]

Person 8

[/td]

[td]

Person 9

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

150

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td]

100

[/td]

[/tr]

[/TABLE]
I am trying to create a simple (if possible) formula that will add up the total for every month corresponding to each person if they meet the criteria that there is 3 months or less (think of them as cells) worth of data in those columns, including the current cell. Let me give some examples:

for Month 1, Persons 1 and 2 have data while the rest do not. They also meet the criteria of not having previous monthly data. Therefore, for Month 1, the total I want to sum up comes out to 200.

For Month 2, Persons 1 through 4 now have data but let's check and compare to the previous months. Persons 1 and 2 also have data in the previous month but the number of cells so far in columns 1 and 2 is only 2 so therefore they are still included in the summed total. Persons 3 and 4 fit the category described above for Month 1. So, the total for this month is 350

For Month 3, Persons 1 through 6 now have data but we have to check and compare to the previous months. For Persons 1 and 2, this is their 3rd month of data but that meets the criteria I set above. For Persons 3 and 4, they fit the category described for Month 2 seeing as they only have 2 months worth of data. Persons 5 and 6 meet the category for Month 1 since they only have 1 month worth of data. So the total for this month is 550.

For Month 4, There are now 9 people that have data but lets check and see which ones meet the criteria I stated above. Persons 1 and 2 now have data for 4 months (3 previous months and the current one) so they fail the criteria above and are therefor excluded. Persons 3 and 4 meet the category of Month 3 and are included. Persons 5 and 6 meet the category of Month 2 and are included. Person's 7, 8 and 9 meet the category of Month 1 and are included. So the total for this month is also 550 and not 700.

I understand that the criteria doesn't ever get broken until Month 4 so if necessary, a formula that works for Month 4 onward is fine but I would prefer a formula that would work for every month meaning that it would dynamically change to include more previous months as the number of the current month increases.

• ## Count number of non-blank cells in rows and columns

Re: Count number of non-blank cells in rows and columns

Those are all good questions and looking back I realized I made a few mistakes in my wording. The required count should be 2 every month according to the sample data I provided, however, I do not just want to type in 2 everywhere where I need this data because let's say I change one month and now the required count is 3. I want it to dynamically change. To explain this and simultaneously answer your questions, lets see if this makes sense. For Month 1, you're right about the two non-blank cells being in columns that contain 4 non-blank cells. But, for Month 1, I'm only concerned with Month 1 data (i.e. look across the row, see 2 non blank cells, check those columns and see this is the first non-blank cell in each, answer is required count of 2). For Month 2, I'm only concerned with Month 1 and 2 for data. Look across the row, see 4 non-blank cells, compare to the previous month and see that two of the cells for Month 2 also have data for Month 1, the required count is again 2. Can you see the pattern now? Hopefully this makes sense.

• ## Count number of non-blank cells in rows and columns

Hi,

I am trying to come up with a formula that will count the number of non blank cells in each row and compare it to the number of non-blank cells in each column and decide if it should add them up. Let me give an example of a spreadsheet I am using and then try to explain it further.[TABLE="class: grid, width: 500"]

[tr]

[td]

Month

[/td]

[td]

Person 1

[/td]

[td]

Person 2

[/td]

[td]

Person 3

[/td]

[td]

Person 4

[/td]

[td]

Person 5

[/td]

[td]

Person 6

[/td]

[td]

Person 7

[/td]

[td]

Person 8

[/td]

[td]

Person 9

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

150

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

50

[/td]

[td]

100

[/td]

[td]

100

[/td]

[td]

100

[/td]

[/tr]

[/TABLE]
Let's say I have the table above. I want to add up the number of blank cells in each row but I only want to include the cells in the count if the number on non-blank cells in a column is less than or equal to 3. For example, for the first month, the number of non blank cells to count is 2. For the second month, the number of non blank cells is 4. For the third month, the number of non-blank cells is 6. However, for the fourth month, the number of non-blank cells is seven because the columns for person 1 and 2 have 4 rows worth of data.

In addition, I also want to have a way to use this same criteria to sum up the values in the corresponding cells. For example, for the first month, the total is 100. For the second month, the total would be 350. For the third month, the total would be 550. But for month 4, the total would also be 550 because the first 2 columns are excluded.

• ## Summations using conditional statements or If Then statements

Hi,

I am trying to create a formula that will calculate a value based on a particular condition that I give it. Here is what I basically want the formula to say: "sum up all the values in this array given the following condition: if the value in the box is less than 20, add that value to the total. If the value in the box is greater than 20, add 20 to the total." I would like for there to be a way if possible for me to be able to have the formula work so that if I add more values to the total I want it to give me, it will include those values in the calculation. Let me try and show an example table or two of that I mean.

[TABLE="width: 500"]

[tr]

[td][/td]

[td]

14.75

[/td]

[/tr]

[tr]

[td][/td]

[td]

24

[/td]

[/tr]

[tr]

[td][/td]

[td]

6.5

[/td]

[/tr]

[tr]

[td][/td]

[td]

20

[/td]

[/tr]

[tr]

[td]

Total

[/td]

[td]

61.25

[/td]

[/tr]

[/TABLE]
So as you can see, the formula for the total would add up the 14.75, 6.5 and 20 just as the are and would only add 20 from the 24 value. Now if I were to add a few more rows to the table above my total row, I want the total to change so that it is also including the new values I'm adding. For example,
[TABLE="width: 500"]

[tr]

[td][/td]

[td]

14.75

[/td]

[/tr]

[tr]

[td][/td]

[td]

24

[/td]

[/tr]

[tr]

[td][/td]

[td]

6.5

[/td]

[/tr]

[tr]

[td][/td]

[td]

20

[/td]

[/tr]

[tr]

[td][/td]

[td]

28

[/td]

[/tr]

[tr]

[td][/td]

[td]

15.8

[/td]

[/tr]

[tr]

[td][/td]

[td]

13

[/td]

[/tr]

[tr]

[td]

Total

[/td]

[td]

110.05

[/td]

[/tr]

[/TABLE]
If there is a way that I can create a formula using IF THEN or conditional statements, that would be ideal because I would be able to have the formula add the values and not have to add them myself every time I update the table.

• ## Double Freeze Panes in Excel

Hi,

I'll try and explain this as best I can and hopefully this will make sense to anybody reading it. I use excel to enter a lot of data and it can get very annoying sometimes when I have to create a new workbook because my spreadsheet either becomes too large or I have to change something and I have no way of knowing how to make the spreadsheet look best so I can still continue my work. I am looking to see if there is a way that I can do basically a double freeze panes so that my headers will switch to the header I want when I scroll down to it. Here is an example of what I mean:
[TABLE="width: 500"]

[tr]

[/TD]
[/TD]
[/TD]
[/TD]
[/TD]

[/tr]

[tr]

[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]

[/tr]

[tr]

[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]

[/tr]

[tr]

[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]

[/tr]

[tr]

[/TD]
[/TD]
[/TD]
[/TD]
[/TD]

[/tr]

[tr]

[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]

[/tr]

[tr]

[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]

[/tr]

[tr]

[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]
[TD="align: center"]text
[/TD]

[/tr]

[/TABLE]
So lets say I have the following table above. The first set of headers corresponds to the data that follows up until the next set of headers. I then want to use code or something to be able to tell excel that when I get to the next set of headers, freeze the panes under those headers and I scroll back, freeze the panes under the previous headers. That way I can easily look up data all in one spreadsheet and I don't have to be creating new spreadsheets just because my data requirements change. Hope this helps and I can try to explain it better if this doesn't make any sense.

• ## Use IF Function to return a particular value if a column contains particular value

Re: Use IF Function to return a particular value if a column contains particular valu

Cytop,
i tried your formula but no matter what value I put in A1:A10, A11 always returned 0 as the value of the cell. Let me see if I can explain this a little better so that you might understand what I'm trying to do. Let's say I have a column of 10 values with either Y or N as their value. I want to create a formula that says "take whatever value is in this current cell and add 1 if there is a value of Y in any of the cells else add 0". So if I have 10 values that say Y I want the number to say 10, if 5 Y's then number should say 5. I know this is relatively easy for me to count up myself if I have a relatively small number of Y's and N's but I'm not sure at this point how many I will have for what I need this formula for.

• ## Use IF Function to return a particular value if a column contains particular value

Re: Use IF Function to return a particular value if a column contains particular valu

Hi cytop,

Thanks, I'll have to try that formula out when I'm back at my computer and post how well it works. To answer your question, I want it to add 1 if ANY of the values in the range equal Y not all. If all the values equal Y then I'd want the formula to be able to add 1 for every value that equals Y (in this example, 10).

• ## Use IF Function to return a particular value if a column contains particular value

Hi,

I'm trying to us the IF function such that if I have a table of values in a column, the following in words is an example of what I want the formula to say "if the values in A1:A10 are equal to Y then add one to current cell, else add zero". the current code I have been trying out says If(A1:A10="Y",A11+1,A11+0) but whether I have it written like that or I put equals signs before the A11, it always returns #VALUE error. Not sure what I'm doing wrong.

• ## automatic posting into cells

Re: automatic posting into cells

Thanks mikerickson, that was the code I needed!

• ## automatic posting into cells

Re: automatic posting into cells

Miceal, I just got a chance to try the code you gave me and I got an error message when I went to run it. I typed it in exactly as you gave me.

• ## Changing text color based on information in the cell

Re: Changing text color based on information in the cell

[INDENT=2]I just figured I would give both a try so I have experience with both but knowing that bit of information about the ActiveCell piece, I guess I won't. Thanks royUK. Do either you or Miceal think you could take a look at my other question I posted a few months ago? I haven't seem to have gotten very far with the people who were originally replying to me. The forum can be found at http://www.ozgrid.com/forum/showthread.php?t=181335 [/INDENT]

• ## Changing text color based on information in the cell

Re: Changing text color based on information in the cell

Thanks Miceal, your suggestion worked. I varied your suggestion slightly to use the "Less Than" option and say <70% but it still worked all the same. I just have one question for you about the VBA line you provided me with as I want to try out both available options. When you say if activecell how do I specify in the code which cell I'm referring to that needs to change. Also, does the code you provided only change the font color and not the boldness? I'm new to using VBA in excel and not sure how to do it