Using Min Max need to further identify odd even values to go in specific cells

BlondieC

New Member
Joined
Feb 9, 2016
Messages
39
Starting with this code provided by a helpful person on here. WorksheetA and column A will be constant. I think I need a dynamic reference where WorksheetB is as there will be multiple worksheets that I want to pull the values from and the range on each of the worksheets will be the same columns, but the rows will change.

Code:
[I]=MAX(IF('WorksheetA'!$A$2:$A=$A2,'WorksheetB'!$B$2:$B$14))[/I]
Background:
-Workbook with 6 standard worksheets and additional multiple worksheets that will vary workbook to workbook.

The workbook functions as follows:
-WorkingData worksheet contains address information, a combo of <acronym>VBA</acronym> and macros are available and one of the results produces a concatenated list that contains the street name and street type ie Albany St.
-StreetRangeData worksheet has a combo of <acronym>VBA</acronym> and macros available - macro1 (for simplicity) copies/pastes the concatenated street name list from WorkingData worksheet into StreetRangeData worksheet A2:A. Macro 2 reads through the new list in A2:A and produces a new worksheet for each street name in the list and gives the new worksheet that street name. For each street name in the list I now have a worksheet by that name that is based on a template worksheet so the contents and functionality are all the same and the names in the list are now hyperlinked to the matching worksheet.
-Albany St worksheet and all the other street named worksheets have a command button that grabs all data from specific columns on the WorkingData worksheet and pastes it in the specified location of the street named worksheet. So Albany St worksheet will only have info for Albany St and Andrew St worksheet will only have info for Andrew St etc.

This brings me to where the code I'm starting with needs further modification.

What I would like to do in each individual street named worksheet, is look at the civic address number data value and return the Max and Min for the even range and the Max and Min for the odd range (4 number values total), to the StreetRangeData worksheet and paste those values in columns B, C, D, E which are all to the right of column A which is holding the street name.


Thank you
 

BlondieC

New Member
Joined
Feb 9, 2016
Messages
39
I found a very helpful thread with a formula (from Snakehips), that address part of what I'm trying to do with my workbook. MAX ISODD and MAX ISEVEN both work. However, when I change it to MIN the value return in the cell is 0.

I read a lot more threads on a lot more sites and tried the various suggestions without any luck. I would like to use this for MIN ISODD and MIN ISEVEN - if anyone has a fix please.

Also, is there a way to dynamically reference the sheet name ie Albany St as opposed to typing it in? I have a list of the street names that I would like to have inserted into the appropriate position.

Formula - CSE array:
Code:
{=MAX((ISODD(IF('ALBANY ST'!$G$3:$G$90="",0,RIGHT('ALBANY ST'!$G$3:$G$90,LEN('ALBANY ST'!$G$3:$G$90)))))*IF('ALBANY ST'!$G$3:$G$90="",0,RIGHT('ALBANY ST'!$G$3:$G$90,LEN('ALBANY ST'!$G$3:$G$90))))}
Thank you
 
Last edited:

Forum statistics

Threads
1,086,233
Messages
5,388,607
Members
402,126
Latest member
kalcerro_1

Some videos you may like

This Week's Hot Topics

Top