Search results

Hello ajayxInc2, I would suggest changing the layout for your worksheet to make it easy to calculate stuff. Col A=Team ColB=Counts ColC=Actuals or you can setup two separate ranges: colA=Team ColB=Counts ColC=Team ColD=Actuals Then you would be able to do simple lookups between the...

Hi Maggie, Trying to reference multiple 2-D ranges on separate sheets in a single calculation without referring to each range individually will require some sophisticated techniques... I do not know any. However, I am not sure you need such sophisticated techniques... Without knowing more...

Since I do not have Excel 2003, I found a site that described the process. There are many other resources available... I found several with the following Google query: excel how to create pivot table 2003 Hope that helps. Cheers, Sajan.

Hi, Attached is a pivot table of your raw data. (Some item codes had multiple descriptions.) Cheers, Sajan.

Hi Pam, Can you post what your raw data looks like (format wise)? It may be better to create a Pivot table for your data. A Pivot table would be able to show the Item #, Description and Total. Cheers, Sajan. Cheers, Sajan.

No need to be embarrassed! We all started with basic stuff! Regarding your question... try the following formula in cell B4 =B3 Then copy that formula to the other cells where a description is needed. If that is not what you are after, please clarify. Cheers, Sajan.

Hello Kevin, You can set your Emp_List to a formula like below: =$A$1:INDEX($A:$A,MATCH(REPT("z",99),$A:$A)) This assumes that your list starts on A1, and is in column A. Cheers, Sajan.

Hello Pam, Welcome to the forum! Are you wanting to lookup the description from some other worksheet? Is the description used as part of the subtotaling? Can you upload a sample worksheet to show what you are looking to do? You can upload a file using the "Upload a File" button. Cheers...

Hi nejjoan14, Assuming the date you want to compare to is in cell B1, put the following formula in cell I49 and copy down: =SUMPRODUCT((LOOKUP(ROW($A$5:$A$46), ROW($A$5:$A$46)/($A$5:$A$46<>0), $A$5:$A$46)=$B$1)*($D$5:$D$46=$A49)*($H$5:$H$46))*24 Cheers, Sajan.

Hello nejjoan14, I am not sure I understand what you mean by "filter this from dates". Are you wanting to add a condition where A5:A46 matches a given date, in addition to the categories like PM, M, E, etc.?

Hello Ramanan, Following is one approach... Assuming the Named Reference "data" refers to B2:F9, then put the following array formula in cell J2 and copy down...

Hi nejjoan14, I am not sure I understand your comment "based on dates" since I do not see any dates to use as a condition. But if you are merely looking to sum the total hours worked for PM, Break, etc., try the following formula in cell I49 and copy down...

Welcome to the forum! Do you mean grouping? If so, select the cells, and press Alt + Shift + Right arrow to group and Left arrow to ungroup Cheers, Sajan.

Here is one more approach: =LEFT(A1&REPT(" ",12),12)

Regarding your question about file size due to formulas vs macros... I am not sure. I am sure it is possible to create a huge file with just macros or just formulas. I am sure one of the other forum members will chime in with some insights.

And in case anyone is interested in an explanation of the most recent formula.... here it is. =IF(LOOKUP(1,MMULT(N(ISNUMBER(0+SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),",","@"),".","@")," ",REPT(" ",100)),(ROW(OFFSET(A$1,,,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2,"...

Good to hear! Thanks for the feedback. Glad to help! By the way, what kind of data are you working with? I normally suggest to folks that if they can control the problems at the source, that would be best. Cheers, Sajan.

Hi Rediska, Try the following formula: =IF(LOOKUP(1,MMULT(N(ISNUMBER(0+SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(A2),",","@"),".","@")," ",REPT(" ",100)),(ROW(OFFSET(A$1,,,LEN(TRIM(A2))-LEN(SUBSTITUTE(A2," ",""))+1))-1)*100+1,100)),{"@",""},""))),{1;-1})),"Bad","Good") Please...

Hi Rediska, Is the comma issue the only problem that needs to be addressed?

Hi, Thanks for the feedback. Glad to help! -Sajan.

Hi Rediska, Those numbers are following the same pattern as before. i.e. the formula is not recognizing a number where a comma is in a position recognized by Excel. As such, the following would not work with the current formula: 1,000 1,000,000 9,999 9,999,999 etc. The solution is the same...

Hi Wim, Here is one approach: =LARGE(IF(FREQUENCY(dateslist,dateslist),dateslist), D7) where D7 points to a cell with values like 7, 6, etc. Cheers, Sajan.

The following are the test strings I used... Incidentally, a number of the format #,### was not one of them! Goes to show the importance of good test cases! Test String Result One Two ,4567 3.45 Three 5678, 67,78 ,98,78, .3 .4.5 .6. .2,3.4 789 767868,1,1,1 34,345,4.4545,45.23 Bad Xyz Two...

I am assuming that the delimiter is a space (char #32). If that is not correct, replace space in the formula with the proper delimiter.

Hi Rediska, Good thing you are testing this since you know what your data looks like! :-) Both 1,000 and 3,456 are valid Excel numbers. In the formula I add 0.1 at the end of numbers, but 1,000.1 is still a valid number! On the other hand 3,00 is not a valid number. that is the reason for...