• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Dynamic Index and Match

G

Governor

New Member
Good Day,
I have a spreadsheet which consists of Location Postcodes (first half), Transport Modes and Cost data in the body of the sheet, but I have multiple sheets depending on the Start/End Location (Sutton / Bedford / Swindon). If I was wanting to Index and Match from one location that wouldn't be a problem, but what like to do is have a drop down list of the Start/End location which would then return a different Cost depending on the Location Postcode.

I have attached an example sheet for reference where:
"To/From" is a drop down to select Sutton, Flitwick or Swindon.
"Site Postcode" is a drop down for a couple of test Postcodes.
"Vehicle Type" is a drop down to select just that.
"Rate" returns the value from the "Sutton" Sheet as I have set up the basic Index/Match which I can do already.

Any help is much appreciated.

Edit: I have used "IF" as a work around, but this feels a little clunky, long winded and limited.
 

Attachments

  • Example.xlsx
    15.6 KB · Views: 1
  • Example edit.xlsx
    15.7 KB · Views: 1
Last edited:
Check the formula in cell E2 of the Sheet1 sheet works correctly:
Code: 
=INDEX(INDIRECT("'" & A2 & "'!B2:I6"),MATCH(B2,INDIRECT("'" & A2 & "'!A2:A6"),0),MATCH(C2,INDIRECT("'" & A2 & "'!B1:I1"),0))
 

Attachments

  • Chandoo55592Example.xlsx
    15.6 KB · Views: 8
So I am getting to grips with using the Indirect function with Index, Match and Sum. I am wondering how to make it work with CountIf?

I have a formula with is, =CountIf(January!$M$8:$M$100, "Y") *55

I'm sure you can work it out but, from the January Tab count the "Y"'s in Column M (from cell 8 to 100) and multiply that by 55.
This formula is in a Yearly Summary table where I have the months (from January to December) in column C. So I was trying to use the following formula,

=Countif(Indirect("'"&C3&"'!$M$8:$M$100, "Y"))" *55

However this is not working at all...errors ranging from, it's just plain wrong, to missing parenthesis and too few arguments for the function.

Any help is appreciated.
 
Code: 
=COUNTIF(INDIRECT("'" & C3 & "'!M8:M100"),"Y")*55
Since the sheet names will all be single words (no spaces) you could get away with:
Code: 
=COUNTIF(INDIRECT(C3 & "!M8:M100"),"Y")*55
 
Last edited:
You must log in or register to reply here.
Back
Top