How do I...

[lee willis]

1 am making a cheat sheet for sales tax in a1 i have the sales tax. a2 -
a1001 i have $.01 to $100.00. b2-b1001 i have the formula that says what the
tax is. so for $.01 to $.07 is $.00. $.08 to $.23 is $.01. My question is
how do set it up so on a diffrent page it would pull the info so the lay out
would be (A1) $.01 (B1)- (C1)$.07 (D1)$.00 (A2)$.08 (B2)- (C2)$.23 (D2)$.01
and so on. Can anyone help?

 

[ilia]

Not the easiest task in the world. Here I'll assume your list is on
Sheet1. Then, on Sheet2, do the following:

1. Select a decent range of cells, preferably matching to the number
of tax rates you have, in column D.
2. Paste this formula in the formula bar:
=INDEX(Sheet1!$B$1:$B$1001,SMALL(IF(MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B
$1:$B$1001,0)=ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B
$1001))),MATCH(Sheet1!$B$1:$B$1001,Sheet1!$B$1:$B
$1001,0),""),ROW(INDIRECT("1:"&ROWS(Sheet1!$B$1:$B$1001)))))
3. Press Ctrl+Shift+Enter to commit. This is an array-formula, so
simply pressing Enter will not work.
4. In cell A1, type in 0.01
5. In cell A2, type in =C1+0.01 and copy down
6. In cell C1, type this formula and press Enter:
=IF(ISERROR(INDEX(Sheet1!$A$1:$A$1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B
$1001,0)-1)),MAX(Sheet1!$A$1:$A$1001),INDEX(Sheet1!$A$1:$A
$1001,MATCH(Sheet2!D2,Sheet1!$B$1:$B$1001,0)-1))
7. In cell B1, type in - and copy down.

Hope that helps.

 

[ilia]

Sorry, in column C, also copy down the formula from C1.

 

[lee willis]

close the issue is on sheet 1 E1 is 6.25% and B1 =A1*E1 which equals
..000625. so there is no match.

 

[ilia]

I don't understand. Are you saying the rates in column B of Sheet1
are coming from a formula? That shouldn't affect the above solution,
because the lookup is based on the tax rate, not the range within
which it's applicable.

 

[lee willis]

yes it is a formula in column B its 6.25% * $.01 to $100.01 so it is a
diffrent value for .01 than .02. thats why i am having issues.