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

BlondieC

New Member
Joined
Feb 9, 2016
Messages
41
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
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

BlondieC

New Member
Joined
Feb 9, 2016
Messages
41
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:

Watch MrExcel Video

Forum statistics

Threads
1,122,957
Messages
5,599,051
Members
414,281
Latest member
Engjamal2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top