Certain numbers =S

  • Thread starter Thread starter Frank
  • Start date Start date
F

Frank

I have a form that is used to calculate a products price. One of the cells
is "S" foer stock and "NS" for non stock.
#'s 32, 56, 87, 45 and about 5 others are "S" . Abouit 10 sets of numbers
are "NS" It would help greatly if excel could fill in the cell with the
proper response by inputing the #'s in say cell a1. Hope I made this clear
Thanks
Frank
 
If I interpret your requirement correctly, only you can know whether or not
something is in stock and insert the appropriate flag. If you then want to
use this flag in you price list you could do something like -

A B C D E
F
1 Product Price MarkUp Sell price S(tatus) Price
2 Widgets 20.00 1.1 =b2*c2 S
=IF(E2="S",D2,"POA")

Columns A through C and E is your input area (S=in stock)
Column D calculates the selling price
Column F returns the selling price if in stock or POA (price on asking or
any other message) if not

Columns B through E need to be hidden to produce the final price list
without revealing sensitive information.

Regards.

Bill Ridgeway
Computer Solutions
 
You question is really not too clear.

I think you're asking for a quick way to see if an item is in stock by
entering it's product code in A1, and displaying it's current position in
say B1.

If that's the case, a simple Vlookup formula in B1 would work.

With your datalist in A5 to G100, where the product code is in Column A, and
the stock position in Column F,
Try this formula in B1:

=VLOOKUP(A1,A5:G100,6,0)
 
I have a form that is used to calculate a products price. One of the cells
is "S" foer stock and "NS" for non stock.
#'s 32, 56, 87, 45 and about 5 others are "S" . Abouit 10 sets of numbers
are "NS" It would help greatly if excel could fill in the cell with the
proper response by inputing the #'s in say cell a1. Hope I made this clear
Thanks
Frank
Click to expand...

Perhaps something like:

=IF(OR(A1={32,56,87,14}),"S","NS")

or, if you need to specify the NS items, then:

=IF(OR(A1={32,56,87,14}),"S",IF(OR(A1={1,2,3,4,5}),"NS","???"))



--ron
 
Well, as suspected by me, I was not very clear. The products are poles and
some dimensions are stock i.e.. 87" and 42" and 97", so on, about 10 items
exist like this. So when this is input as the size needed, there is a cell
that is marked either "S" (meaning it's a stock size) or"NS" meaning it's
not a stock size. The price changes depending on this. I have all the
calculations for the invoice already, but when the secretary, or whoever is
filling in the form it would be great if when she entered one of the 10
stock sizes the cell for Stock or non stock item would be filled in also
with an S or NS.I think maybe that is clearer. I know how to put criteria in
an access query, but am unfamiliar with the same function in excel.Office
2000 Pro, I should have mentioned that. So I need something like if A1 = (87
or 54 or 96 or 90 or 42 etc than cell A7 = S (for stock) and then also the
opposite of that to produce an input of "NS" for not one of the stock items.
The responses were great, but my question was bad and unclear. Hopefully I
have cleared it up a bit.
At any rate, thanks so much for the replies.
Thanks
Frank
 
So I need something like if A1 = (87
or 54 or 96 or 90 or 42 etc than cell A7 = S (for stock) and then also the
opposite of that to produce an input of "NS" for not one of the stock items.
The responses were great, but my question was bad and unclear. Hopefully I
have cleared it up a bit.
Click to expand...

In that case, my second formula should be easily modified to do what you are
requesting.

Post back if that formula is not clear.


--ron
 
So ... it would appear that the Vlookup function would fit the bill, if you
just entered it into the appropriate cell.

Also, any revisions to the "S" and "NS" column would be automatically
included in the "search".
 
Hey Ron,
Yes, your second formula works for the stock numbers. Only thing now is I
need like a "If not one of these numbers then NS" There are only 10 stock
numbers, everything else is Non Stock "NS" If you have time to tell me
great, I will do my best to figure it out. Ron, believe it or not I am a MS
Certified tech, but windows is my bag MCDST Charter Member. Some network
stuff also. But this is a bit foreign to me. Am I correct in assuming I
would need to really study up on SQL to get a grasp on some of the stuff I
have been asking help for? I am really interested, but I feel it's a bit
late and time consuming (I am a musician also) to try and really learn some
programming. SQL is that, but seems different and easier to grasp if applied
only in excel and access Is this correct?
Thanks so much,
Frank
 
Hi,
I got it to work using <> . Is that the correct way? It does work. I was
trying to figure out how to write "not equal to" and that came to mind.
Frank
 
Me again, f I am being a pest, please just say so and I will slip away into
the night. Now I need "if A1 is empty then F1(where the S or NS goes) is
empty. I will try.
Frank
 
If it works, that is the way. SQL has little to do with worksheet formulas.
Programming has little to do with worksheet formulas. If you want to learn
how to do formulas, you can check out these newsgroups or get a book like
John Walkenbach's book on Excel formulas.
 
Hey Ron,
Yes, your second formula works for the stock numbers. Only thing now is I
need like a "If not one of these numbers then NS" There are only 10 stock
numbers, everything else is Non Stock "NS"
Click to expand...


Well, then, my first formula should do the job:

=IF(OR(A1={32,56,87,14}),"S","NS")

Just add the rest of the "S" numbers to the array constant in the formula.
--ron
 
If the cell is really empty (doesn't contain a formula or a space or
anything).
=if(isblank(A1),F1,something)

If it just looks blank

=if(Trim(A1)="",F1,something)
 
But this is a bit foreign to me. Am I correct in assuming I
would need to really study up on SQL to get a grasp on some of the stuff I
have been asking help for? I am really interested, but I feel it's a bit
late and time consuming (I am a musician also) to try and really learn some
programming. SQL is that, but seems different and easier to grasp if applied
only in excel and access Is this correct?
Click to expand...

Frank,

I don't know if SQL would be any easier. What you are asking about is pretty
straightforward Excel lookup stuff. My problem is understanding exactly what
it is that you want to do.

I have found that once I can specify precisely the nature of the problem, the
solution is usually pretty straightforward. And if I can't see it, there are
plenty here who can.


--ron
 
Me again, f I am being a pest, please just say so and I will slip away into
the night. Now I need "if A1 is empty then F1(where the S or NS goes) is
empty. I will try.
Frank
Click to expand...


=IF(ISBLANK(A1),"",IF(OR(A1={32,56,87,14}),"S","NS"))


--ron
 
Thanks so much Everyone. Ron, I used your last formula. excel? To use
advanced queries (lookups?) in access or excel, I just always thought that
was similar to SQL? If you guys say no, well, I guess it's no. Boy, I have
alot tolearn here. I cannot see posting questions here day after day, not
fair anyway, I need to learn this stuff on my own and come here when I am
stumped.
Thanks again,
Frank
 
Ok Thanks
Frank
Tom Ogilvy said:
If it works, that is the way. SQL has little to do with worksheet
formulas.
Programming has little to do with worksheet formulas. If you want to
learn
how to do formulas, you can check out these newsgroups or get a book like
John Walkenbach's book on Excel formulas.
Click to expand...
 
Thanks so much Everyone. Ron, I used your last formula. excel? To use
advanced queries (lookups?) in access or excel, I just always thought that
was similar to SQL? If you guys say no, well, I guess it's no. Boy, I have
alot tolearn here. I cannot see posting questions here day after day, not
fair anyway, I need to learn this stuff on my own and come here when I am
stumped.
Thanks again,
Frank
Click to expand...

People are here to help. And no one is going to resent you asking many
questions.

With regard to doing lookups in Excel, look at HELP for the VLOOKUP, HLOOKUP,
LOOKUP, MATCH and INDEX worksheet functions. That should get you started.


--ron
 
Back
Top