Find The Last Value In A Column 


Find The Last Value In A Column  Excel 
View Answers 
Hi,
Column B:B is now 950 lines deep (and getting deeper), and it always the last entry, that is important.
How can rewrite this formula
=(B5the last entry, in Column B)
I want to display the result, at the top of the sheet; in say, A1
Cheers,
Mick2.
Column B:B is now 950 lines deep (and getting deeper), and it always the last entry, that is important.
How can rewrite this formula
=(B5the last entry, in Column B)
I want to display the result, at the top of the sheet; in say, A1
Cheers,
Mick2.
Similar Excel Video Tutorials
Find Last Row & Column Dynamic Range
 Create a dynamic range when there are blanks in the data set. Use an array formula to find the Last row or column used in a data set. See the function ...
AGGREGATE function Contains 19 different functions
 Learn about the new Excel 2010 AGGREGATE function that can calculate these 19 different functions depending on what number you put into the 1st argume ...
Amazing Find & Go To Trick!
 See how to use the Find command to go to all cells with a certain formatting, or even a certain function! Then it is easy to make universal changes af ...
Find & Replace Format or Formula
 Find all the occurrences of a word, number, format or formula and then change or replace all of them! See how to use the Find and Replace feature in E ...
Helpful Excel Macros
Vlookup Macro to Return All Matching Results from a Sheet in Excel
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
 This Excel Macro works like a better Vlookup function because it returns ALL of the matching results. Run the
Vlookup Macro to Return All Matching Results and Stack them with Previous Results
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
 This is very similar to the other Vlookup type Macro in that it returns all of the results that match a particular se
Reverse Row or Column Order in a Worksheet
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
 This macro reverses the order of a selected row or column in excel. It will completely reverse the data.
Highlight the Column of the Selected Cell
 This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
 This macro will highlight the column of the active cell. This will fill the column of the active cell with a solid back
Excel Macro that Searches Entire Workbook and Returns All Matches
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
 This is the ultimate Lookup Macro for Excel. It will search every worksheet in the workbook and return all of the mat
Similar Topics
Hi
I am trying to find the last entry in a range, and have two questions
The range is a column of say twelve cells, January to December and each month an entry will be inserted into the relevant cell. I want to find that entry. I can do this with a lookup formula, the problem is the cells are references to another range, (they are actually in another sheet, but thats not important) and only works on the last cell, December. How can I resolve this
I have attached a simple example which should explain.
On a similar note, Lookup only finds the last entry, the one in furthest down the range. If you made an entry in cell 2 and 3 and then changed cell 2 the result to the formula would stil be cell 3. How could I identify the most recent entry rather than the last entry?
I am trying to find the last entry in a range, and have two questions
The range is a column of say twelve cells, January to December and each month an entry will be inserted into the relevant cell. I want to find that entry. I can do this with a lookup formula, the problem is the cells are references to another range, (they are actually in another sheet, but thats not important) and only works on the last cell, December. How can I resolve this
I have attached a simple example which should explain.
On a similar note, Lookup only finds the last entry, the one in furthest down the range. If you made an entry in cell 2 and 3 and then changed cell 2 the result to the formula would stil be cell 3. How could I identify the most recent entry rather than the last entry?
I am using Office Excel 2007 with XP Pro. I am self taught on computers and office excel formulas but have become stumped with this one.
I require a formula to do an average based on entries not lines in a column.
Will try to explain as best I can as I cannot get the html maker to work so cannot post a example here. If someone wishes me to email them an example please let me know.
Column A 1:99 has various entries through out the column. Active cell will be B90, I need B90 to look up from A90 until it finds 30 entries and then give me the average of those 30 entries. B91 will need to do the same however if A91 does not have an entry then the average result should still be the same as in B90. If we then go to B92 and A92 has an entry then B92 will be use the A92 entry as entry 1 and look up column A until it finds another 29 entries to do the 30 entry average calculation for B92. I hope that make sense.
I cannot use the =avg(A90:A60) formula as there may not be an entry on every line and I need 30 entry average not 30 line average.
I also need a 60 entry and 90 entry average formula as well for another column, but will try to figure those ones out after I get the 30 entry average first.
Again if I havn't explained it or have some confused please ask and I'll try to send you an example.
I require a formula to do an average based on entries not lines in a column.
Will try to explain as best I can as I cannot get the html maker to work so cannot post a example here. If someone wishes me to email them an example please let me know.
Column A 1:99 has various entries through out the column. Active cell will be B90, I need B90 to look up from A90 until it finds 30 entries and then give me the average of those 30 entries. B91 will need to do the same however if A91 does not have an entry then the average result should still be the same as in B90. If we then go to B92 and A92 has an entry then B92 will be use the A92 entry as entry 1 and look up column A until it finds another 29 entries to do the 30 entry average calculation for B92. I hope that make sense.
I cannot use the =avg(A90:A60) formula as there may not be an entry on every line and I need 30 entry average not 30 line average.
I also need a 60 entry and 90 entry average formula as well for another column, but will try to figure those ones out after I get the 30 entry average first.
Again if I havn't explained it or have some confused please ask and I'll try to send you an example.
G'day All,
I reckon this should be easier than I'm finding it, but I just can't seem to find the right combination of functions to solve this.
In column A I will have an entry every 5 or 6 or 7 rows with blanks rows between.
In Column B starting at every row corresponding with an entry in Column A there will be a short column of figures could be 1 2 3 or four or more of them. (the're will always be a blank between the last number in a group in B before the next entry in A)
In column C starting at every Row corresponding to an entry in column A I want the sum of the group of figures in Column B.
But here's the rub.... The most important thing is that I can use the same formula all the way down column C. So click the one formula at the top and drag it all the way down 1000 rows.
I know this is easy for those with a bigger brain than mine.... but I'm stumped!
I reckon this should be easier than I'm finding it, but I just can't seem to find the right combination of functions to solve this.
In column A I will have an entry every 5 or 6 or 7 rows with blanks rows between.
In Column B starting at every row corresponding with an entry in Column A there will be a short column of figures could be 1 2 3 or four or more of them. (the're will always be a blank between the last number in a group in B before the next entry in A)
In column C starting at every Row corresponding to an entry in column A I want the sum of the group of figures in Column B.
But here's the rub.... The most important thing is that I can use the same formula all the way down column C. So click the one formula at the top and drag it all the way down 1000 rows.
I know this is easy for those with a bigger brain than mine.... but I'm stumped!
Hello,
I would like to display the result of the last entered data of column B in cell. C3
Typical data is numeric and it may contain letters anywhere in it.
example: 90444K or K23567 or 23K56 or 332298
The data is in column B and starts from B5 and down.
For formula I used: =LOOKUP(8000,B:B) and this works great for any random number entry without text in it.
How do I display the last entry in a column?
Regards,
Harry
I would like to display the result of the last entered data of column B in cell. C3
Typical data is numeric and it may contain letters anywhere in it.
example: 90444K or K23567 or 23K56 or 332298
The data is in column B and starts from B5 and down.
For formula I used: =LOOKUP(8000,B:B) and this works great for any random number entry without text in it.
How do I display the last entry in a column?
Regards,
Harry
Hi all,
I'd like a formula in column E, when it is on the same row as the word "Entry" in column A, to sum all values beneath it, till another "entry" occures in column A. When it is not on the same line as "entry" it should sum C and D.
Sheet1 B C D E 3 Entry 4,58 4 0,89 0,39 1,28 5 0,33 0,07 0,41 6 0,77 0,54 1,31 7 0,83 0,75 1,58 8 Entry 6,61 9 0,29 0,86 1,14 10 0,23 0,44 0,67 11 0,54 0,28 0,82 12 0,08 0,89 0,97 13 0,16 0,68 0,84 14 0,21 0,10 0,31 15 0,50 0,20 0,70 16 0,46 0,04 0,50 17 0,11 0,55 0,66 18 Entry 8,44 19 0,25 0,05 0,30 20 0,08 0,34 0,43 21 0,08 0,99 1,07 22 0,14 0,48 0,62 23 0,86 0,37 1,23 24 0,04 0,12 0,16 25 0,97 0,93 1,90 26 0,23 0,56 0,79 27 0,15 0,69 0,83 28 0,79 0,32 1,11 29 Entry
I'd like a formula in column E, when it is on the same row as the word "Entry" in column A, to sum all values beneath it, till another "entry" occures in column A. When it is not on the same line as "entry" it should sum C and D.
Sheet1 B C D E 3 Entry 4,58 4 0,89 0,39 1,28 5 0,33 0,07 0,41 6 0,77 0,54 1,31 7 0,83 0,75 1,58 8 Entry 6,61 9 0,29 0,86 1,14 10 0,23 0,44 0,67 11 0,54 0,28 0,82 12 0,08 0,89 0,97 13 0,16 0,68 0,84 14 0,21 0,10 0,31 15 0,50 0,20 0,70 16 0,46 0,04 0,50 17 0,11 0,55 0,66 18 Entry 8,44 19 0,25 0,05 0,30 20 0,08 0,34 0,43 21 0,08 0,99 1,07 22 0,14 0,48 0,62 23 0,86 0,37 1,23 24 0,04 0,12 0,16 25 0,97 0,93 1,90 26 0,23 0,56 0,79 27 0,15 0,69 0,83 28 0,79 0,32 1,11 29 Entry
My inventory spread sheet has several columns that have entries on some lines daily. The column lengths vary but the last number in a particular column is used for inventory purposes. I would like to have a line below the last entry of the longest column (on one line only) for ease of searching rather having to go up and down the column lines. One column may have 10 entry lines and another may have 100 entry lines. Lets say I want line 101 to have the last number of column x which may be on line 10 and column y's number is on line 100.
Thanks anyone, Merritt
Thanks anyone, Merritt
Currently I'm using the following code to find the bottommost entry in column B .....
Code:
However, I have a large number of these and my program is running enormously slowly, so I'm wondering if the numerous searches from the bottom cell of each column upwards to find the bottommost entry is too much.
Is there a code that could (let's say for column B) find the cell halfway between the bottom of the column and the top, see if there's an entry. If there is NOT, then find the cell halfway between that position and the top and see if that cell has an entry. If the first search DID find an entry, then it would find the cell halfway between that position and the bottom of the column and see if that cell had an entry.
These searches would continue on and on, either upwards or downwards, etc etc, until the bottommost entry was found.
In theory, this would greatly reduce the number of cells searched for an entry compared with the code I provided above.
In the example below, the algorithm would search the cell halfway between (let's say) B20 and B1 ... it would find that B10 is empty, so it would then look in cell B5 and find that it has an entry, so it would look down in B8 and find an entry so it would continue to look down and find that B9 is empty, hence, B9 would be the bottommost entry ....
Sheet1
A B C
1 3
2 4
3 2
4 1
5 5
6 7
7 5
8 8
9
10
11
12
13
14
15
16
17
18
19
20
Excel tables to the web >> Excel Jeanie HTML 4
Done over an entire column with more than a million rows, it would save searching every cell in column B (from bottom upwards) and only search perhaps a handful of cells .. in the case above, it would look in cell B500000 (no entry), it would then search B250000 (no entry), then B125000 (no entry) then B62500 (no entry), then B31250 (no entry) then B15625 (no entry) then B7812 (no entry) then B3906 (no entry) then B1953 (no entry) then B976 (no entry) then B488 (no entry) then B244 (no entry) then B122 (no entry) then B61 (no entry) then B30 (no entry) then B15 (no entry) then B7 (entry) so it now looks downward to the cell halfway between B7 and the last no entry which was B15, so it would look in B11 (no entry) so it would now look up again ... B9 ( no entry) then B8 (entry) .. so B8 would be the bottommost entry.
This was 20 searches as opposed to more than a million searches.
That surely has to speed my program up?
Is anyone able to code this algorithm ?
Code:
Private Sub Aminus_Click() Range("B65536").End(xlUp).Offset(1, 0) = "A" End Sub
However, I have a large number of these and my program is running enormously slowly, so I'm wondering if the numerous searches from the bottom cell of each column upwards to find the bottommost entry is too much.
Is there a code that could (let's say for column B) find the cell halfway between the bottom of the column and the top, see if there's an entry. If there is NOT, then find the cell halfway between that position and the top and see if that cell has an entry. If the first search DID find an entry, then it would find the cell halfway between that position and the bottom of the column and see if that cell had an entry.
These searches would continue on and on, either upwards or downwards, etc etc, until the bottommost entry was found.
In theory, this would greatly reduce the number of cells searched for an entry compared with the code I provided above.
In the example below, the algorithm would search the cell halfway between (let's say) B20 and B1 ... it would find that B10 is empty, so it would then look in cell B5 and find that it has an entry, so it would look down in B8 and find an entry so it would continue to look down and find that B9 is empty, hence, B9 would be the bottommost entry ....
Sheet1
A B C
1 3
2 4
3 2
4 1
5 5
6 7
7 5
8 8
9
10
11
12
13
14
15
16
17
18
19
20
Excel tables to the web >> Excel Jeanie HTML 4
Done over an entire column with more than a million rows, it would save searching every cell in column B (from bottom upwards) and only search perhaps a handful of cells .. in the case above, it would look in cell B500000 (no entry), it would then search B250000 (no entry), then B125000 (no entry) then B62500 (no entry), then B31250 (no entry) then B15625 (no entry) then B7812 (no entry) then B3906 (no entry) then B1953 (no entry) then B976 (no entry) then B488 (no entry) then B244 (no entry) then B122 (no entry) then B61 (no entry) then B30 (no entry) then B15 (no entry) then B7 (entry) so it now looks downward to the cell halfway between B7 and the last no entry which was B15, so it would look in B11 (no entry) so it would now look up again ... B9 ( no entry) then B8 (entry) .. so B8 would be the bottommost entry.
This was 20 searches as opposed to more than a million searches.
That surely has to speed my program up?
Is anyone able to code this algorithm ?
G'day
How do i create a formula for just one cell based on the last entry in a
particula row or column? The last entry will obviously change every time i
enter a new value in that particaul row or column.
eg.
Date Value Formula
1/1 15 = 100 MINUS " last entry in value column" (ie.
equals 55 on 41, but will change to 66 on 5/1)
2/1 39
3/1 27
4/1 45
5/1 34
6/1
7/1
Cheers
B1.
How do i create a formula for just one cell based on the last entry in a
particula row or column? The last entry will obviously change every time i
enter a new value in that particaul row or column.
eg.
Date Value Formula
1/1 15 = 100 MINUS " last entry in value column" (ie.
equals 55 on 41, but will change to 66 on 5/1)
2/1 39
3/1 27
4/1 45
5/1 34
6/1
7/1
Cheers
B1.
Hi!
I have a list in this format:
entry 1
entry 2
entry 3
entry 4
entry 5
entry 6
entry 7
entry 8
entry 9
entry 10
entry 11
entry 12
entry 13
entry 14
entry 15
and want to convert it into:
entry 1 entry 6 entry 11
entry 2 entry 7 entry 12
entry 3 entry 8 entry 13
entry 4 entry 9 entry 14
entry 5 entry10 entry 15
Is this possible without making a makro?
Thanks for your help
 Marco
I have a list in this format:
entry 1
entry 2
entry 3
entry 4
entry 5
entry 6
entry 7
entry 8
entry 9
entry 10
entry 11
entry 12
entry 13
entry 14
entry 15
and want to convert it into:
entry 1 entry 6 entry 11
entry 2 entry 7 entry 12
entry 3 entry 8 entry 13
entry 4 entry 9 entry 14
entry 5 entry10 entry 15
Is this possible without making a makro?
Thanks for your help
 Marco
I would like to sum the numbers in a column and place the result below the last entry in that column. The number of entries in the column varies, so the result needs to always follow the last entry.
I wrote code that sums the Entire column, places the formula/result in an adjacent column (to avoid a circular formula), then cuts and pastes that result into the first blank cell in the data column. It works, but obviously there is a more efficient solution. Any help would be appreciated.
I wrote code that sums the Entire column, places the formula/result in an adjacent column (to avoid a circular formula), then cuts and pastes that result into the first blank cell in the data column. It works, but obviously there is a more efficient solution. Any help would be appreciated.
I have a sheet that has several rows... each row has at least 1 entry in one of the columns BF. I want it all to be lined up in Column A. There's never more than 1 entry in any given column, so say Row 2, there will only be one entry in one of BF.
What formula could I use in Column A to tell excel look across BF, when you find something, show it in Column A. I tried some IF statement but wasn't having much luck... any help appreciated!
Chris
What formula could I use in Column A to tell excel look across BF, when you find something, show it in Column A. I tried some IF statement but wasn't having much luck... any help appreciated!
Chris
Hi, I'm new to Excel macros and have what I think is a pretty simple task.
I have a lot of data and I need to have a name and then a number that is a certain number of lines down copied and then pasted onto another sheet so that the name is in one column and the number is in the next column.
The line above every name says "Portfolio Summary" and the line next to every number says "Cash and Equivalents" so these could be used as cues I'm sure. Alternatively, the same lines of data for each entry are 41 lines apart. In other words, the name of the first entry is always 41 lines from the name of the second entry, etc. This is also the case with the numbers.
I understand how to do this for one entry, or however i want to actually type the cell names in for, but how could I set it to repeat this action every 41 lines? Or by the cue?
Any help is greatly appreciated.
Thanks,
Drew
I have a lot of data and I need to have a name and then a number that is a certain number of lines down copied and then pasted onto another sheet so that the name is in one column and the number is in the next column.
The line above every name says "Portfolio Summary" and the line next to every number says "Cash and Equivalents" so these could be used as cues I'm sure. Alternatively, the same lines of data for each entry are 41 lines apart. In other words, the name of the first entry is always 41 lines from the name of the second entry, etc. This is also the case with the numbers.
I understand how to do this for one entry, or however i want to actually type the cell names in for, but how could I set it to repeat this action every 41 lines? Or by the cue?
Any help is greatly appreciated.
Thanks,
Drew
I'm creating a worksheet using excel 2007. In column A I've calendar weeks from 1 to 52. In column E I'm using data validation to select an entry from the list. Now in order to do some calculation in column N, I've written an array formula. I'm trying to implement the following logic using this formula " =IF(AND($A$2:$A2=A3,MATCH(E3,E:E,0)=ROW()),(100((SUM($M$2:$M3)*100))/100),INDEX(N$2:N2,SMALL(IF(E$2:E3=E3,ROW(E$2:E3)),COUNTIF(E$2:E3,E3)1)1,1)M3)".
For N3:
IF(A3=A2) && the occurrence of an entry in E3 is for the first time THEN (N3=((100((M2+M3)*100))/100))
ELSE N3=Nn  M3.
Here, Nn denotes the last occurrence of the an entry displayed E3.
For N4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in E4 is for the first time THEN (N4=((100((M2+M3+M4)*100))/100))
ELSE N4=Nn  M4.
Similarly,
For N5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in E5 is for the first time THEN (N5= ((100(M2+M3+M4+M5)*100))/100))
ELSE N5=Nn  M5.
and so on...
But there seems to be a problem. In E2 I've selected an entry, the result is fine and it shows the correct result 75%. But in E3, If I select a different entry other than the one selected in E2, then the result in N3 is diplayed as 50%.
This is incorrect as according to the calculation it should show 75%. The result 50% in N3 is correct only if E2=E3.
I've attached the excel for reference. Could you please check and tell me what's wrong with my formula?
For N3:
IF(A3=A2) && the occurrence of an entry in E3 is for the first time THEN (N3=((100((M2+M3)*100))/100))
ELSE N3=Nn  M3.
Here, Nn denotes the last occurrence of the an entry displayed E3.
For N4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in E4 is for the first time THEN (N4=((100((M2+M3+M4)*100))/100))
ELSE N4=Nn  M4.
Similarly,
For N5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in E5 is for the first time THEN (N5= ((100(M2+M3+M4+M5)*100))/100))
ELSE N5=Nn  M5.
and so on...
But there seems to be a problem. In E2 I've selected an entry, the result is fine and it shows the correct result 75%. But in E3, If I select a different entry other than the one selected in E2, then the result in N3 is diplayed as 50%.
This is incorrect as according to the calculation it should show 75%. The result 50% in N3 is correct only if E2=E3.
I've attached the excel for reference. Could you please check and tell me what's wrong with my formula?
Hi All,
I am facing a problem in excel. Suppose I have entries in Column A and I have a few keywords in Column D. I need to check if the entry in column A contains one of the kepwords in Column D. The formula for the check will be written in Column B and if it contains the keyword, I need to display it in column B.
For Eg.
Cell A1 has an entry "ABC Holdings Limited" and the keywords in Column D are
Cell Keyword
D1 Company
D2 XyZ
D3 ABC
D4 Others
Now since A1 has "ABC" in it which is one of the keywords, I need to display "ABC" in cell B1.
Hope I am clear.
Cheers
Rishu
I am facing a problem in excel. Suppose I have entries in Column A and I have a few keywords in Column D. I need to check if the entry in column A contains one of the kepwords in Column D. The formula for the check will be written in Column B and if it contains the keyword, I need to display it in column B.
For Eg.
Cell A1 has an entry "ABC Holdings Limited" and the keywords in Column D are
Cell Keyword
D1 Company
D2 XyZ
D3 ABC
D4 Others
Now since A1 has "ABC" in it which is one of the keywords, I need to display "ABC" in cell B1.
Hope I am clear.
Cheers
Rishu
Hello Everyone
I want a formula in column Bto check data IN column C & column G by comparing tabel N to Q in present example column g is shallow & column c is 300 required result is 1.2. Column N1 & M1 are range of dia to be compaired
A B C D E F G
1.5 300 3.91 0.13 4.04 Deep 1.5 300 3.75 0.03 3.78 Deep 1.2 300 3.48 0.15 3.33 Shallow 1.2 300 2.72 0.62 2.1 Shallow
N M O P Q
DIA Shallow Deep Very Deep 200 400 1.2 1.5 1.5 500 800 1.8 1.8 1.8 900 1200 2.1 2.1 2.1
I hope my explanation clear as i m not able to post HTML post
thanks in advance
I want a formula in column Bto check data IN column C & column G by comparing tabel N to Q in present example column g is shallow & column c is 300 required result is 1.2. Column N1 & M1 are range of dia to be compaired
A B C D E F G
1.5 300 3.91 0.13 4.04 Deep 1.5 300 3.75 0.03 3.78 Deep 1.2 300 3.48 0.15 3.33 Shallow 1.2 300 2.72 0.62 2.1 Shallow
N M O P Q
DIA Shallow Deep Very Deep 200 400 1.2 1.5 1.5 500 800 1.8 1.8 1.8 900 1200 2.1 2.1 2.1
I hope my explanation clear as i m not able to post HTML post
thanks in advance
Hi
I am attaching two screen shots for a detail explaination.
As you can see the blue lines are Entry lines which have Lines and Grlines under it(as child)
If you see E column which has Journal Entry Number and F column which has User id column.
And within same the Journal Entry Number column there might be different number of users with different user ids.
Hence i have created a Pivot table for the first five columns for this case.
The 2nd screenshot i filtered the grouping level column that has only the Lines and Grlines(No Entry level) and all the Journal Entry Numbers such as MTX10001158,MTX10001163 etc and all the User ids that fall under its respective Journal Entry Number.
Here I want to know whether i can get the Distinct or Uniqe Count of the different User ids within the same Journal Entry Number column.
Example in the 1st screenshot that has MTX10001158 Journal Entry Number has
USER95
USER94
USER95
USER94
in the line and grlines level.
so i want count=2 for User id USER94
and count = 2 for User id USER95 within the same Journal Entry using Pivot table and so on for all the Journal Entry Numbers.
That is Distinct count of different user ids within the same Journal Entry Number using pivot table and
if possible the sum of count of user ids distict count.(example count =2 for user94 and count for user95 is 2 so sum is 2+2=4)
Please help me with this query.
Thank you.
I am attaching two screen shots for a detail explaination.
As you can see the blue lines are Entry lines which have Lines and Grlines under it(as child)
If you see E column which has Journal Entry Number and F column which has User id column.
And within same the Journal Entry Number column there might be different number of users with different user ids.
Hence i have created a Pivot table for the first five columns for this case.
The 2nd screenshot i filtered the grouping level column that has only the Lines and Grlines(No Entry level) and all the Journal Entry Numbers such as MTX10001158,MTX10001163 etc and all the User ids that fall under its respective Journal Entry Number.
Here I want to know whether i can get the Distinct or Uniqe Count of the different User ids within the same Journal Entry Number column.
Example in the 1st screenshot that has MTX10001158 Journal Entry Number has
USER95
USER94
USER95
USER94
in the line and grlines level.
so i want count=2 for User id USER94
and count = 2 for User id USER95 within the same Journal Entry using Pivot table and so on for all the Journal Entry Numbers.
That is Distinct count of different user ids within the same Journal Entry Number using pivot table and
if possible the sum of count of user ids distict count.(example count =2 for user94 and count for user95 is 2 so sum is 2+2=4)
Please help me with this query.
Thank you.
I am running a column of decending and assending numerical values and I am
not sure which cell will contain the last entry. However, when the last
numerical figure posts, I need to move this value to another cell location,
automatically. What function can I use to obtain this result? There are blank
cells between the last entry in the column and the desired cell, of
destination. Rather then Copy & Paste. The location and value of the last
entry in a column may vary from month to month, but it is important to
relocate this amount to a new destination cell. I am unaware of any function
that will give me the desired results.
not sure which cell will contain the last entry. However, when the last
numerical figure posts, I need to move this value to another cell location,
automatically. What function can I use to obtain this result? There are blank
cells between the last entry in the column and the desired cell, of
destination. Rather then Copy & Paste. The location and value of the last
entry in a column may vary from month to month, but it is important to
relocate this amount to a new destination cell. I am unaware of any function
that will give me the desired results.
I'm having a heck of a time trying to figure out how to do this and I'm hoping I can get some help here.
I have some data on one worksheet and I just want to pull it into another sheet  that is easy enough. Idealy I would like to be able to cut and paste the formula and have it change the col ref as I go down the page.
Example:
=Entry!F$18+Entry!F$19
if I copy this down it keeps it exactly the same (if I take out the $ it changes the row ref as it should). What I want it to do is when I copy it in is have it change to
=Entry!G$18+Entry!G$19
=Entry!H$18+Entry!H$19
=Entry!I$18+Entry!I$19
etc...
rather than copy it then have to had edit the col ref.
Is there a way to do this? (I'm using excel 2007) Thanks for any help.
Phyllis
I have some data on one worksheet and I just want to pull it into another sheet  that is easy enough. Idealy I would like to be able to cut and paste the formula and have it change the col ref as I go down the page.
Example:
=Entry!F$18+Entry!F$19
if I copy this down it keeps it exactly the same (if I take out the $ it changes the row ref as it should). What I want it to do is when I copy it in is have it change to
=Entry!G$18+Entry!G$19
=Entry!H$18+Entry!H$19
=Entry!I$18+Entry!I$19
etc...
rather than copy it then have to had edit the col ref.
Is there a way to do this? (I'm using excel 2007) Thanks for any help.
Phyllis
Currently I'm using the following code to find the bottommost entry in column B .....
Code:
However, I have a large number of these and my program is running enormously slowly, so I'm wondering if the numerous searches from the bottom cell of each column upwards to find the bottommost entry is too much.
Is there a code that could (let's say for column B) find the cell halfway between the bottom of the column and the top, see if there's an entry. If there is NOT, then find the cell halfway between that position and the top and see if that cell has an entry. If the first search DID find an entry, then it would find the cell halfway between that position and the bottom of the column and see if that cell had an entry.
These searches would continue on and on, either upwards or downwards, etc etc, until the bottommost entry was found.
In theory, this would greatly reduce the number of cells searched for an entry compared with the code I provided above.
Any ideas?
Code:
Private Sub Aminus_Click() Range("B65536").End(xlUp).Offset(1, 0) = "A" End Sub
However, I have a large number of these and my program is running enormously slowly, so I'm wondering if the numerous searches from the bottom cell of each column upwards to find the bottommost entry is too much.
Is there a code that could (let's say for column B) find the cell halfway between the bottom of the column and the top, see if there's an entry. If there is NOT, then find the cell halfway between that position and the top and see if that cell has an entry. If the first search DID find an entry, then it would find the cell halfway between that position and the bottom of the column and see if that cell had an entry.
These searches would continue on and on, either upwards or downwards, etc etc, until the bottommost entry was found.
In theory, this would greatly reduce the number of cells searched for an entry compared with the code I provided above.
Any ideas?
Hi,
I have two columns, both have a drop down menus (created by Data, Validation,list). When a user selects an entry in one column i would like to restrict the entry in the other column(vice versa). In other words, he should be able to select an entry in one of the column only at any pointof time.
Thanks,
Suresh
I have two columns, both have a drop down menus (created by Data, Validation,list). When a user selects an entry in one column i would like to restrict the entry in the other column(vice versa). In other words, he should be able to select an entry in one of the column only at any pointof time.
Thanks,
Suresh
(specifically looking for something to total the number of entries of particular text, and add 1 to gain a new number id for another entry of that type, on enterkeyhit)
Hi,
I have a basic table for listing code names, with 2 columns. Column B  name code (string, e.g. cell B1 is DOG, cell B2 is CAT, B3 is DOG, B4 is RAB, B5 is CAT, and B6 is DOG). Column C is where the id number goes for each entry in column B. So row 2 would read (B2)DOG (C2)001, and respectively, row 3 would be CAT 001, row 4 would be DOG 002, row 5 would be RAB 001, row 6 would be CAT 002, and row 7 would be DOG 003 :
A B C (automatically generated numbering)
DOG 001
CAT 001
DOG 002
RAB 001
CAT 002
DOG 003
What I want to be able to do is to enter the code (CAT/DOG/RAB) into any cell in column B and the numbering in column C to be automatically generated when I hit enter for any entry in column B. Postsorting is not an option. It needs to be auto, and on entry of the text part. And if I add another entry in half way up the chart, it will automatically adjust the numbering...so if I have DOG 001, DOG 002 and DOG 003 already, but I add a DOG in after DOG 001, then the new one becomes DOG 002, the old DOG 002 becomes DOG 003, etc etc. I may have thousands of entries, so its important that its auto. I also need a countup for each type of entry in columnB on a second excel sheet. So say, the last DOG entry is DOG 014, then on sheet 2, the cell to the right of the one that says "Number of DOGs" says 14. I know this is better suited to using Access, but for compatibilities sake with other things, it would be fantastic to get this working in excel.
I have gone through as much of the VBA tutorials I can find as I can in what limited spare time I have, but I'm just not getting any real answers. Can someone please help. I would be hugely grateful.
Hi,
I have a basic table for listing code names, with 2 columns. Column B  name code (string, e.g. cell B1 is DOG, cell B2 is CAT, B3 is DOG, B4 is RAB, B5 is CAT, and B6 is DOG). Column C is where the id number goes for each entry in column B. So row 2 would read (B2)DOG (C2)001, and respectively, row 3 would be CAT 001, row 4 would be DOG 002, row 5 would be RAB 001, row 6 would be CAT 002, and row 7 would be DOG 003 :
A B C (automatically generated numbering)
DOG 001
CAT 001
DOG 002
RAB 001
CAT 002
DOG 003
What I want to be able to do is to enter the code (CAT/DOG/RAB) into any cell in column B and the numbering in column C to be automatically generated when I hit enter for any entry in column B. Postsorting is not an option. It needs to be auto, and on entry of the text part. And if I add another entry in half way up the chart, it will automatically adjust the numbering...so if I have DOG 001, DOG 002 and DOG 003 already, but I add a DOG in after DOG 001, then the new one becomes DOG 002, the old DOG 002 becomes DOG 003, etc etc. I may have thousands of entries, so its important that its auto. I also need a countup for each type of entry in columnB on a second excel sheet. So say, the last DOG entry is DOG 014, then on sheet 2, the cell to the right of the one that says "Number of DOGs" says 14. I know this is better suited to using Access, but for compatibilities sake with other things, it would be fantastic to get this working in excel.
I have gone through as much of the VBA tutorials I can find as I can in what limited spare time I have, but I'm just not getting any real answers. Can someone please help. I would be hugely grateful.
Im am in need of some help with a current work problem. I have a master data entry sheet with Headers in column 1 and each additional column is 1x event.
Data Entry Sheet:
Data Entry A B C D E F G 1 Julian Date 1 2 3 4 5 6 2 Group A B C D E F 3 # of passengers 1 2 3 4 5 6 4 Transportation Mode Air Ground Air Ground Air Ground 5 Time Request Generated 09:01 09:01 09:02 09:03 09:04 09:05 6 Request submitted 09:10 09:11 09:12 09:13 09:14 09:15 7 Mission Approval Time 09:15 09:16 09:17 09:18 09:19 09:20 8 Mission Number A1 A2 A3 A4 A5 A6 9 Launch Time 09:20 09:21 09:22 09:23 09:24 09:25 10 Customer Arrives Drop off point 09:30 09:31 09:32 09:33 09:34 09:35 11 Time arrived Destination 09:40 10:41 09:42 10:43 09:44 10:45 Excel 2007
Based on the type of event label I would like it to auto populate the time differences I have created. Currently I am able to get the calulations to occur as I want, but it populates into all sheets. I think a conditional if then type formula would work, but I dont want to leave empty columns.
Air Mission Sheet:
Air A B C D E F G 1 Julian Date 1 2 3 4 5 6 2 Group A B C D E F 3 # Passengers 1 2 3 4 5 6 4 Time Difference from Request submitted from Request Gernation time 9 min 10 min 10 min 10 min 10 min 10 min 5 Time from Submission to Mission Approval 5 min 5 min 5 min 5 min 5 min 5 min 6 Mission approval to Launch Time 5 min 5 min 5 min 5 min 5 min 5 min 7 Launch Time to Drop off point 20 min 80 min 20 min 80 min 20 min 80 min 8 Drop off to Destination Time 10 min 70 min 10 min 70 min 10 min 70 min 9 Total Mission Time 30 min 90 min 30 min 90 min 30 min 90 min Excel 2007
Worksheet Formulas Cell Formula B1 ='Data Entry'!B1 C1 ='Data Entry'!C1 D1 ='Data Entry'!D1 E1 ='Data Entry'!E1 F1 ='Data Entry'!F1 G1 ='Data Entry'!G1 B2 ='Data Entry'!B2 C2 ='Data Entry'!C2 D2 ='Data Entry'!D2 E2 ='Data Entry'!E2 F2 ='Data Entry'!F2 G2 ='Data Entry'!G2 B3 ='Data Entry'!B3 C3 ='Data Entry'!C3 D3 ='Data Entry'!D3 E3 ='Data Entry'!E3 F3 ='Data Entry'!F3 G3 ='Data Entry'!G3 B4 =TEXT( IF( ( ( 'Data Entry'!B6'Data Entry'!B5 )*1440 )<1, ( 'Data Entry'!B6'Data Entry'!B5 )*1440+1440, ( 'Data Entry'!B6'Data Entry'!B5 )*1440 ), "#" ) & " min" C4 =TEXT( IF( ( ( 'Data Entry'!C6'Data Entry'!C5 )*1440 )<1, ( 'Data Entry'!C6'Data Entry'!C5 )*1440+1440, ( 'Data Entry'!C6'Data Entry'!C5 )*1440 ), "#" ) & " min" D4 =TEXT( IF( ( ( 'Data Entry'!D6'Data Entry'!D5 )*1440 )<1, ( 'Data Entry'!D6'Data Entry'!D5 )*1440+1440, ( 'Data Entry'!D6'Data Entry'!D5 )*1440 ), "#" ) & " min" E4 =TEXT( IF( ( ( 'Data Entry'!E6'Data Entry'!E5 )*1440 )<1, ( 'Data Entry'!E6'Data Entry'!E5 )*1440+1440, ( 'Data Entry'!E6'Data Entry'!E5 )*1440 ), "#" ) & " min" F4 =TEXT( IF( ( ( 'Data Entry'!F6'Data Entry'!F5 )*1440 )<1, ( 'Data Entry'!F6'Data Entry'!F5 )*1440+1440, ( 'Data Entry'!F6'Data Entry'!F5 )*1440 ), "#" ) & " min" G4 =TEXT( IF( ( ( 'Data Entry'!G6'Data Entry'!G5 )*1440 )<1, ( 'Data Entry'!G6'Data Entry'!G5 )*1440+1440, ( 'Data Entry'!G6'Data Entry'!G5 )*1440 ), "#" ) & " min" B5 =TEXT( IF( ( ( 'Data Entry'!B7'Data Entry'!B6 )*1440 )<1, ( 'Data Entry'!B7'Data Entry'!B6 )*1440+1440, ( 'Data Entry'!B7'Data Entry'!B6 )*1440 ), "#" ) & " min" C5 =TEXT( IF( ( ( 'Data Entry'!C7'Data Entry'!C6 )*1440 )<1, ( 'Data Entry'!C7'Data Entry'!C6 )*1440+1440, ( 'Data Entry'!C7'Data Entry'!C6 )*1440 ), "#" ) & " min" D5 =TEXT( IF( ( ( 'Data Entry'!D7'Data Entry'!D6 )*1440 )<1, ( 'Data Entry'!D7'Data Entry'!D6 )*1440+1440, ( 'Data Entry'!D7'Data Entry'!D6 )*1440 ), "#" ) & " min" E5 =TEXT( IF( ( ( 'Data Entry'!E7'Data Entry'!E6 )*1440 )<1, ( 'Data Entry'!E7'Data Entry'!E6 )*1440+1440, ( 'Data Entry'!E7'Data Entry'!E6 )*1440 ), "#" ) & " min" F5 =TEXT( IF( ( ( 'Data Entry'!F7'Data Entry'!F6 )*1440 )<1, ( 'Data Entry'!F7'Data Entry'!F6 )*1440+1440, ( 'Data Entry'!F7'Data Entry'!F6 )*1440 ), "#" ) & " min" G5 =TEXT( IF( ( ( 'Data Entry'!G7'Data Entry'!G6 )*1440 )<1, ( 'Data Entry'!G7'Data Entry'!G6 )*1440+1440, ( 'Data Entry'!G7'Data Entry'!G6 )*1440 ), "#" ) & " min" B6 =TEXT( IF( ( ( 'Data Entry'!B9'Data Entry'!B7 )*1440 )<1,( 'Data Entry'!B9'Data Entry'!B7 )*1440+1440,( 'Data Entry'!B9'Data Entry'!B7 )*1440 ),"#" )&" min" C6 =TEXT( IF( ( ( 'Data Entry'!C9'Data Entry'!C7 )*1440 )<1,( 'Data Entry'!C9'Data Entry'!C7 )*1440+1440,( 'Data Entry'!C9'Data Entry'!C7 )*1440 ),"#" )&" min" D6 =TEXT( IF( ( ( 'Data Entry'!D9'Data Entry'!D7 )*1440 )<1,( 'Data Entry'!D9'Data Entry'!D7 )*1440+1440,( 'Data Entry'!D9'Data Entry'!D7 )*1440 ),"#" )&" min" E6 =TEXT( IF( ( ( 'Data Entry'!E9'Data Entry'!E7 )*1440 )<1,( 'Data Entry'!E9'Data Entry'!E7 )*1440+1440,( 'Data Entry'!E9'Data Entry'!E7 )*1440 ),"#" )&" min" F6 =TEXT( IF( ( ( 'Data Entry'!F9'Data Entry'!F7 )*1440 )<1,( 'Data Entry'!F9'Data Entry'!F7 )*1440+1440,( 'Data Entry'!F9'Data Entry'!F7 )*1440 ),"#" )&" min" G6 =TEXT( IF( ( ( 'Data Entry'!G9'Data Entry'!G7 )*1440 )<1,( 'Data Entry'!G9'Data Entry'!G7 )*1440+1440,( 'Data Entry'!G9'Data Entry'!G7 )*1440 ),"#" )&" min" B7 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B9 )*1440 )<1,( 'Data Entry'!B11'Data Entry'!B9 )*1440+1440,( 'Data Entry'!B11'Data Entry'!B9 )*1440 ),"#" )&" min" C7 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C9 )*1440 )<1,( 'Data Entry'!C11'Data Entry'!C9 )*1440+1440,( 'Data Entry'!C11'Data Entry'!C9 )*1440 ),"#" )&" min" D7 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D9 )*1440 )<1,( 'Data Entry'!D11'Data Entry'!D9 )*1440+1440,( 'Data Entry'!D11'Data Entry'!D9 )*1440 ),"#" )&" min" E7 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E9 )*1440 )<1,( 'Data Entry'!E11'Data Entry'!E9 )*1440+1440,( 'Data Entry'!E11'Data Entry'!E9 )*1440 ),"#" )&" min" F7 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F9 )*1440 )<1,( 'Data Entry'!F11'Data Entry'!F9 )*1440+1440,( 'Data Entry'!F11'Data Entry'!F9 )*1440 ),"#" )&" min" G7 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G9 )*1440 )<1,( 'Data Entry'!G11'Data Entry'!G9 )*1440+1440,( 'Data Entry'!G11'Data Entry'!G9 )*1440 ),"#" )&" min" B8 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B10 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B10 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B10 )*1440 ), "#" ) & " min" C8 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C10 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C10 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C10 )*1440 ), "#" ) & " min" D8 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D10 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D10 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D10 )*1440 ), "#" ) & " min" E8 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E10 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E10 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E10 )*1440 ), "#" ) & " min" F8 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F10 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F10 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F10 )*1440 ), "#" ) & " min" G8 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G10 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G10 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G10 )*1440 ), "#" ) & " min" B9 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B6 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B6 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B6 )*1440 ), "#" ) & " min" C9 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C6 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C6 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C6 )*1440 ), "#" ) & " min" D9 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D6 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D6 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D6 )*1440 ), "#" ) & " min" E9 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E6 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E6 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E6 )*1440 ), "#" ) & " min" F9 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F6 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F6 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F6 )*1440 ), "#" ) & " min" G9 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G6 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G6 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G6 )*1440 ), "#" ) & " min"
Ground Mission Sheet:
Ground A B C D E F G 1 Julian Date 1 2 3 4 5 6 2 Group A B C D E F 3 # Passengers 1 2 3 4 5 6 4 Request generation to Launch Time 29 min 30 min 30 min 30 min 30 min 30 min 5 Launch time to Destination time 10 min 70 min 10 min 70 min 10 min 70 min 6 Total Mission Time 39 min 100 min 40 min 100 min 40 min 100 min Excel 2007
Worksheet Formulas Cell Formula B1 ='Data Entry'!B1 C1 ='Data Entry'!C1 D1 ='Data Entry'!D1 E1 ='Data Entry'!E1 F1 ='Data Entry'!F1 G1 ='Data Entry'!G1 B2 ='Data Entry'!B2 C2 ='Data Entry'!C2 D2 ='Data Entry'!D2 E2 ='Data Entry'!E2 F2 ='Data Entry'!F2 G2 ='Data Entry'!G2 B3 ='Data Entry'!B3 C3 ='Data Entry'!C3 D3 ='Data Entry'!D3 E3 ='Data Entry'!E3 F3 ='Data Entry'!F3 G3 ='Data Entry'!G3 B4 =TEXT( IF( ( ( 'Data Entry'!B10'Data Entry'!B5 )*1440 )<1, ( 'Data Entry'!B10'Data Entry'!B5 )*1440+1440, ( 'Data Entry'!B10'Data Entry'!B5 )*1440 ), "#" ) & " min" C4 =TEXT( IF( ( ( 'Data Entry'!C10'Data Entry'!C5 )*1440 )<1, ( 'Data Entry'!C10'Data Entry'!C5 )*1440+1440, ( 'Data Entry'!C10'Data Entry'!C5 )*1440 ), "#" ) & " min" D4 =TEXT( IF( ( ( 'Data Entry'!D10'Data Entry'!D5 )*1440 )<1, ( 'Data Entry'!D10'Data Entry'!D5 )*1440+1440, ( 'Data Entry'!D10'Data Entry'!D5 )*1440 ), "#" ) & " min" E4 =TEXT( IF( ( ( 'Data Entry'!E10'Data Entry'!E5 )*1440 )<1, ( 'Data Entry'!E10'Data Entry'!E5 )*1440+1440, ( 'Data Entry'!E10'Data Entry'!E5 )*1440 ), "#" ) & " min" F4 =TEXT( IF( ( ( 'Data Entry'!F10'Data Entry'!F5 )*1440 )<1, ( 'Data Entry'!F10'Data Entry'!F5 )*1440+1440, ( 'Data Entry'!F10'Data Entry'!F5 )*1440 ), "#" ) & " min" G4 =TEXT( IF( ( ( 'Data Entry'!G10'Data Entry'!G5 )*1440 )<1, ( 'Data Entry'!G10'Data Entry'!G5 )*1440+1440, ( 'Data Entry'!G10'Data Entry'!G5 )*1440 ), "#" ) & " min" B5 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B10 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B10 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B10 )*1440 ), "#" ) & " min" C5 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C10 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C10 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C10 )*1440 ), "#" ) & " min" D5 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D10 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D10 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D10 )*1440 ), "#" ) & " min" E5 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E10 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E10 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E10 )*1440 ), "#" ) & " min" F5 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F10 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F10 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F10 )*1440 ), "#" ) & " min" G5 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G10 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G10 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G10 )*1440 ), "#" ) & " min" B6 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B5 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B5 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B5 )*1440 ), "#" ) & " min" C6 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C5 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C5 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C5 )*1440 ), "#" ) & " min" D6 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D5 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D5 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D5 )*1440 ), "#" ) & " min" E6 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E5 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E5 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E5 )*1440 ), "#" ) & " min" F6 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F5 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F5 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F5 )*1440 ), "#" ) & " min" G6 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G5 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G5 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G5 )*1440 ), "#" ) & " min"
What I would like is to check Data sheet Row 4 for each clomun for whether it was a ground or air mission and then populate that sheet with the calulations that I have currently compiled. Rather than my current problem where every mission is on both sheets.
I appreciate your help and assistance.
Data Entry Sheet:
Data Entry A B C D E F G 1 Julian Date 1 2 3 4 5 6 2 Group A B C D E F 3 # of passengers 1 2 3 4 5 6 4 Transportation Mode Air Ground Air Ground Air Ground 5 Time Request Generated 09:01 09:01 09:02 09:03 09:04 09:05 6 Request submitted 09:10 09:11 09:12 09:13 09:14 09:15 7 Mission Approval Time 09:15 09:16 09:17 09:18 09:19 09:20 8 Mission Number A1 A2 A3 A4 A5 A6 9 Launch Time 09:20 09:21 09:22 09:23 09:24 09:25 10 Customer Arrives Drop off point 09:30 09:31 09:32 09:33 09:34 09:35 11 Time arrived Destination 09:40 10:41 09:42 10:43 09:44 10:45 Excel 2007
Based on the type of event label I would like it to auto populate the time differences I have created. Currently I am able to get the calulations to occur as I want, but it populates into all sheets. I think a conditional if then type formula would work, but I dont want to leave empty columns.
Air Mission Sheet:
Air A B C D E F G 1 Julian Date 1 2 3 4 5 6 2 Group A B C D E F 3 # Passengers 1 2 3 4 5 6 4 Time Difference from Request submitted from Request Gernation time 9 min 10 min 10 min 10 min 10 min 10 min 5 Time from Submission to Mission Approval 5 min 5 min 5 min 5 min 5 min 5 min 6 Mission approval to Launch Time 5 min 5 min 5 min 5 min 5 min 5 min 7 Launch Time to Drop off point 20 min 80 min 20 min 80 min 20 min 80 min 8 Drop off to Destination Time 10 min 70 min 10 min 70 min 10 min 70 min 9 Total Mission Time 30 min 90 min 30 min 90 min 30 min 90 min Excel 2007
Worksheet Formulas Cell Formula B1 ='Data Entry'!B1 C1 ='Data Entry'!C1 D1 ='Data Entry'!D1 E1 ='Data Entry'!E1 F1 ='Data Entry'!F1 G1 ='Data Entry'!G1 B2 ='Data Entry'!B2 C2 ='Data Entry'!C2 D2 ='Data Entry'!D2 E2 ='Data Entry'!E2 F2 ='Data Entry'!F2 G2 ='Data Entry'!G2 B3 ='Data Entry'!B3 C3 ='Data Entry'!C3 D3 ='Data Entry'!D3 E3 ='Data Entry'!E3 F3 ='Data Entry'!F3 G3 ='Data Entry'!G3 B4 =TEXT( IF( ( ( 'Data Entry'!B6'Data Entry'!B5 )*1440 )<1, ( 'Data Entry'!B6'Data Entry'!B5 )*1440+1440, ( 'Data Entry'!B6'Data Entry'!B5 )*1440 ), "#" ) & " min" C4 =TEXT( IF( ( ( 'Data Entry'!C6'Data Entry'!C5 )*1440 )<1, ( 'Data Entry'!C6'Data Entry'!C5 )*1440+1440, ( 'Data Entry'!C6'Data Entry'!C5 )*1440 ), "#" ) & " min" D4 =TEXT( IF( ( ( 'Data Entry'!D6'Data Entry'!D5 )*1440 )<1, ( 'Data Entry'!D6'Data Entry'!D5 )*1440+1440, ( 'Data Entry'!D6'Data Entry'!D5 )*1440 ), "#" ) & " min" E4 =TEXT( IF( ( ( 'Data Entry'!E6'Data Entry'!E5 )*1440 )<1, ( 'Data Entry'!E6'Data Entry'!E5 )*1440+1440, ( 'Data Entry'!E6'Data Entry'!E5 )*1440 ), "#" ) & " min" F4 =TEXT( IF( ( ( 'Data Entry'!F6'Data Entry'!F5 )*1440 )<1, ( 'Data Entry'!F6'Data Entry'!F5 )*1440+1440, ( 'Data Entry'!F6'Data Entry'!F5 )*1440 ), "#" ) & " min" G4 =TEXT( IF( ( ( 'Data Entry'!G6'Data Entry'!G5 )*1440 )<1, ( 'Data Entry'!G6'Data Entry'!G5 )*1440+1440, ( 'Data Entry'!G6'Data Entry'!G5 )*1440 ), "#" ) & " min" B5 =TEXT( IF( ( ( 'Data Entry'!B7'Data Entry'!B6 )*1440 )<1, ( 'Data Entry'!B7'Data Entry'!B6 )*1440+1440, ( 'Data Entry'!B7'Data Entry'!B6 )*1440 ), "#" ) & " min" C5 =TEXT( IF( ( ( 'Data Entry'!C7'Data Entry'!C6 )*1440 )<1, ( 'Data Entry'!C7'Data Entry'!C6 )*1440+1440, ( 'Data Entry'!C7'Data Entry'!C6 )*1440 ), "#" ) & " min" D5 =TEXT( IF( ( ( 'Data Entry'!D7'Data Entry'!D6 )*1440 )<1, ( 'Data Entry'!D7'Data Entry'!D6 )*1440+1440, ( 'Data Entry'!D7'Data Entry'!D6 )*1440 ), "#" ) & " min" E5 =TEXT( IF( ( ( 'Data Entry'!E7'Data Entry'!E6 )*1440 )<1, ( 'Data Entry'!E7'Data Entry'!E6 )*1440+1440, ( 'Data Entry'!E7'Data Entry'!E6 )*1440 ), "#" ) & " min" F5 =TEXT( IF( ( ( 'Data Entry'!F7'Data Entry'!F6 )*1440 )<1, ( 'Data Entry'!F7'Data Entry'!F6 )*1440+1440, ( 'Data Entry'!F7'Data Entry'!F6 )*1440 ), "#" ) & " min" G5 =TEXT( IF( ( ( 'Data Entry'!G7'Data Entry'!G6 )*1440 )<1, ( 'Data Entry'!G7'Data Entry'!G6 )*1440+1440, ( 'Data Entry'!G7'Data Entry'!G6 )*1440 ), "#" ) & " min" B6 =TEXT( IF( ( ( 'Data Entry'!B9'Data Entry'!B7 )*1440 )<1,( 'Data Entry'!B9'Data Entry'!B7 )*1440+1440,( 'Data Entry'!B9'Data Entry'!B7 )*1440 ),"#" )&" min" C6 =TEXT( IF( ( ( 'Data Entry'!C9'Data Entry'!C7 )*1440 )<1,( 'Data Entry'!C9'Data Entry'!C7 )*1440+1440,( 'Data Entry'!C9'Data Entry'!C7 )*1440 ),"#" )&" min" D6 =TEXT( IF( ( ( 'Data Entry'!D9'Data Entry'!D7 )*1440 )<1,( 'Data Entry'!D9'Data Entry'!D7 )*1440+1440,( 'Data Entry'!D9'Data Entry'!D7 )*1440 ),"#" )&" min" E6 =TEXT( IF( ( ( 'Data Entry'!E9'Data Entry'!E7 )*1440 )<1,( 'Data Entry'!E9'Data Entry'!E7 )*1440+1440,( 'Data Entry'!E9'Data Entry'!E7 )*1440 ),"#" )&" min" F6 =TEXT( IF( ( ( 'Data Entry'!F9'Data Entry'!F7 )*1440 )<1,( 'Data Entry'!F9'Data Entry'!F7 )*1440+1440,( 'Data Entry'!F9'Data Entry'!F7 )*1440 ),"#" )&" min" G6 =TEXT( IF( ( ( 'Data Entry'!G9'Data Entry'!G7 )*1440 )<1,( 'Data Entry'!G9'Data Entry'!G7 )*1440+1440,( 'Data Entry'!G9'Data Entry'!G7 )*1440 ),"#" )&" min" B7 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B9 )*1440 )<1,( 'Data Entry'!B11'Data Entry'!B9 )*1440+1440,( 'Data Entry'!B11'Data Entry'!B9 )*1440 ),"#" )&" min" C7 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C9 )*1440 )<1,( 'Data Entry'!C11'Data Entry'!C9 )*1440+1440,( 'Data Entry'!C11'Data Entry'!C9 )*1440 ),"#" )&" min" D7 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D9 )*1440 )<1,( 'Data Entry'!D11'Data Entry'!D9 )*1440+1440,( 'Data Entry'!D11'Data Entry'!D9 )*1440 ),"#" )&" min" E7 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E9 )*1440 )<1,( 'Data Entry'!E11'Data Entry'!E9 )*1440+1440,( 'Data Entry'!E11'Data Entry'!E9 )*1440 ),"#" )&" min" F7 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F9 )*1440 )<1,( 'Data Entry'!F11'Data Entry'!F9 )*1440+1440,( 'Data Entry'!F11'Data Entry'!F9 )*1440 ),"#" )&" min" G7 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G9 )*1440 )<1,( 'Data Entry'!G11'Data Entry'!G9 )*1440+1440,( 'Data Entry'!G11'Data Entry'!G9 )*1440 ),"#" )&" min" B8 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B10 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B10 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B10 )*1440 ), "#" ) & " min" C8 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C10 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C10 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C10 )*1440 ), "#" ) & " min" D8 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D10 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D10 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D10 )*1440 ), "#" ) & " min" E8 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E10 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E10 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E10 )*1440 ), "#" ) & " min" F8 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F10 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F10 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F10 )*1440 ), "#" ) & " min" G8 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G10 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G10 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G10 )*1440 ), "#" ) & " min" B9 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B6 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B6 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B6 )*1440 ), "#" ) & " min" C9 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C6 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C6 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C6 )*1440 ), "#" ) & " min" D9 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D6 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D6 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D6 )*1440 ), "#" ) & " min" E9 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E6 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E6 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E6 )*1440 ), "#" ) & " min" F9 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F6 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F6 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F6 )*1440 ), "#" ) & " min" G9 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G6 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G6 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G6 )*1440 ), "#" ) & " min"
Ground Mission Sheet:
Ground A B C D E F G 1 Julian Date 1 2 3 4 5 6 2 Group A B C D E F 3 # Passengers 1 2 3 4 5 6 4 Request generation to Launch Time 29 min 30 min 30 min 30 min 30 min 30 min 5 Launch time to Destination time 10 min 70 min 10 min 70 min 10 min 70 min 6 Total Mission Time 39 min 100 min 40 min 100 min 40 min 100 min Excel 2007
Worksheet Formulas Cell Formula B1 ='Data Entry'!B1 C1 ='Data Entry'!C1 D1 ='Data Entry'!D1 E1 ='Data Entry'!E1 F1 ='Data Entry'!F1 G1 ='Data Entry'!G1 B2 ='Data Entry'!B2 C2 ='Data Entry'!C2 D2 ='Data Entry'!D2 E2 ='Data Entry'!E2 F2 ='Data Entry'!F2 G2 ='Data Entry'!G2 B3 ='Data Entry'!B3 C3 ='Data Entry'!C3 D3 ='Data Entry'!D3 E3 ='Data Entry'!E3 F3 ='Data Entry'!F3 G3 ='Data Entry'!G3 B4 =TEXT( IF( ( ( 'Data Entry'!B10'Data Entry'!B5 )*1440 )<1, ( 'Data Entry'!B10'Data Entry'!B5 )*1440+1440, ( 'Data Entry'!B10'Data Entry'!B5 )*1440 ), "#" ) & " min" C4 =TEXT( IF( ( ( 'Data Entry'!C10'Data Entry'!C5 )*1440 )<1, ( 'Data Entry'!C10'Data Entry'!C5 )*1440+1440, ( 'Data Entry'!C10'Data Entry'!C5 )*1440 ), "#" ) & " min" D4 =TEXT( IF( ( ( 'Data Entry'!D10'Data Entry'!D5 )*1440 )<1, ( 'Data Entry'!D10'Data Entry'!D5 )*1440+1440, ( 'Data Entry'!D10'Data Entry'!D5 )*1440 ), "#" ) & " min" E4 =TEXT( IF( ( ( 'Data Entry'!E10'Data Entry'!E5 )*1440 )<1, ( 'Data Entry'!E10'Data Entry'!E5 )*1440+1440, ( 'Data Entry'!E10'Data Entry'!E5 )*1440 ), "#" ) & " min" F4 =TEXT( IF( ( ( 'Data Entry'!F10'Data Entry'!F5 )*1440 )<1, ( 'Data Entry'!F10'Data Entry'!F5 )*1440+1440, ( 'Data Entry'!F10'Data Entry'!F5 )*1440 ), "#" ) & " min" G4 =TEXT( IF( ( ( 'Data Entry'!G10'Data Entry'!G5 )*1440 )<1, ( 'Data Entry'!G10'Data Entry'!G5 )*1440+1440, ( 'Data Entry'!G10'Data Entry'!G5 )*1440 ), "#" ) & " min" B5 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B10 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B10 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B10 )*1440 ), "#" ) & " min" C5 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C10 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C10 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C10 )*1440 ), "#" ) & " min" D5 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D10 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D10 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D10 )*1440 ), "#" ) & " min" E5 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E10 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E10 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E10 )*1440 ), "#" ) & " min" F5 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F10 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F10 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F10 )*1440 ), "#" ) & " min" G5 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G10 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G10 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G10 )*1440 ), "#" ) & " min" B6 =TEXT( IF( ( ( 'Data Entry'!B11'Data Entry'!B5 )*1440 )<1, ( 'Data Entry'!B11'Data Entry'!B5 )*1440+1440, ( 'Data Entry'!B11'Data Entry'!B5 )*1440 ), "#" ) & " min" C6 =TEXT( IF( ( ( 'Data Entry'!C11'Data Entry'!C5 )*1440 )<1, ( 'Data Entry'!C11'Data Entry'!C5 )*1440+1440, ( 'Data Entry'!C11'Data Entry'!C5 )*1440 ), "#" ) & " min" D6 =TEXT( IF( ( ( 'Data Entry'!D11'Data Entry'!D5 )*1440 )<1, ( 'Data Entry'!D11'Data Entry'!D5 )*1440+1440, ( 'Data Entry'!D11'Data Entry'!D5 )*1440 ), "#" ) & " min" E6 =TEXT( IF( ( ( 'Data Entry'!E11'Data Entry'!E5 )*1440 )<1, ( 'Data Entry'!E11'Data Entry'!E5 )*1440+1440, ( 'Data Entry'!E11'Data Entry'!E5 )*1440 ), "#" ) & " min" F6 =TEXT( IF( ( ( 'Data Entry'!F11'Data Entry'!F5 )*1440 )<1, ( 'Data Entry'!F11'Data Entry'!F5 )*1440+1440, ( 'Data Entry'!F11'Data Entry'!F5 )*1440 ), "#" ) & " min" G6 =TEXT( IF( ( ( 'Data Entry'!G11'Data Entry'!G5 )*1440 )<1, ( 'Data Entry'!G11'Data Entry'!G5 )*1440+1440, ( 'Data Entry'!G11'Data Entry'!G5 )*1440 ), "#" ) & " min"
What I would like is to check Data sheet Row 4 for each clomun for whether it was a ground or air mission and then populate that sheet with the calulations that I have currently compiled. Rather than my current problem where every mission is on both sheets.
I appreciate your help and assistance.
Hello,
I've created an excel worksheet. In the column F, I've a user form which will help me to select one or more entry and display in a cell in column F.
In column I am using the formula "=((H2/20))" for calculating individual effort.
Now, in column J, I need to display the remaining effort.
For this I'm using the formula, "=(20(COUNTIF($A$2:A2,A2)=COUNT($A$2:A2))*SUM($H1:H$2)H2)/20"
in J3 & "=((20H2)/20)" in J2, which needs to be modified a bit.
Now, lets say I've selected abc, xyz in F3:
Then in I3 results should be displayed as  "abc: 25%, xyz: 25%"
In J3, the results should be displayed as  "abc:75%, xyz :75%".
Now, again if I select abc, lmn in F4:
Then in I4 results should be displayed as "abc:25%, lmn:25%"
In J4, the results should be displayed as "abc: 50%, lmn :75%".
Please note, since in J3: the remaining effort for abc was 75% and in I4 the effort required was 25%. Hence the calculated result in J4 (I4J3).
The logic for the formula has to be :
For J3:
IF(A3=A2) && the occurrence of an entry in F3 is for the first time THEN (J3=(20(G2+G3))/20)
ELSE J3=Jn  I3.
Here, Jn denotes the last occurence of the an entry displayed F3.
For J4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in F4 is for the first time THEN (I4=(20(G2+G3+G4))/20)
ELSE J4=Jn  I4.
Similarly,
For J5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in F5 is for the first time THEN (I5= (20(G2+G3+G4))/20)
ELSE J5=Jn  I5.
and so on...
I've attached the excel for your reference.
I've created an excel worksheet. In the column F, I've a user form which will help me to select one or more entry and display in a cell in column F.
In column I am using the formula "=((H2/20))" for calculating individual effort.
Now, in column J, I need to display the remaining effort.
For this I'm using the formula, "=(20(COUNTIF($A$2:A2,A2)=COUNT($A$2:A2))*SUM($H1:H$2)H2)/20"
in J3 & "=((20H2)/20)" in J2, which needs to be modified a bit.
Now, lets say I've selected abc, xyz in F3:
Then in I3 results should be displayed as  "abc: 25%, xyz: 25%"
In J3, the results should be displayed as  "abc:75%, xyz :75%".
Now, again if I select abc, lmn in F4:
Then in I4 results should be displayed as "abc:25%, lmn:25%"
In J4, the results should be displayed as "abc: 50%, lmn :75%".
Please note, since in J3: the remaining effort for abc was 75% and in I4 the effort required was 25%. Hence the calculated result in J4 (I4J3).
The logic for the formula has to be :
For J3:
IF(A3=A2) && the occurrence of an entry in F3 is for the first time THEN (J3=(20(G2+G3))/20)
ELSE J3=Jn  I3.
Here, Jn denotes the last occurence of the an entry displayed F3.
For J4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in F4 is for the first time THEN (I4=(20(G2+G3+G4))/20)
ELSE J4=Jn  I4.
Similarly,
For J5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in F5 is for the first time THEN (I5= (20(G2+G3+G4))/20)
ELSE J5=Jn  I5.
and so on...
I've attached the excel for your reference.
Hello,
I've created an excel worksheet. In the column F, I've a user form which will help me to select one or more entry and display in a cell in column F.
In column I am using the formula "=((H2/20))" for calculating individual effort.
Now, in column J, I need to display the remaining effort.
For this I'm using the formula, "=(20(COUNTIF($A$2:A2,A2)=COUNT($A$2:A2))*SUM($H1:H$2)H2)/20"
in J3 & "=((20H2)/20)" in J2, which needs to be modified a bit.
Now, lets say I've selected abc, xyz in F3:
Then in I3 results should be displayed as  "abc: 25%, xyz: 25%"
In J3, the results should be displayed as  "abc:75%, xyz :75%".
Now, again if I select abc, lmn in F4:
Then in I4 results should be displayed as "abc:25%, lmn:25%"
In J4, the results should be displayed as "abc: 50%, lmn :75%".
Please note, since in J3: the remaining effort for abc was 75% and in I4 the effort required was 25%. Hence the calculated result in J4 (I4J3).
The logic for the formula has to be :
For J3:
IF(A3=A2) && the occurrence of an entry in F3 is for the first time THEN (J3=(20(G2+G3))/20)
ELSE J3=Jn  I3.
Here, Jn denotes the last occurence of the an entry displayed F3.
For J4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in F4 is for the first time THEN (I4=(20(G2+G3+G4))/20)
ELSE J4=Jn  I4.
Similarly,
For J5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in F5 is for the first time THEN (I5= (20(G2+G3+G4))/20)
ELSE J5=Jn  I5.
and so on...
I've attached the excel for your reference.
I've created an excel worksheet. In the column F, I've a user form which will help me to select one or more entry and display in a cell in column F.
In column I am using the formula "=((H2/20))" for calculating individual effort.
Now, in column J, I need to display the remaining effort.
For this I'm using the formula, "=(20(COUNTIF($A$2:A2,A2)=COUNT($A$2:A2))*SUM($H1:H$2)H2)/20"
in J3 & "=((20H2)/20)" in J2, which needs to be modified a bit.
Now, lets say I've selected abc, xyz in F3:
Then in I3 results should be displayed as  "abc: 25%, xyz: 25%"
In J3, the results should be displayed as  "abc:75%, xyz :75%".
Now, again if I select abc, lmn in F4:
Then in I4 results should be displayed as "abc:25%, lmn:25%"
In J4, the results should be displayed as "abc: 50%, lmn :75%".
Please note, since in J3: the remaining effort for abc was 75% and in I4 the effort required was 25%. Hence the calculated result in J4 (I4J3).
The logic for the formula has to be :
For J3:
IF(A3=A2) && the occurrence of an entry in F3 is for the first time THEN (J3=(20(G2+G3))/20)
ELSE J3=Jn  I3.
Here, Jn denotes the last occurence of the an entry displayed F3.
For J4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in F4 is for the first time THEN (I4=(20(G2+G3+G4))/20)
ELSE J4=Jn  I4.
Similarly,
For J5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in F5 is for the first time THEN (I5= (20(G2+G3+G4))/20)
ELSE J5=Jn  I5.
and so on...
I've attached the excel for your reference.
I have a large if formula for a cashflow model.
This is part of it. If cell G4 = fortnightly then from a reference date (A6) I want to go down through each date and enter a value (G5) next to it.
Is there a shorter way to write this? As my total formula is bigger than Excel 2003 can take
IF(G$4="fortnightly",IF(OR($A6='Data Entry'!$G$9,$A6='Data Entry'!$G$9+14,$A6='Data Entry'!$G$9+28,$A6='Data Entry'!$G$9+42,$A6='Data Entry'!$G$9+56,$A6='Data Entry'!$G$9+70,$A6='Data Entry'!$G$9+84,$A6='Data Entry'!$G$9+98,$A6='Data Entry'!$G$9+112,$A6='Data Entry'!$G$9+126,$A6='Data Entry'!$G$9+140,$A6='Data Entry'!$G$9+154,$A6='Data Entry'!$G$9+168,$A6='Data Entry'!$G$9+182,$A6='Data Entry'!$G$9+196,$A6='Data Entry'!$G$9+210,$A6='Data Entry'!$G$9+224,$A6='Data Entry'!$G$9+238,$A6='Data Entry'!$G$9+252,$A6='Data Entry'!$G$9+266,$A6='Data Entry'!$G$9+280,$A6='Data Entry'!$G$9+294,$A6='Data Entry'!$G$9+308,$A6='Data Entry'!$G$9+322,$A6='Data Entry'!$G$9+336,$A6='Data Entry'!$G$9+350,$A6='Data Entry'!$G$9+364,$A6='Data Entry'!$G$9+378),G$5)
Regards
Steve
This is part of it. If cell G4 = fortnightly then from a reference date (A6) I want to go down through each date and enter a value (G5) next to it.
Is there a shorter way to write this? As my total formula is bigger than Excel 2003 can take
IF(G$4="fortnightly",IF(OR($A6='Data Entry'!$G$9,$A6='Data Entry'!$G$9+14,$A6='Data Entry'!$G$9+28,$A6='Data Entry'!$G$9+42,$A6='Data Entry'!$G$9+56,$A6='Data Entry'!$G$9+70,$A6='Data Entry'!$G$9+84,$A6='Data Entry'!$G$9+98,$A6='Data Entry'!$G$9+112,$A6='Data Entry'!$G$9+126,$A6='Data Entry'!$G$9+140,$A6='Data Entry'!$G$9+154,$A6='Data Entry'!$G$9+168,$A6='Data Entry'!$G$9+182,$A6='Data Entry'!$G$9+196,$A6='Data Entry'!$G$9+210,$A6='Data Entry'!$G$9+224,$A6='Data Entry'!$G$9+238,$A6='Data Entry'!$G$9+252,$A6='Data Entry'!$G$9+266,$A6='Data Entry'!$G$9+280,$A6='Data Entry'!$G$9+294,$A6='Data Entry'!$G$9+308,$A6='Data Entry'!$G$9+322,$A6='Data Entry'!$G$9+336,$A6='Data Entry'!$G$9+350,$A6='Data Entry'!$G$9+364,$A6='Data Entry'!$G$9+378),G$5)
Regards
Steve