Posts by mljohn

I only want to see the current time so I am using NOW() with Shift+Ctrl+2 to only show the current time. However, when I use that time in a formula that finds if that time is within a time range it does not work. When I type the time in manually it works.
How do I use the NOW() formula with the formula to find if the time is within the time range? =IF($J$16=MEDIAN(R2,S2,$J$16),"Yes","No")

Thanks, works perfectly

Is there a formula that can find a cell or partial cell in one sheet in the column of another sheet and tell me what the original cell was?

Example:
In sheet2, L6 it says "TN880 Bk". It would search column B in Sheet1 and find in B2 "TN880". It would then return the result of "TN880" in M6 (where this formula resides) in sheet2.

Or if it is an exact match it would return the exact match.

Re: Dynamically create range 3 years from TODAY for formula

Works great. Thanks

Re: Dynamically create range 3 years from TODAY for formula

Quote from skywriter;797548

Maybe something like this?
=INDEX(OFFSET(Data!$GM$2,0,-38,1,36),MATCH(TRUE,INDEX(OFFSET(Data!$GM3,0,-38,1,36)<>0,0),0))

This does not work. I get #N/A

$GM$2 is the column name "First Ord" , I assume that is not correct but I don't see how to fix it.

Matt

Re: Dynamically create range 3 years from TODAY for formula

Is there anybody that can help with this?

Matt

Re: Dynamically create range 3 years from TODAY for formula

Does this make sense? I could use some help with this.

Re: Dynamically create range 3 years from TODAY for formula

Here is my attachment of my spreadsheet.

forum.ozgrid.com/index.php?attachment/73393/

My columns are named by month and date "Jun-2017", "Jul-2017", "Aug-2017"

Cell GO1 formula: =TODAY()

I will use this report monthly so I want it to be dynamic every time I add a new months column of data.

In cell GM3 I have this formula. I want the formula to apply only within the last 36 months (last 36 columns) from the GO1 date.

=INDEX(Data!$B$2:$GJ$2,MATCH(TRUE,INDEX(Data!B3:GJ3<>0,0),0))

Any thoughts on how to do this would be very much appreciated.

Matt

Re: Adjust formula from last to first

Excellent. That did the trick

Thank you

I have this formula that finds the last cell in a row that is greater than $0.00. Then it finds the date header at the top of the column.

LOOKUP(2,1/INDEX(Data!B4:GI4<>0,0,0),Data!$B$2:$FU$2)

Can it be adjusted to find the first cell?

Matt

Re: Add to column of cells all part numbers with a price

I was hoping for a formula but I will keep this in mind.

Thanks

I am trying to come up with a formula that gives me all of the part numbers that have prices.
In Sheet "Orig" I have my data. In Sheet "Result" in column "A" is where I want the results.

There are two sources. NC Price and PremPrice.
If there is a price in NC Price for a particular part number I want the part number in a cell in the Result sheet.
If there is a price in PremPrice for that same part number I want the part number in the next cell down.
I want a part number for every part number that has a price.

This one has me banging my head.

See example below.

[TABLE="width: 558"]

[tr]

[td][/td]

[td][/td]

[td][/td]

[TD="colspan: 2"]Sheet "Orig"[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

Sheet "Result"

[/td]

[/tr]

[tr]

[td][/td]

[td]

A

[/td]

[td]

B

[/td]

[td]

C

[/td]

[td]

D

[/td]

[td]

E

[/td]

[td]

F

[/td]

[td][/td]

[td]

A

[/td]

[/tr]

[tr]

[td]

1

[/td]

[td]

Brand

[/td]

[td]

Part#

[/td]

[td]

NC Price

[/td]

[td]

PremPrice

[/td]

[td][/td]

[td][/td]

[td][/td]

[td]

Result

[/td]

[/tr]

[tr]

[td]

2

[/td]

[td]

Brother

[/td]

[td]

DR110CL

[/td]

[TD="align: right"]$75.65[/TD]
[TD="align: right"]$124.42[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

DR110CL

[/td]

[/tr]

[tr]

[td]

3

[/td]

[td]

Brother

[/td]

[td]

DR200

[/td]

[TD="align: right"]$34.00[/TD]
[TD="align: right"]$39.00[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

DR110CL

[/td]

[/tr]

[tr]

[td]

4

[/td]

[td]

Brother

[/td]

[td]

DR250

[/td]

[TD="align: right"]$37.40[/TD]
[TD="align: right"]$42.00[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

DR200

[/td]

[/tr]

[tr]

[td]

5

[/td]

[td]

Brother

[/td]

[td]

DR300

[/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR200

[/td]

[/tr]

[tr]

[td]

6

[/td]

[td]

Brother

[/td]

[td]

DR350

[/td]

[TD="align: right"]$21.25[/TD]
[TD="align: right"]$38.00[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

DR250

[/td]

[/tr]

[tr]

[td]

7

[/td]

[td]

Brother

[/td]

[td]

DR360

[/td]

[TD="align: right"]$20.40[/TD]
[TD="align: right"]$39.90[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

DR250

[/td]

[/tr]

[tr]

[td]

8

[/td]

[td]

Brother

[/td]

[td]

DR630

[/td]

[TD="align: right"]$22.95[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR350

[/td]

[/tr]

[tr]

[td]

9

[/td]

[td]

Brother

[/td]

[td]

DR700

[/td]

[TD="align: right"]$38.25[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR350

[/td]

[/tr]

[tr]

[td]

10

[/td]

[td]

Brother

[/td]

[td]

DR720

[/td]

[TD="align: right"]$22.10[/TD]
[TD="align: right"]$42.00[/TD]

[td][/td]

[td][/td]

[td][/td]

[td]

DR360

[/td]

[/tr]

[tr]

[td]

11

[/td]

[td]

Brother

[/td]

[td]

LC103BK

[/td]

[TD="align: right"]$1.45[/TD]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR360

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR630

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td]

9

[/td]

[td]

6

[/td]

[td]

Total

[/td]

[td]

15

[/td]

[td][/td]

[td]

DR700

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR720

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

DR720

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

LC103BK

[/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[/tr]

[tr]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td][/td]

[td]

Total

[/td]

[td]

15

[/td]

[/tr]

[/TABLE]

Re: Find product price from appropriate vendor from all vendors

Worked great. Thanks

Matt

In Sheet "Orig", Cell B3 I have the part number that I am looking for the price.

In Sheet "Vendors" I have all of the price lists from all of my vendors.
In Sheet "Vendors" column "C" I have matching part numbers for all of my vendors.
In Sheet "Vendors" column A I have the names of the vendors such as below:

A B C D E
[TABLE="width: 318"]

[tr]

[td]

Vendor

[/td]

[td]

Brand

[/td]

[td]

Part#

[/td]

[td]

Other#

[/td]

[td]

Cost

[/td]

[/tr]

[tr]

[td]

SW

[/td]

[td]

Xerox

[/td]

[td]

106R02307

[/td]

[td][/td]

[TD="align: right"]$52.90[/TD]

[/tr]

[tr]

[td]

SW

[/td]

[td]

Xerox

[/td]

[td]

108R00795

[/td]

[td][/td]

[TD="align: right"]$36.50[/TD]

[/tr]

[tr]

[td]

SW

[/td]

[td]

Xerox

[/td]

[td]

108R00795

[/td]

[td]

108R00795

[/td]

[TD="align: right"]$46.75[/TD]

[/tr]

[tr]

[td]

LMI

[/td]

[td]

Xerox

[/td]

[td]

106R01486

[/td]

[td][/td]

[TD="align: right"]$49.00[/TD]

[/tr]

[tr]

[td]

LMI

[/td]

[td]

Xerox

[/td]

[td]

106R01530

[/td]

[td][/td]

[TD="align: right"]$52.90[/TD]

[/tr]

[tr]

[td]

LMI

[/td]

[td]

Xerox

[/td]

[td]

106R02307

[/td]

[td][/td]

[TD="align: right"]$59.90[/TD]

[/tr]

[tr]

[td]

LMI

[/td]

[td]

Xerox

[/td]

[td]

108R00795

[/td]

[td][/td]

[TD="align: right"]$74.90[/TD]

[/tr]

[/TABLE]

I want a formula that finds the matching part number from Sheet "Orig" Cell B3 in Sheet "Vendors" Column "C" and only returns the matching price for that part number from the LMI prices. It is like a Vlookup but would narrow it down to only inclued the search to the LMI rows.

thanks

Re: Use only the correct part number in a formula from multiple part numbers in a cel

80 views and no suggestions?

I have a column of part numbers. Each cell in the column has either one or multiple part numbers separated by a comma and space. (Sheet2)

On Sheet1 I have a cell (A1 - A6) to put in the part number I am looking for. In Column B I have a Vlookup formula to find the price of the part number.

The Vlookup formula does not work because of the multiple part numbers in the same cell

How do I create a formula that can pull the correct price for the correct part number even though that part number is combined together with other part numbers in the same cell?

Here is an example:
https://www.dropbox.com/s/hmc4gqei8aancl8/Search.xlsx?dl=0

Notice in Sheet1 Cell A1 and A3 are pulling the same price when they shouldn't.
Also, A5 and A6 are blank but still pulling the same price.

Thanks

Matt

Re: Count and Average

Thank you Trebor76 for the formula.

I was trying to make it harder than it needed to be.

Glad you understood my example.

Matt

Re: Count and Average

What I don't understand how to do is...

Find the the months that have amounts greater than zero B3:AK3

Average the number of months between amounts greater than zero B2:AK2

For example:

B2 May 2012, C2 Jun 2012, D2 Jul 2012, E2 Aug 2012, G2 Sep 2012, H2 Nov 2012, I2 Dec 2012
B6 $224.00, C6 $0.00, D6 $430.00, E6 $0.00, G6 $481.00, H6 $0.00, I6 $280.00
B7 $0.00, C7 $0.00, D7 $0.00, E7 $55.00, G7 $0.00, H7 $0.00, I7 $110.00

Answer for this client (A6): 1.75 months
Answer for this client (A7): 3.5 months

forum.ozgrid.com/index.php?attachment/65518/

Thanks

How do I average the number of months the cells that have amounts greater than zero.

Count months greater than zero within a 36 month period.

Average the amount of months between the months that are greater than zero.

Months = B2:AK2 Jan 2002, Feb 2002, Mar 2002, Apr 2002, May 2002, etc
Cells = B3:AK3 Cells that may contain numbers greater than zero

Thanks

Matt