Any Excel Pros?

GOTMH8N · 06-06-2013, 07:22 AM

I'm trying to find an excel function that will take data from one sheet and place it into another when there are values in those cells.

Basically, there is a general parts pricing estimating sheet that is huge and you have to sweep through the whole thing to find which cells have values. Is there a function that will search the page and bring up only the cells with values?

KingOfJericho · 06-06-2013, 08:39 AM

I'm positive that I could help but I'm having a hard time understanding what you're trying to do.

M3 6MT · 06-06-2013, 09:55 AM

You can use the function =len()
=len() counts the # of characters in a cell...if there are no values, it will be return 0.
You can filter to not include 0. Let me know if this helps.

BMW F22 · 06-06-2013, 10:19 AM

Look up vlookup function. I think that's what you're looking for.

elm3 · 06-06-2013, 10:22 AM

You can also use = CONCATENATE ()

Edit: the more I think about it, vlookup would be better. The above is more to put together two different values into one cell.

GOTMH8N · 06-06-2013, 10:44 AM

Quote:

Originally Posted by KingOfJericho

I'm positive that I could help but I'm having a hard time understanding what you're trying to do.

I'm sorry, it's kind of hard to spit out but I think I may be on the right track with VLOOKUP().

What I need to do it is create a sheet that will list the quantity of the products used for a specific job. If say, products A-Z are available for a job but only products A,B,D,G,T,Z were used. VLOOKUP() would search through products A-Z and show all the quantities and list everything that was not used as 0 and list the quantities for A,B,D,G,T,Z. This sound correct?

GOTMH8N · 06-06-2013, 11:00 AM

ok here is a generic screen shot of what I'm working on. Sheet 1 is the sheet from the job and Sheet 2 is the one I'm making. I used VLOOKUP() to auto fill Sheet 2's quantities for the products reflected from Sheet 1.

Red Bread · 06-06-2013, 11:42 AM

Use FALSE in the vlookup to make sure you only get exact matches. You can use an ISERROR to fill 0 or "" if the vlookup comes back with an error or blanks.

M3 6MT · 06-06-2013, 12:26 PM

you can also create a pivot table

pokerface · 06-06-2013, 11:22 PM

both SUMIF and VLOOKUP should work for your purposes.

GOTMH8N · 06-07-2013, 07:20 AM

thanks for the help guys! VLOOKUP seems to work perfectly for what I need to do

Bulldog_335xi · 06-07-2013, 02:46 PM

Use index and match functions. Next step from vlookup...

06-06-2013, 07:22 AM	#1
GOTMH8N Stay Classy 76 Rep 1,021 Posts Drives: 335i Sport Join Date: May 2012 Location: c iTrader: (2)	Any Excel Pros? I'm trying to find an excel function that will take data from one sheet and place it into another when there are values in those cells. Basically, there is a general parts pricing estimating sheet that is huge and you have to sweep through the whole thing to find which cells have values. Is there a function that will search the page and bring up only the cells with values?
	Appreciate 0 Tweet Quote

06-06-2013, 08:39 AM	#2
KingOfJericho Major General 2458 Rep 7,341 Posts Drives: Yes Join Date: Aug 2007 Location: CT iTrader: (1) Garage List 2010 135i Coupe [5.26]	I'm positive that I could help but I'm having a hard time understanding what you're trying to do. __________________ The views and opinions expressed in this post are those of the author and do not necessarily reflect the official policy or position of Bimmerpost. 2018 Jeep Grand Cherokee High Altitude Hemi \| 2010 S4 Sold \| 2010 BMW 135i Retired \| 2006 Lotus Exige Sold
	Appreciate 0 Quote

06-06-2013, 09:55 AM	#3
M3 6MT Get to the chopppper! 11 Rep 121 Posts Drives: 2011.5 E93 Join Date: May 2010 Location: New York City iTrader: (5)	You can use the function =len() =len() counts the # of characters in a cell...if there are no values, it will be return 0. You can filter to not include 0. Let me know if this helps.
	Appreciate 0 Quote

06-06-2013, 10:19 AM	#4
BMW F22 Major General 3659 Rep 9,783 Posts Drives: ///M235i Join Date: Nov 2005 Location: Bay Area iTrader: (8)	Look up vlookup function. I think that's what you're looking for.
	Appreciate 0 Quote

06-06-2013, 10:22 AM	#5
elm3 ///M Fan 367 Rep 2,159 Posts Drives: '09 E92 M3 IB/'17 f85 X5M LBB Join Date: Sep 2008 Location: Canton, GA. iTrader: (1) Garage List 2017 BMW / X5M [9.00] 2009 E92 M3 [10.00] 2004 Yamaha R1 [10.00]	You can also use = CONCATENATE () Edit: the more I think about it, vlookup would be better. The above is more to put together two different values into one cell. __________________ '09 E92 ///M3 Interlagos Blue,6MT,Silver Novillo Leather,CF Trim,Premium Package,Technology Package,Cold Weather Package,Premium Sound,19"Wheels,Moonroof-Toy/'17 X5M, Long Beach Blue, Executive Package,Technology Package, Lighting Package, Driver Assist, Full Mughello Red Interior, 21" wheels-Toy/'14 535i with M Sport and 19" wheels-Daily Last edited by elm3; 06-06-2013 at 10:28 AM..
	Appreciate 0 Quote

06-06-2013, 11:00 AM	#7
GOTMH8N Stay Classy 76 Rep 1,021 Posts Drives: 335i Sport Join Date: May 2012 Location: c iTrader: (2)	ok here is a generic screen shot of what I'm working on. Sheet 1 is the sheet from the job and Sheet 2 is the one I'm making. I used VLOOKUP() to auto fill Sheet 2's quantities for the products reflected from Sheet 1. Attached Images
	Appreciate 0 Quote

06-06-2013, 11:42 AM	#8
Red Bread Major General 4458 Rep 9,160 Posts Drives: Smog machines Join Date: Oct 2008 Location: Austin, TX iTrader: (0)	Use FALSE in the vlookup to make sure you only get exact matches. You can use an ISERROR to fill 0 or "" if the vlookup comes back with an error or blanks.
	Appreciate 0 Quote

06-06-2013, 12:26 PM	#9
M3 6MT Get to the chopppper! 11 Rep 121 Posts Drives: 2011.5 E93 Join Date: May 2010 Location: New York City iTrader: (5)	you can also create a pivot table
	Appreciate 0 Quote

06-06-2013, 11:22 PM	#10
pokerface Colonel 443 Rep 2,531 Posts Drives: F25 X3 N20 / F80 ZCP Join Date: Apr 2006 Location: Toronto iTrader: (1)	both SUMIF and VLOOKUP should work for your purposes.
	Appreciate 0 Quote

06-07-2013, 07:20 AM	#11
GOTMH8N Stay Classy 76 Rep 1,021 Posts Drives: 335i Sport Join Date: May 2012 Location: c iTrader: (2)	thanks for the help guys! VLOOKUP seems to work perfectly for what I need to do
	Appreciate 0 Quote

06-07-2013, 02:46 PM	#12
Bulldog_335xi Speed Enthusiast 2 Rep 25 Posts Drives: 2011 e92 335xi Join Date: Oct 2012 Location: Switzerland iTrader: (0)	Use index and match functions. Next step from vlookup...
	Appreciate 0 Quote