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,084,753
Messages
5,379,654
Members
401,620
Latest member
Ankur Teotia

Some videos you may like

This Week's Hot Topics

  • VBA code giving errors and stopping Excel
    Hello Experts, I have this code being used to loop through files in a file path, and copy specific data to another sheet. It is giving me several...
  • Disable MsgBox message
    Morning, I have a userform where if i leave a ComboBox empty i see a MsgBox warning me that i must enter an invoice number. It is this MsgBox i...
  • Macro Recorder into VBA, Copy Paste Data Filled Cells
    Hi Everyone, I have a macro recorder file that takes a selection of data, copies, then pastes into a new sheet on ("A2:B2") The issue is my...
  • Number format changes while pasting into a cell
    Hi, I am trying to paste a number 180204524303 from an email to an excel cell, however, whenever i try to do so , the the paste value appears as...
  • Collating data
    Hello all. Could someone please help. I am trying to pull all column data from multiple sheets (24 I total so far) into 1 master sheet without...
  • Sum Multiple Columns Based on Multiple Criteria
    I am trying to consolidate data by summing columns G through M based on material, plant, vendor, and fiscal year being identical. The period does...
Top