Using a cell reference for a range

[p_gillespie]

Hi,

I'm trying to specify a range for count if based on the value of a cell - if this is possible (i cant work it out using either type of cell reference), if not i'm looking for a way of doing this?

ie. cell E7 currently has =IF(COUNTIF(E3:E6,"No")>0,"No","Yes") but i would like the range part of it to be "current cell -1 row (E(6-1)":"current cell - "look up value in C6 (E6-4) if this makes sense, to allow me to copy the row for hundreds of dishes and it change the look up range automagically without having to use hundreds of sheets. I have tried using R1C1 but cant get it to work right.

Copy & Paste of simplified worksheet below if it benefits anyone.

Thanks

Pete
[TABLE="width: 583"]
<tbody>[TR]
[TD="align: right"]A1[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Dish Number[/TD]
[TD]Ingredient Number[/TD]
[TD]Product Description[/TD]
[TD]Suitable for a Vegan Diet[/TD]
[TD]Suitable for a Vegetarian Diet[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD]Tortilla Chips[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Salsa[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]Guacamole[/TD]
[TD]No[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]Sour Cream[/TD]
[TD]No[/TD]
[TD]YES[/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD][/TD]
[TD][/TD]
[TD]Nachos[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD]Vegie quarter pounder[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD][/TD]
[TD="align: right"]2[/TD]
[TD]Lettuce[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD][/TD]
[TD="align: right"]3[/TD]
[TD]Tomato[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD][/TD]
[TD="align: right"]4[/TD]
[TD]Chips[/TD]
[TD]Yes[/TD]
[TD]yes[/TD]
[/TR]
[TR]
[TD="align: right"]13[/TD]
[TD][/TD]
[TD="align: right"]5[/TD]
[TD]red onion[/TD]
[TD]Yes[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD="align: right"]6[/TD]
[TD]Burger Bun[/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD="align: right"]14[/TD]
[TD][/TD]
[TD][/TD]
[TD]Burger [/TD]
[TD]No[/TD]
[TD]Yes[/TD]
[/TR]
</tbody><colgroup><col span="3"><col><col span="2"></colgroup>[/TABLE]

 

[skywriter]

Here is a custom function if you care to use it. There is one for vegans and one for vegetarians. You simply insert the appropriate function into your spreadsheet cell and choose the last ingredient cell and then you can copy/paste the formula.

Sheet1

*	B	C	D	E	F
Dish Number	Ingredient Number	Product Description	Suitable for a Vegan Diet	Suitable for a Vegetarian Diet
Tortilla Chips	No	Yes
*	Salsa	Yes	Yes
*	Guacamole	No	Yes
*	Sour Cream	No	Yes
*	*	Nachos	No	Yes
*	*	*	*	*
Vegie quarter pounder	Yes	Yes
*	Lettuce	Yes	Yes
*	Tomato	Yes	Yes
*	Chips	Yes	yes
*	red onion	Yes	Yes
*	Burger Bun	No	Yes
*	*	Burger	No	Yes
*	*	*	*	*

<tbody>
[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]5[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]6[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula E7 =SuitableForVegans(C6) F7 =SuitableForVegetarians(C6) E15 =SuitableForVegans(C14) F15 =SuitableForVegetarians(C14) <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Instructions for Installing Function Code
1) Make sure your worksheet is saved as .xlsm file (2007 and later versions).
2) Enable macros.
3) Press Alt + F11
4) Choose - Insert - Module
5) Paste the code where the cursor is flashing.
6) Close the window.
7) The function is now ready to be used.
8) Choose fx next to the formula window or Shift + F3
and look under user defined in the select category box.

Option Explicit

Function SuitableForVegans(LastIngredientCell As Range)

Dim lngLastIngredientNumber As Long
Dim rngCountIfRange As Range
Dim strSuitableForVegans As String

Application.Volatile

lngLastIngredientNumber = LastIngredientCell.Value

Set rngCountIfRange = LastIngredientCell.Offset(-lngLastIngredientNumber + 1, 2).Resize(lngLastIngredientNumber, 1)

If WorksheetFunction.CountIf(rngCountIfRange, "No") > 0 Then
strSuitableForVegans = "No"
Else
strSuitableForVegans = "Yes"
End If

SuitableForVegans = strSuitableForVegans


End Function

Function SuitableForVegetarians(LastIngredientCell As Range)

Dim lngLastIngredientNumber As Long
Dim rngCountIfRange As Range
Dim strSuitableForVegetarians As String

Application.Volatile

lngLastIngredientNumber = LastIngredientCell.Value

Set rngCountIfRange = LastIngredientCell.Offset(-lngLastIngredientNumber + 1, 3).Resize(lngLastIngredientNumber, 1)

If WorksheetFunction.CountIf(rngCountIfRange, "No") > 0 Then
strSuitableForVegetarians = "No"
Else
strSuitableForVegetarians = "Yes"
End If

SuitableForVegetarians = strSuitableForVegetarians


End Function