Range (Mabe) Help...

[ChuckF]

Over my head...

Here is the situation. I have a workbook, where each worksheet has
pricing data for each state. On the "state" pages the format is
the same.
1-3.9 inches 4-7.9 inches 8-11.9 inches
<2500 sq feet $$$$ $$$$ $$$$
<8000 sq feet $$$$ $$$$ $$$$
20,000-23,999 $$$$ $$$$ $$$$
24,000-27,999 $$$$ $$$$ $$$$

On a separate spreadsheet I have entered the sq feet for each location
(currently over 900 of them)

What I would like to do is have the pricing data automatically update
based on the sq footage that I have entered for each site.

I believe that I need to set up a "range" but to be honest; I have
never really played with that.

Any help would be appreciated.

Thanks so much.

 

[Dave Peterson]

When you use text labels like this: "<2500 sq feet" or "1-3.9 inches", it
becomes a pain to match up. It's difficult to do arithmetic on this kind of
free format text.

Unless... you limit the options to one of those labels--maybe using
data|validation. But I'm guessing you just have numbers in the square foot
cells.

If that's the case, I'd take some time to change the tables.

(And try this against a copy of your workbook--just in case!)

I'd make the table look more like:

0 4 8
0 1 2 3
2500 4 5 6
8000 7 8 9
24000 10 11 12
28000 13 14 15
100000 16 17 18

(as an aside, what happens between 8000 and 20000 feet?)

Now with the square footage in A1, the inches in B1, you could retrieve the
value from the IL worksheet by a formula like this:

=INDEX(IL!A:E,MATCH(A1,IL!A:A,1),MATCH(B1,IL!1:1,1))

(I only went 5 columns to the right--change that to match your tables.)

But you have the state indicator in another cell.

So with the square footage in A1, inches in B1, and state/worksheet name in C1,
I could use this formula to get the price:

=INDEX(INDIRECT("'"&C1&"'!A:e"),MATCH(A1,INDIRECT("'"&C1&"'!A:a"),1),
MATCH(B1,INDIRECT("'"&C1&"'!1:1"),1))

The formula is essentially the same, but with IL replaced with
indirect("'" & c1 & "'"

==========
If you're lucky and your tables are nice, you could group the state sheets
(click on the first worksheet tab and ctrl-click on subsequent) and change those
labels.

Each change you make to a member of those grouped sheets will be made to the
other members of the group--so be careful. So don't edit any prices!

And Ungroup the sheets when you're done (rightclick on a worksheet tab and
choose Ungroup sheets)