I need help with a formula, I need it to return a blank cell.

Cindy Bowker

Good Morning Paul,

I hope you don't mind helping me out again. I have tried to use the INDEX/MATCH formula and it doesn't seem to be working. I will include a couple of screen shoots of my working sheet and the reference sheet.

Ideally what I need to do is pull the per piece pricing of the scaffold from the reference sheet and them multiple it by the quantity of scaffold required.

Any help you could provide would be greatly appreciated.

Paul Newcome

I don't mind at all!

How is it not working? Is it pulling the wrong data, throwing an error, other?

Cindy Bowker

=INDEX({Scaffold Reference Range 3}, MATCH(Scaffold@row, {Scaffold Reference Range 2}, 0))

This is what I have so far, it is giving me a #NO MATCH error. I haven't yet tried to do the multiplication step.

Paul Newcome

Which columns are you referencing in your ranges?

The first range should be the column you want to pull from and the second range is the comparison column.

Cindy Bowker

let me try is again and make sure I have it typed in correctly

Cindy Bowker

I have the first range as the monthly price column ( which is what I am looking for), and the second column is from the part column which is what I am looking for.

Not sure what I am missing here.

Paul Newcome

Your data doesn't match.

If you look at the Formula sheet, the Scaffold column starts with

"AD210 2'W"

but in your Reference sheet you have

"AD210 - 2'W"

The hyphen is missing on the Formula Sheet.

Cindy Bowker

I never noticed that, I copied for one drop down and typed another. I will fix that up and let you know what happens. I guess this is what happens when you stare at the screen so much you can;t even see simple mistakes.

I will let you know what happens.

Cindy

Paul Newcome

No worries. I tend to get so focused on the formula itself, that I forget to check or completely miss the data.

Cindy Bowker

Okay so that is now pulling the correct pricing, the next problem is to multiply that number by the QTY required.

Would I use something like this:

=VALUE(QTY REQUIRED COLUMN*INDEX({Scaffold Reference Range 3}, MATCH(Scaffold@row, {Scaffold Reference Range 2}, 0)))

Paul Newcome

Before we over-complicate things... Let's take a look at the data.

How exactly is the dollar amount being populated in the [Monthly Cost] column of the reference sheet?

Do you type in a number and the column is formatted as a currency, are you manually typing in the "$"?

Are you using a formula (if so what is it)?

Same questions as above for the [Qty Required] column on the formula sheet?

Cindy Bowker

It was imported from the original excel file which appears to be a formatted currency column.

The quantity is entered by the employee filling in the form as a number.

Paul Newcome

Ok. Let's start with this...

=[Qty Required]@row * INDEX({Scaffold Reference Range 3}, MATCH(Scaffold@row, {Scaffold Reference Range 2}, 0))

If that does not work, try this...

=[Qty Required]@row * VALUE(SUBSTITUTE(INDEX({Scaffold Reference Range 3}, MATCH(Scaffold@row, {Scaffold Reference Range 2}, 0)), "$", ""))

Cindy Bowker

Using the value substitute method worked great.

I do have one other question. When the guys are ordering scaffold there are several different pieces that they need to order, is there a way to allow duplicate fields to be ordered from when employees are filling in a form. My thought is it might be easier to have a separate form for the scaffold that is on another sheet that would have the different categories of scaffold in separate columns.

Any thoughts on this?

Paul Newcome

I'm not sure I follow.