Yet another Excel Question

Gadgetman496

I'm trying to get Excel to do a simple task but am not familiar with combining excel functions (If that's the right term) or what symbol to use to separate the "IF" and "OR"

Basically I have a 3 item drop-down list in cells in column A

The drop down list gets its info from column H cells 1, 2 & 3

I have a price for each of the 3 items in column K cells 1, 2 & 3

I want it to work so as when I populate a cell in column A it will display the correct price for that item in column B and either leave it blank or insert a zero if cell A is not populated.

If I put the following function into a cell in column B it gives the correct result for that one item only. How do I get it to be able to understand the 3 different items

=IF(A1=H1,K1,0)

What I need I guess is to have an "IF" "OR" combination in the above function?

coylemj

This will compute the way you asked and will populate the field with zero if cell A1 isn't one of the values in the dropdown list.

=IF(A1=H1,K1,IF(A1=H2,K2,IF(A1=H3,K3,0)))

Gadgetman496

coylemj wrote: »

This will compute the way you asked and will populate the field with zero if cell A1 isn't one of the values in the dropdown list.

=IF(A1=H1,K1,IF(A1=H2,K2,IF(A1=H3,K3,0)))

Thank you coylemj that worked perfectly.

Looks simple when you know what you're at but that had my head wrecked.

I was starting to look at "VLOOKUP" & that was even more of a head-wreck :eek:

I'll have to find a good source to help me get a handle on using Excel functions.

Lu Tze

gadgetman496 wrote: »

Thank you coylemj that worked perfectly.

Looks simple when you know what you're at but that had my head wrecked.

I was starting to look at "VLOOKUP" & that was even more of a head-wreck :eek:

I'll have to find a good source to help me get a handle on using Excel functions.

Keep an eye on the main bargain alerts forum, there are free excel courses on udemy (has its own thread) every now and again posted, i did one before and it was decent, short videos explaining explaining individual functions/formulas/features like this.

Obviously other excel courses might be ****e, but worth keeping an eye on it anyways if it costs nothing!

Gadgetman496

coylemj wrote: »

This will compute the way you asked and will populate the field with zero if cell A1 isn't one of the values in the dropdown list.

=IF(A1=H1,K1,IF(A1=H2,K2,IF(A1=H3,K3,0)))

Now that I see it on a actual sheet it would be better to leave the field blank if cell A1 isn't one of the values in the dropdown list.

If I remove the ,0 from the end of the above function it populates the field with the word FALSE.

Is there any easy way to get it to display a blank cell?

Gadgetman496

Ah fook it. I see an issue now that I didn't allow for in my original question.

I initially asked the question in relation to 3 fields but that could in practice turn out to be 30 or 40 in columns H & K

If I copy the function formatting down the sheet in column B it increments everything by 1 for each row which throws out no result for anything past row 3

Dont be at yourself

gadgetman496 wrote: »

I'm trying to get Excel to do a simple task but am not familiar with combining excel functions (If that's the right term) or what symbol to use to separate the "IF" and "OR"

Basically I have a 3 item drop-down list in cells in column A

The drop down list gets its info from column H cells 1, 2 & 3

I have a price for each of the 3 items in column K cells 1, 2 & 3

I want it to work so as when I populate a cell in column A it will display the correct price for that item in column B and either leave it blank or insert a zero if cell A is not populated.

If I put the following function into a cell in column B it gives the correct result for that one item only. How do I get it to be able to understand the 3 different items

=IF(A1=H1,K1,0)

What I need I guess is to have an "IF" "OR" combination in the above function?

Something like this should work:

=if(countif(h:h,a1),vlookup(a1,h:k,4,false),"")

The vlookup looks for the value of a1 in column h, and returns the value of the corresponding row from column k. The countif ensures it only tries to fetch the value if a1 appears in column H.

Gadgetman496

Dont be at yourself wrote: »

Something like this should work:

=if(countif(h:h,a1),vlookup(a1,h:k,4,false),"")

The vlookup looks for the value of a1 in column h, and returns the value of the corresponding row from column k. The countif ensures it only tries to fetch the value if a1 appears in column H.

I think that about does it nicely

I take it the 4 in that function is for the 4 columns from H to K is that correct?

Really appreciate the help from all of you, thanks again.

Dont be at yourself

gadgetman496 wrote: »

I take it the 4 in that function is for the 4 columns from H to K is that correct?

Yep, exactly.

Gadgetman496

Dont be at yourself wrote: »

Yep, exactly.

Great I understand it so and that helps a lot

One final question regarding that function you gave me

=if(countif(h:h,a1),vlookup(a1,h:k,4,false),"")

If I was using column A on sheet 1 but I had the columns H & K populated on sheet 2 what should that function be in order for it to work? or can that even be done?

It would be cleaner if the data for populating the dropdown in column A and the price column were on a separate sheet.

Dont be at yourself

gadgetman496 wrote: »

Great I understand it so and that helps a lot

One final question regarding that function you gave me



If I was using column A on sheet 1 but I had the columns H & K populated on sheet 2 what should that function be in order for it to work? or can that even be done?

It would be cleaner if the data for populating the dropdown in column A and the price column were on a separate sheet.

Change 'H:K' above to 'Sheet2!H:K' (assuming you're keeping the data in columns H-K.

RoadhouseBlues

There is an ISBLANK function too if that's any good. You can denote the empty cell by using "". I'm just trying to learn this myself too though so you should prob listen to the other folks here:-)

OttoPilot

The h:k of the vlookup is where youre asking it to look for the value you're looking up. So when it comes to writing that part of the formula you should switch to sheet 1 and select columns h to k there. Then you will see the formula references the sheet it's looking in (sheet 1) as well as the columns. Then press command to enter the next part or parameter of the formula.

Gadgetman496

Dont be at yourself wrote: »

Change 'H:K' above to 'Sheet2!H:K' (assuming you're keeping the data in columns H-K.

Hmm! I am keeping the data on sheet 2 for columns H & K but I want the dropdown list in column A of sheet 1 but it's not allowing me to select the data on sheet2 when trying to create the dropdown on sheet1

I'm using the toolbar option Data>Validation>List when trying to create the dropdown which works fine when the data is stored on the same sheet but not if the data is on the second sheet.

Dont be at yourself

gadgetman496 wrote: »

Hmm! I am keeping the data on sheet 2 for columns H & K but I want the dropdown list in column A of sheet 1 but it's not allowing me to select the data on sheet2 when trying to create the dropdown on sheet1

I'm using the toolbar option Data>Validation>List when trying to create the dropdown which works fine when the data is stored on the same sheet but not if the data is on the second sheet.

Instead of selecting the cells, just type in the cell reference (Sheet2!H:H).

Gadgetman496

First, a huge thanks to those who helped me on this so far

OK! I have made some changes like adding a quantity column and moved the location of the columns and cells to make it as straight forward as I can for the purposes of getting it to work and it is now doing everything I initially looked for it to do.

My whole aim here was to be able to carry out the task with the minimum amount of clicks and this is the function formula I ended up with

=IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:B,2,FALSE)*B2,"")

What it does is, each time a part is chosen from the parts dropdown it automatically puts the correct price for that part into the correct cell in the cost column.

Column A sheet1 is a parts list dropdown (Gets its data from column A on sheet 2)

Column B sheet1 is the Qty dropdown (Gets its data from column C on sheet 2)

Column C sheet1 is the price of each part and is the column which cells holds the formula above (Gets its data from column B on sheet 2).


I have one final request for help on this due to the introduction of the Qty column. 99% of the time the Qty for the cells in column B sheet1 will be 1 but there will be the odd occasion where the qty might be more than 1 (hence the Qty dropdown) and I don't mind having to chose the Qty from the dropdown whenever it's greater than 1. So here's what I need help achieving,

What needs to be done to this formula to get it to automatically put a 1 into the cells of column B each time the cells in column A are populated with a part?

=IF(COUNTIF(Sheet2!A:A,A2),VLOOKUP(A2,Sheet2!A:B,2,FALSE)*B2,"")

I'm hoping all those who helped me get this far will help me get over this final hurdle to get this closed out and alleviate my head-wreck

Dont be at yourself

So column A contains the parts dropdown, column B contains the quantity, and column C contains the above formula, which calculates the total price (unit price pulled in from Sheet2, * quantity)? And you want the quantity column to default to 1, so long as there's a part selected in column A?

In that case, the simplest way is to place '=COUNTA(A2)' in B2 -- this counts the number of non-blank cells in your range (A2). It will be 1 if A2 contains any text, and 0 if it is empty.

Dont be at yourself

So column A contains the parts dropdown, column B contains the quantity, and column C contains the above formula, which calculates the total price (unit price pulled in from Sheet2, * quantity)? And you want the quantity column to default to 1, so long as there's a part selected in column A?

In that case, the simplest way is to place '=COUNTA(A2)' in B2 -- this counts the number of non-blank cells in your range (A2). It will be 1 if A2 contains any text, and 0 if it is empty.

Gadgetman496

Dont be at yourself wrote: »

So column A contains the parts dropdown, column B contains the quantity, and column C contains the above formula, which calculates the total price (unit price pulled in from Sheet2, * quantity)? And you want the quantity column to default to 1, so long as there's a part selected in column A?

In that case, the simplest way is to place '=COUNTA(A2)' in B2 -- this counts the number of non-blank cells in your range (A2). It will be 1 if A2 contains any text, and 0 if it is empty.

Thank you once more for taking the time to help out

Yes, you understood 100% what I wanted :cool:

God damn it you make that look so easy It's great to know what you're doing.

That just goes to show how little i know about this stuff. I was going on the logic that if the existing formula new that it only put a price into the cells of column C if the cells in column A were populated I would have to make the required change to the formula itself instead of using the COUNT function in a totally different cell

Anyway, your suggestion functions perfectly, the only thing I would like to avoid if possible and it's purely a cosmetic gripe is, the cells in the Qty column B are full of zeros when you open a clean sheet and before you populate anything, & while it's fully functional it's a little ugly. Would it be possible to hide them or anything like that?

Dont be at yourself

gadgetman496 wrote: »

Anyway, your suggestion functions perfectly, the only thing I would like to avoid if possible and it's purely a cosmetic gripe is, the cells in the Qty column B are full of zeros when you open a clean sheet and before you populate anything, & while it's fully functional it's a little ugly. Would it be possible to hide them or anything like that?

=if(counta(a2)=1,1,"")

If you learn the below functions, you can do a huge amount in Excel:

IF
COUNTIF
COUNTA
VLOOKUP

Gadgetman496

Dont be at yourself wrote: »

=if(counta(a2)=1,1,"")

If you learn the below functions, you can do a huge amount in Excel:

IF
COUNTIF
COUNTA
VLOOKUP

I've only just spotted an issue with both the

=COUNTA(A2)

and

=if(counta(a2)=1,1,"")

Any time either of the above gets entered into a cell on column B the column cell loses the dropdown list option. and once the cell does get populated with a number it overwrites the formula

Gadgetman496

Can this formula below be changed so that you can tell it what cell to populate with '1' rather than have it put the '1' into the cell that holds the formula? If the condition is met I would want it to populate the cell B2 with a '1' but actually hold the formula itself in a totally different cell.

=if(counta(a2)=1,1,"")