Range Formula between 2 sheets in 1 workbook

[Janelle]

Sheet 1:

[pre]

GROSS SERVICE SALES	ALLOWANCE
$0 - $12,000 	         $50.00
$12,001 - $16,000	 $65.00
$16,001 - $20,000	 $80.00
$20,001 - $24,000	 $85.00
$24,001 - $28,000	 $110.00
$28,001 - $32,000	 $125.00
$32,001 + 	         $140.00
Sheet 2:

Salon	Sales	         Cost	    Allowance Total
153	$15,275.60	$194.62	$?	$259.62
160	$23,285.60	$217.30	$?	$302.30
166	$12,774.60	$133.57	$?	$198.57

[/pre]
I would like the allowance (on sheet 2) to populate based on the sales (on sheet 2).

So sheet 1 is the table of what the allowance should be if the sales falls between specific numbers.

 

[bobhc]

Good Day Janelle


Post edited with back ticks to keep format


This could be done with a VLOOKUP

 

[shrivallabha]

Welcome to Chandorg forums.


You can use VLOOKUP here easily if the range is split in two separate columns


i.e. Sheet1

[pre]

$0 	        $12,000 	$50.00
$12,001 	$16,000 	$65.00
$16,001 	$20,000 	$80.00
$20,001 	$24,000 	$85.00
$24,001 	$28,000 	$110.00
$28,001 	$32,000 	$125.00
$32,001 		        $140.00

[/pre]
Then in cell D2 in Sheet2 you can put following formula:

=VLOOKUP(B2,Sheet1!$A$2:$C$8,3,TRUE)

and then copy it down.

 

[bobhc]

Good day Janelle


Please have a look at the upload


https://dl.dropboxusercontent.com/u/75495784/Janelle.xlsx

 

[Janelle]

What does my cell have to formatted to? Something seems to be wrong with my formatting.

 

[Faseeh]

Janelle,


If you are talking about the blue triangle on the top left corner of the cells, then just select the entire column and convert the range to numbers. otherwise please post the data that appears wrong to you.


Regards,

 

[Janelle]

=VLOOKUP(Sheet2!B2,Sheet1!$A$2:$B$8,2,TRUE)

I'm using this.

@bobhc - i used your sheet and most cells have the green triangle on the top left saying "Number stored as text" When I enter a new number I get #N/A in the cell thats suppose to have the allowance value.

 

[Janelle]

not sure if this makes a difference, but sheet2 has 16 salons on it.

 

[Faseeh]

@Janelle,


You need to adjust the lookup table according to your data. What error are you getting?


Regards,

 

[Janelle]

I haven't changed anything in the lookup table that's stayed the same. If you open https://dl.dropboxusercontent.com/u/75495784/Janelle.xlsx you'll see what I mean. I cant change any sales numbers without an error.

 

[SirJB7]

Hi, Janelle!

Just passing by and giving a quick look to the file of your last link. Cells are formatted as General but with string contents, am I wrong? They should be numeric with currency sign if needed. Have you considered this? I haven't read the whole topic but surely it's making VLOOKUP function to don't work properly.

Regards!

 

[Janelle]

@SirJB7 the number is stored as text, when I click to convert it to a number my allowance doesnt calculate.

 

[SirJB7]

Hi, Janelle!


Give a look at this file:

https://dl.dropboxusercontent.com/u/60558749/Range%20Formula%20between%202%20sheets%20in%201%20workbook%20-%20Janelle%20%28for%20Janelle%20at%20chandoo.org%29.xlsx


I just changed the format to currency with $ sign, and modified your "from-to" table to "from" for use with VLOOKUP.


Regards!


EDITED: "from" by "to" in last occurrence

 

[Janelle]

@SirJB7 that allows me to add/edit, but the allowance is coming up wrong..


Salon Sales Cost Allowance

153 $ 15,275.60 $ 194.62 $ 50.00


The allowance on that should be 65, not 50

 

[SirJB7]

Hi, Janelle!

Please download again the updated file from same previous link. A shifted range within the scale.

Regards!

 

[Janelle]

That works perfectly! Thanks!

 

[SirJB7]

Hi, Janelle!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!

 

[bobhc]

Hi Janelle


Just returned from the much need cupa. Looks like SirJB7 has sorted your needs, I am sure you have already sorted out the final column but if not just download be original upload to see.

 

[Janelle]

If I wanted the totals from this worksheet to automatically be in another document, is that possible? How would I go about do that?

 

[bobhc]

Hi Janelle


If you select the contents of one to the required cell in your new worksheet and enter the = sign in the cell then switch to your workbook/work sheet with the data in and click on the cell with the data and then press enter that will copy the contents. If you do this in the top most cells you will be able the drag fill handles down.

 

[Janelle]

that will automatically update everything every time something is changed??

 

[Janelle]

Works like a charm! Thanks

 

[SirJB7]

Hi, Janelle!

If I were you I wouldn't dare to doubt about b(ut)ob(ut)hc.

Regards!

 

[bobhc]

Hi my old friend SirJB7


You know I bow to your Excel knowledge.

 

[SirJB7]

@b(ut)ob(ut)hc

Hi, old dog!

Good afternoon for you, my much older friend.



Regards!