Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 13
Default Adding Inventory with items on order

I am trying to total numbers from two different cells. Cells A1:B10 are a
part number and quantity. This gives me inventory totals. Right now I have
the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which
works fine, but I have a list of items that I have on order and their
quantities in cells E1:F10. What I want to do is to say if A1 appears in the
array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get
back a "#n/a" answer in my cell if the value or part number in my a column
does not appear in my E column. This is my current equation which works if I
have the part number in the E column, but gives "#n/a" if I don't:

=VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1

If I don't have the number listed on my order list I want the cell to equal
'Sheet2'!B1 and if that equals zero, then I want the cell to be blank.

I'm not sure if I need to do a Search function for A1 and use a boolean or
if the vlookup is the proper function.

Any advice would be much appreciated. Thanks.
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 9,101
Default Adding Inventory with items on order


=if(isna(VLOOKUP(A1,$E$1:$F$10,2)),0,VLOOKUP(A1,$E $1:$F$10,2))+'Sheet2'!B1

"Soulscream" wrote:

I am trying to total numbers from two different cells. Cells A1:B10 are a
part number and quantity. This gives me inventory totals. Right now I have
the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which
works fine, but I have a list of items that I have on order and their
quantities in cells E1:F10. What I want to do is to say if A1 appears in the
array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get
back a "#n/a" answer in my cell if the value or part number in my a column
does not appear in my E column. This is my current equation which works if I
have the part number in the E column, but gives "#n/a" if I don't:

=VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1

If I don't have the number listed on my order list I want the cell to equal
'Sheet2'!B1 and if that equals zero, then I want the cell to be blank.

I'm not sure if I need to do a Search function for A1 and use a boolean or
if the vlookup is the proper function.

Any advice would be much appreciated. Thanks.

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max Max is offline
external usenet poster
 
Posts: 9,221
Default Adding Inventory with items on order

One guess

In Sheet1,
Try in G1:
=IF(E1="","",IF(COUNTIF(A:A,E1),SUM(F1,Sheet2!B1), IF(Sheet2!B1="","",Sheet2!B1)))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Soulscream" wrote:
I am trying to total numbers from two different cells. Cells A1:B10 are a
part number and quantity. This gives me inventory totals. Right now I have
the B1:B10 cells setup to pull the numbers from 'Sheet2'!B2 - B10, which
works fine, but I have a list of items that I have on order and their
quantities in cells E1:F10. What I want to do is to say if A1 appears in the
array E1:F10 then to add F1 and 'Sheet2'!B1, but when I do my formula I get
back a "#n/a" answer in my cell if the value or part number in my a column
does not appear in my E column. This is my current equation which works if I
have the part number in the E column, but gives "#n/a" if I don't:

=VLOOKUP(A1,$E$1:$F$10,2)+'Sheet2'!B1

If I don't have the number listed on my order list I want the cell to equal
'Sheet2'!B1 and if that equals zero, then I want the cell to be blank.

I'm not sure if I need to do a Search function for A1 and use a boolean or
if the vlookup is the proper function.

Any advice would be much appreciated. Thanks.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Order form linked to inventory Lella Excel Discussion (Misc queries) 3 October 10th 07 01:08 PM
How do I set up an inventory control worksheet to show items left Leonard Setting up and Configuration of Excel 1 July 26th 07 05:52 PM
Tracking inventory order history rjez Excel Discussion (Misc queries) 1 July 13th 06 12:36 PM
order form with multiple items and sizes for items Bernard56 Excel Discussion (Misc queries) 1 January 18th 06 12:43 AM
where can I find a sample home inventory that list all items i ma. lanier Excel Discussion (Misc queries) 2 December 28th 04 10:54 AM


All times are GMT +1. The time now is 10:15 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"