• 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.

Range Formula between 2 sheets in 1 workbook

J

Janelle

New Member
Sheet 1:

[pre]
Code: 
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.
 
Good Day Janelle


Post edited with back ticks to keep format


This could be done with a VLOOKUP
 
Welcome to Chandoo.Org forums.


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


i.e. Sheet1

[pre]
Code: 
$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.
 
Good day Janelle


Please have a look at the upload


https://dl.dropboxusercontent.com/u/75495784/Janelle.xlsx
 
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,
 
=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,


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


Regards,
 
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.
 
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!
 
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
 
@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
 
Hi, Janelle!

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

Regards!
 
Hi, Janelle!

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

Regards!
 
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.
 
If I wanted the totals from this worksheet to automatically be in another document, is that possible? How would I go about do that?
 
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.
 
You must log in or register to reply here.
Back
Top