Formula for cell value

[akamax_power]

Hi all. I'm trying to rework our bidding program we use in excel. What's the easiest way for a cell to return a value based on 3 different validations. Here's what i would like it to do: On the 'Excavate' sheet, If the line 'Type' = EQ then it needs to go to the 'Equipment' sheet find the equipment based on the 'Item Description' (which is pulled from a list based on the equipment sheet) then pick the rate from the rate columns on 'Equipment' based on the rate selected on the main 'Project Summary'. And it needs to do the same thing if the 'Type' is 'LA' or 'MA' and go to the 'Labor' or 'Material' sheet finding the correct rate. I put a formula in the first rate cell on the 'Excavate' sheet but its a static formula it doesn't reflect any change i make to the line.

Any help would be great, here's the file i'm working on.

[gwkenny]

On your Excavate sheet, use this formula in H11

=OFFSET(INDIRECT(C11),MATCH(D11,INDIRECT(C11),0)-1,'Project Summary'!$D$6,1,1)

Copy this formula down.

You are done.

FYI: In my opinion merged cells are a bad idea in general. They have their uses if you are making the screen act like a "form", but in a general spreadsheet it's generally a bad idea. That's just my viewpoint.

[mikerickson]

I selected Excavate!C11 and added dependent named ranges

Name: TypeDescriptions
RefersTo: =CHOOSE(MATCH(Excavate!$C11,Type,0)-1,EQ,LA,MA)

Name: TypeDataBase
RefersTo: =OFFSET(TypeDescriptions,0,0,ROWS(TypeDescriptions),COUNTA(TypeRow))

Name: TypeRow
RefersTo: =OFFSET(TypeDescriptions,0,1-COLUMN(TypeDescriptions),1,256)

Name: TypeUnits
RefersTo: =OFFSET(TypeDescriptions,-1,1,1,COLUMNS(TypeDataBase)-2)
the 2 should be changed to 1 if "Insert New Rate Template" is removed from that merged cell.

Sheet Excavate is set out with

Column D (Item Description) has list validation set to =TypeDescriptions

Column E (Units) has list validation set to =TypeUnits

H11 (Rate) has the formula
=IF(ISERROR(TypeUnits),0, INDEX(TypeDataBase, MATCH(D11,TypeDescriptions,0)-1, MATCH(E11,TypeUnits,0)))

And the formula in I11 (Amount) was changed to =F11*H11.

I hope this helps.

[gwkenny]

Mike:

By making assumptions, and trying to provide more than what was requested, I believe you provided an inappropriate solution this time.

I do not believe Excavate Column E "Units" means which rate template to use. Especially as the OP indicated that you:

"pick the rate from the rate columns on 'Equipment' based on the rate selected on the main 'Project Summary'"

Thus everything listed in Excavate is going to utilize the same rate template and it makes absolutely no sense to list the same rate template down Column E.

Excavate titles are not really clear though. If you read one entry across, I'm assuming it means something like, 2 Units of 825 Compactor @ 5 (quantity) hours each at a rate of $91.00 gives you an amount of $910.00.

Just guess though :(

*****************

FYI Mike: The upload you provided "BidA" gives an error message when opened in XL 2003 or 2007 under Windows OS on my machines: The following data may have been lost: Table.

*****************

OP: Try the formula I cited in my first post in this thread. Should do everything you requested.

Good luck!

[mikerickson]

I didn't explore the workbook enough. The I interpretation posted had the user making that choice line for line, but the OP is choosing it once for the whole sheet.

Your INDIRECT solution is not only more consice than mine, it also matches what the OP needs.

[akamax_power]

gwkenny, thanks for the formula. That's exactly what i wanted. Sorry if the columns werent clear but that's the way it's supposed to work
"2 Units of 825 Compactor @ 5 (quantity) hours each at a rate of $91.00 gives you an amount of $910.00."



Thanks again.

Gerald

[gwkenny]

Gerald:

H11:

=IF(ISERROR(INDIRECT(C11)),0,OFFSET(INDIRECT(C11),MATCH(D11,INDIRECT(C11),0 )-1,'Project Summary'!$D$6,1,1))

Copy down.

Thus, if the "TYPE" column does not contain a named range the returned result would be zero.

Good luck!

[akamax_power]

That's great! sorry if i'm pestering but sometimes the rate could get changed manually, which i want to be able to do. I don't want to lock out the cell. But I'd like to be able to double click the cell to have it set back to the original formula. I've started a VBA formula to double click but I'm not sure how to relabel the formula to get it to match the cell row values of the cell that's double clicked.

Thanks

[gwkenny]

Hmm, I don't recommend you have the cell re-insert the formula on a double click event. Much better to put a button on the sheet or assign it with application.onkey etc....

Too easy to double click and make a mistake.

Should also surround it with some code making sure it's in an appropriate cell (only one cell highlighted. It's in Column H, etc...).

Here's the code:

Sub aaa()
Dim sng_Row As Single
sng_Row = Selection.Row
Selection.Formula = "=IF(ISERROR(INDIRECT(C" & sng_Row & ")),0,OFFSET(INDIRECT(C" & sng_Row & "),MATCH(D" & sng_Row & ",INDIRECT(C" & sng_Row & "),0 )-1,'Project Summary'!$D$6,1,1))"

End Sub

So short, didn't even bother putting in the code tags.

Good luck!

[akamax_power]

Ok so here's my code:

Sub CtrlAltF(ByVal Target As Range, Cancel As Boolean)
Const WS_RANGE As String = "H11:H25235"
Dim sng_Row As Single
sng_Row = Selection.Row

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target

Selection.Formula = "=IF(ISERROR(INDIRECT(C" & sng_Row & ")),0,OFFSET(INDIRECT(C" & sng_Row & "),MATCH(D" & sng_Row & ",INDIRECT(C" & sng_Row & "),0 )-1,'Project Summary'!$D$6,1,1))"
End With
End If
End Sub


How do i get this to work with the onkey command? I want the command to run on Ctrl+Alt+F

[gwkenny]

Put this code in your THISWORKBOOK in your VBAProject for this file:

Private Sub Workbook_Open()
Application.OnKey "^%f", "CtrlAltF"
End Sub

Anytime the control-shift-f is pressed, "CtrlAltF" will fire. Guess you'll have to test for the sheet name too :(

You can probably get away without adding a test for the workbook name :)

The help on ONKEY in the help files isn't too bad, worth reading to round out your understanding of ONKEY.

Impressed with your diligence in creating a working product.

[akamax_power]

Ok, I've been messing around with dynamic lists. I've got the validations working but the formula for the rate cell doesn't work. I'm not sure how to rewrite it.

Validation for the "Item Description" column instead of being "=INDIRECT(C14)" is now "=OFFSET(INDIRECT($C14),1,0,COUNTA(INDIRECT(C14&"Col")),1)"

so the formula for "Rate" - "=IF(ISERROR(INDIRECT(C14)),"",OFFSET(INDIRECT(C14),MATCH(D14,INDIRECT(C14),0 )-1,'Project Summary'!$D$6,1,1))"
errors out now

UPDATE: So I figured out the formula for the cell value:

=IF(ISERROR(INDIRECT($C28)),"",OFFSET(OFFSET(INDIRECT($C28),1,0,COUNTA(INDIRECT(C28&"Col")),1),MATCH(D28,OFFSET(INDIRECT($C28),1,0,COUNTA(INDIRECT(C28&"Col")),1),0)-1,'Project Summary'!$D$6,1,1))

I'm not sure if this is the simplest way to do it but it's what I came up with. My problem now is the vba code i have to double click the cell and insert the above code if the formula gets removed. It has something to do with the quotations.

Thanks