Excel formula help

[Zico]

I'm trying to tweak a workbook that has several sheets (Scaffold, Hoarding etc.). On one of the sheets I've made a list that I can then refer to in a drop down list in the other sheets.
=CompanyName on the Company Details sheet

One of the sheets (Credit Breakdown) is setup for working out the costs for each of the other sheets. I have the following formula that currently counts the number of times a company name appears in a range on the other sheets and then multiplies that by a fixed cell (to give £ amount):
=COUNTIF(SCAFFOLD!$B$1:$B$36047,"=ADH Scaffolding")*$H$3

The Credit Breakdown sheet has multiple copies of the above formula setup with different company names but, when a new company name needs to be added, this means that the formula is having to be copied and manually edited in another cell.

I'm trying to figure out how to change the Credit Breakdown formula above to refer to a cell in which a user can simply choose the company from the CompanyName list (that way they can't mess up the formula while trying to manually edit it).

I hope this makes sense. Any help is greatly appreciated!

 

[Meeko]

I assume =CompanyName is a named formula based on the list on the Company Details worksheet?

If so, it should be as simple as amending the "=ADH Scaffolding" in your countif function to a fixed cell reference, say $A$1. Then in cell A1, set up data validation to be a list where values =CompanyName.

That will give you a dropdown list of the companies that can be selected.

 

[Zico]

Evening Meeko.

CompanyName is the source name I gave the list (shouldn't have included the = at the start, sorry forgot to take off = as it's needed under the data validation).

I did try setting up a cell under the Credit Breakdown sheet with data validation, but when putting the fixed cell into the formula it does nothing:
=COUNTIFS(SCAFFOLD!$B$1:$B$36047,"=$B$5")*$H$3

I realise that the above section in green is wrong and that it's now looking for the text $B$5 instead of say ADH Scaffolding.

 

[Zico]

Just changed the formula to:
=COUNTIF(SCAFFOLD!B10:B2523,$B$5)*H$3

The reference to H3 or H$3 works, but if I try and make it fixed to H$3$ it doesn't like the formula.

**Edit
I'm being dumb (and tired ). Should be putting the $ at the front of the H and 3

**Edit2
The formula is now working:
=COUNTIF(SCAFFOLD!$B$10:$B$3000,$B$5)*$H$3

 

[Grrrrr]

The reference to H3 or H$3 works, but if I try and make it fixed to H$3$ it doesn't like the formula.
**Edit
I'm being dumb (and tired ). Should be putting the $ at the front of the H and 3

Pro tip: You don't need to type the dollar signs in manually, just press F4 to toggle through the various dollar combinations. (the cursor should be on the cell reference you want to change)