Posts by darkyam

Re: Solver To Schedule Employees

Welcome to the forum. Your sheet is missing 8am-9am. For the problem itself, I would suggest starting with the times you know you'll have to use PT employees (since FT can start only at 7 or 2), such as 11am when you need 5 more. I'm unclear whether the 50% limit is in terms of number of employees or total dollar amount. If it's number of employees, you can take the total number of employees you would need for the entire day (looks to be about 45), divide by 3, subtract the PT ones you need in the middle of the day, and use the remaining number as PT employees (one after the other to substitute a full-time shift). The remaining holes would be filled by FT employees.

Re: Align (center) Text In Cells Vertically

Highlight the cells and right-click, then click on Format Cells..., then go to the Alignment tab. There are drop downs for both Horizontal and Vertical alignment.

Edit: StephenR and I must have been typing at the same time. Sorry for the dual replies.

Re: Vlookup Versus Indexmatch

TheDude, your formula doesn't take the temperature into account.
Pukks, in cell K7, =IF(SUM(($A$2:$A$11=$J7)*1*($D$2:$D$11=$N$4))=0,0,SUM(($A$2:$A$11=$J7)*($E$2:$E$11)*($D$2:$D$11=$N$4))/SUM(($A$2:$A$11=$J7)*1*($D$2:$D$11=$N$4))) and in cell L7, =IF(SUM(($A$2:$A$11=$J7)*1*($D$2:$D$11=$N$4))=0,0,SQRT((E2:E11-K7)^2/(SUM(($A$2:$A$11=$J7)*1*($D$2:$D$11=$N$4))-1))). Confirm both with Ctrl+Shift+Enter.

Re: Sum Column Values Based On Other Columns

In F6, =SUM((MONTH('DATA ENTRY'!$H$3:$H$2000)=MONTH('SHIP 04 MONTHLY WELD DATA'!$A6))*1)-COUNTBLANK('DATA ENTRY'!$A$3:$A$2000)
In F7 (drag downward), =SUM((MONTH('DATA ENTRY'!$H$3:$H$2000)=MONTH('SHIP 04 MONTHLY WELD DATA'!$A7))*1)
In G6 (drag downward),=SUM((MONTH('DATA ENTRY'!$H$3:$H$2000)=MONTH('SHIP 04 MONTHLY WELD DATA'!$A6))*('DATA ENTRY'!$N$3:$N$2000="R")*1)
Confirm all with Ctrl+Shift+Enter.

Re: Sum Column Values Based On Other Columns

Not sure where your error is coming from; I keep getting 0. More curious are the references to AO995 and AQ997. These cells are blank in your example. You can try the following formula, which is draggable up and down in your table: = SUMPRODUCT(--('DATA ENTRY'!$A$3:$A$2000=$A$3),--(MONTH('DATA ENTRY'!$H$3:$H$2000)=MONTH($A$8)),('DATA ENTRY'!M$3:M$2000)) and confirm with Ctrl+Shift+Enter.

Re: Copy A List Depending On Other Criteria

You could always record or write a macro to go through the steps and then just run the macro.

Re: Copy A List Depending On Other Criteria

Put whatever mark you want in the column, apply a filter, and then filter for your mark in the second column. Select all the data and then hit Ctrl+;, which selects only visible cells. You can then copy and paste only the cells you marked. Option B is to make your mark and then sort by that column, then just copy the cells that have the mark since they would all be in one group after the sort.

...Just read AAE's post. So many ways to do anything in Excel.

Re: Sumproduct Having Blanks On Range

I would suggest replacing your ranges with named ranges using the offset formula to count non-blank cells. That way, your formula would never count more rows than necessary.

Re: VBA Excel Time/Date Question

Try =SUM((B2:B12-B1:B11>1/24)*(D2:D12)) and confirm with Ctrl+Shift+Enter. You will have to be careful with how time is entered if you use this method. Cell B12 reads AM and B9 is currently reading PM. Once these are fixed, the formula will work perfectly. Overall, I would just show the AM/PM in column B and get rid of column C altogether.

Re: Count Number Of Instances Of Dates(month) With Multiple Criteria

OK, I get it now. The easiest way is probably to put your beginning and end date in two cells (I'll use B1 and B2, respectively). The formula would be =SUM((B4:B15="DS")*(I4:I15>=B1)*(I4:I15<=B2)*1,(B4:B15="DS")*(K4:K15>=B1)*(K4:K15<=B2)*1,(B4:B15="DS")*(M4:M15>=B1)*(M4:M15<=B2)*1,(B4:B15="DS")*(O4:O15>=B1)*(O4:O15<=B2)*1) confirmed with Ctrl+Shift+Enter.

Re: Count Number Of Instances Of Dates(month) With Multiple Criteria

What exactly do you mean by check 4 ranges? Are you wanting it to count only when all four cells in a given row are filled or do you want to check that each is by a given review date, or something else?

Re: If And Formula Issues With Blanks

What do you want to happen if the first two cells match, but the third doesn't and is not blank? You can also check if Comparison!E16=0, as blank cells evaluate to that in Excel. The only reason I can think of why DaddyLongLegs' formula wouldn't work would be if the numbers in one cell are numbers and in the other, they are a text string. If this is the case, you could use the N function (comparison!E14=N(comparison!E15), for example).
If this doesn't work, would it be possible for you to save the workbook as something else, hard-code random values in (to hide the bank data) and post that?

Re: Count Number Of Instances Of Dates(month) With Multiple Criteria

I don't use sumproduct too often (though I can't imagine why it wouldn't work for this), but an array formula should be able to do what you want. If Assessor Initials is in column A, Month is in B, and the data is in I,K,M,O, (I'll assume 20 rows of data) the formula could be something like =SUM((A2:A21="DS")*(B2:B21="Mar")*(I2:I21),((A2:A21="DS")*(B2:B21="Mar")*(K2:K21),((A2:A21="DS")*(B2:B21="Mar")*(M2:M21),((A2:A21="DS")*(B2:B21="Mar")*(O2:O21)) and then confirm with Ctrl+Shift+Enter.

Re: Decimal Place Cell View (precision)

Select the cells in question and go to the format cells dialogue box, select custom, then in the box under "Type:" write 0.###############
This will show only as many decimal places as you have numbers for, but will show up to 15 places if you have that many in your numbers.

Re: Vb Code

If you're using Office 2007, you would go to the Developer tab (see the Help section in Excel if this does not appear) and click on the Macros button. From there, you can select the Macro and click Run or Options. If you click Options, a popup will appear, in which you can designate a hotkey combination (note that it is case-sensitive, so Ctrl+Shift+A is different from Ctrl+A). Generally speaking, if you use this method, it is best to use Ctrl+Shift+<letter> just because any hotkey combination you assign overrides the hotkey combination built into Office.
As another option, you can place an object (such as a box or picture) in the worksheet, right click on the object, and assign a macro. Then the macro will run whenever you click the object.

Re: Vlookup Two Lists With Common Criteria

That kind of depends on what you call easy. To adapt it to more areas, you could add the requisite number of loops to this, but it would probably be easier to write a macro to do that (would help, but I am just learning VBA).
For the error issue, that's my fault; I forgot to include error handling. Try
=IF(ISERROR(VLOOKUP($B20,$B$4:$I$6,COLUMN()-1,FALSE)),VLOOKUP($B20,$B$7:$I$9,COLUMN()-1,FALSE),IF(VLOOKUP($B20,$B$4:$I$6,COLUMN()-1,FALSE)="",VLOOKUP($B20,$B$7:$I$9,COLUMN()-1,FALSE),VLOOKUP($B20,$B$4:$I$6,COLUMN()-1,FALSE)))

Re: Vlookup Two Lists With Common Criteria

In cell C20, enter this formula:
=IF(VLOOKUP($B20,$B$4:$I$6,COLUMN()-1,FALSE)="",VLOOKUP($B20,$B$7:$I$9,COLUMN()-1,FALSE),VLOOKUP($B20,$B$4:$I$6,COLUMN()-1,FALSE))
and drag across. If you only have the above two sections (i.e., a person's name may not appear more than once in each section, twice overall), then this should work.

Re: Count &amp; Lookup Function

Combining Lookup and Count functions returns an error. I have tried to get this to work by combining Countif and Index/Match formulas, but to no avail. If you have freedom to play with the layout, I would suggest simply inserting a column after every day, putting the VLOOKUP formula in it, and then having a countif formula count the letters in that column. You could then hide these columns so the spreadsheet appears the same as it is now. Sorry I don't have a good formulaic fix.

Re: Advanced Count Lookup Function?

The only confusing thing is that you have numbers in the column for Sunday. If these are changed, the formulas become easier. In your attachment, in cell D7, you could put =COUNTIF(D$3:D$6,$B7) then drag that formula across and down to fill in your totals table.

Re: Number Series

For your first question, in C2, try =A2+B2-1 and then drag down.
Not quite sure what you're looking for in your second question. It looks like columns A and B are the same. The formula to get D is =C2-B2+1 and then drag down.