A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

using a formula to create an ordered purchase order



 
 
Thread Tools Display Modes
  #1  
Old August 28th, 2008, 10:05 PM posted to microsoft.public.excel.worksheet.functions
Jesse
external usenet poster
 
Posts: 75
Default using a formula to create an ordered purchase order

I use excel to send out request for quotes to vendors and then transfer that
information into a purchase order. I send out a request for quote to several
vendors for the same products. When vendors return the request I open an
excel spreadsheet and put the prices in each vendors spreadsheet. I've
created a spreadsheet that allows me to put an "x" beside the cheapest price
and an excel formula coppies the description and the price to a different
purchase order spreadsheet. =IF('RFQ Results'!D8="X",'RFQ 1'!D18," ") The
problem arises when I purchase items from the same request for quote from
different vendors. The way the formula is written when I put an "x" beside
line items 1, 4, and 7 on the request for quote they are coppied to lines 1,
4, and 7 on the purchase order instead of lines 1, 2, and 3 on the purchase
order. I need a formula that allows me to select lines 1, 4, and 7 from the
request for quote page and places the itme in lines 1, 2, and 3 of a purchase
order sheet. Does anyone know how? Thanks
  #2  
Old August 28th, 2008, 10:10 PM posted to microsoft.public.excel.worksheet.functions
dan dungan
external usenet poster
 
Posts: 67
Default using a formula to create an ordered purchase order

you can use nested ifs
  #3  
Old August 28th, 2008, 10:16 PM posted to microsoft.public.excel.worksheet.functions
Jesse
external usenet poster
 
Posts: 75
Default using a formula to create an ordered purchase order

Can you give an example of a nested if formula that would work?

"dan dungan" wrote:

you can use nested ifs

  #4  
Old August 28th, 2008, 11:10 PM posted to microsoft.public.excel.worksheet.functions
dan dungan
external usenet poster
 
Posts: 67
Default using a formula to create an ordered purchase order

I would need more specific information, like:

Which spreadsheet holds this formula?
=IF('RFQ Results'!D8="X",'RFQ 1'!D18," ")


Show these specific formulas:

The way the formula is written when I put an "x" beside

line items 1, 4, and 7 on the request for quote they are coppied to
lines 1, 4, and 7 on the purchase order instead of lines 1, 2, and 3
on the purchase
order.
  #5  
Old August 28th, 2008, 11:54 PM posted to microsoft.public.excel.worksheet.functions
JLatham
external usenet poster
 
Posts: 3,017
Default using a formula to create an ordered purchase order

May also want to ask how many items might need to be transferred in total.
Keep in mind the limit of 7 levels of nesting in a formula. If there's going
to be a need for more testing than that, it may be required to look to a VBA
solution.

"dan dungan" wrote:

I would need more specific information, like:

Which spreadsheet holds this formula?
=IF('RFQ Results'!D8="X",'RFQ 1'!D18," ")


Show these specific formulas:

The way the formula is written when I put an "x" beside

line items 1, 4, and 7 on the request for quote they are coppied to
lines 1, 4, and 7 on the purchase order instead of lines 1, 2, and 3
on the purchase
order.

  #6  
Old August 29th, 2008, 12:02 AM posted to microsoft.public.excel.worksheet.functions
Max
external usenet poster
 
Posts: 8,574
Default using a formula to create an ordered purchase order

You might to try this sample from my free archives for some relevant ideas:

http://savefile.com/files/1267592
Auto-Ordering to Diff Vendors.xls

It's a pure formulas driven play which dishes the order lines out
automatically from a mastersheet to various vendor sheets
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,500 Files:358 Subscribers:55
xdemechanik
---
"Jesse" wrote:
Can you give an example of a nested if formula that would work?


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:39 PM.


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