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,081,441
Messages
5,358,694
Members
400,508
Latest member
fish31

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top