Running a formula based on a Drop Down option

[ernhump11]

I am trying to set up a "Refund" table for a group I work with to help them figure their own refunds.

I have looked everywhere and not sure if Excel is able to do this or not.

What I'm trying to do is set up a table with different options in a drop down list: "Manual Refund, Renewed in Error, . . . . When someone selects "Manual Refund" I want it to run a specific formula, and so on for the other options. I seem to only find options for VLOOKUP or INDEX,MATCH. The issue is, I can set up a formula and run it with the correct values returned to one specific row but when I copy it to the next row its still running the formula for the previous row. Is there a way to do something like this in excel or I'm I trying to ask too much from it?

[shirleyxls]

I'm not sure what you mean by "run a formula". It sounds like you might have some absolute cell references, but hard to tell.

Would you be able to upload a small sample workbook with anonymised data, include your formula and what you want to happen. It just makes it easier to help you

[FDibbins]

Hi, welcome to the forum

Using a DD is exactly the same as using a manually entered cell, all a DD does, is assist with entries to avoid typos and save typing, or restrict entry to a specified critera.

having said that, without seeing what data you are working on, and what you want, we cant really offer specific help
What formulas are you using?
What options do you want?
As mentioned above, can you upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like?
To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

[ernhump11]

So here is what they told me to do for VLOOKUP but I quickly learned that VLOOKUP works better for set values.

It works for the top row but if I enter anything into the row below, it returns the totals for the row above.

I know my formula for Manual Refund on the second sheet is only for the row I specified but when I use a "name range" in the formula it returns #VALUE!

[shirleyxls]

Thanks for the sample.

When I choose a different reason on rows 6 and 7 I get the correct figures in columns N and O, with the correct total so not sure I understand the issue.

In column P, you don't really need the =SUM(N5+05), just =N5+O5 would do

[ernhump11]

It works for Row 6 but I want to enter in different dates and information into Row 7. When I do that, I still get the same totals for Row 6.

I don't know how to explain it very well because I don't really know the right "lingo". That could be why I'm having trouble trying to find it through GOOGLE. lol

[shirleyxls]

No problem - it's tricky when you're first starting out

Your LOOKUP is only checking the value you have in your Reason column which is why you get the same results every time.

I think the problem may be more related to your table on sheet 2 as this only looks at Row 5 on Sheet 1 to calculate the 2 values you return with your LOOKUP formula so it will always produce the same results.

Could you explain a bit more about what you are expecting to see? It looks like there are some date dependencies, but if you could write down how things should be calculated that would be great. Forget all about Excel for a minute and just note down the logic, then we can apply that (hopefully!)

[shirleyxls]

Hello again,

I've spent a little time having a look at your sheet and have done a little work to embed some formulas in your "Refund" columns. To do this, I've added two helper columns at the end (you can hide these later if you want).

The first one TYPE just puts a code number based on a new column I added to your table on Sheet 2 - it just makes it easier to see in the formula rather than having your whole reason text.

The second one DATEDIF is basically the same formulas you had in your Sheet 2 table (I think!) - it looks like your just coming up with a months difference calculation based on sold, registration and expiration dates, but I could be wrong.

The new refund formulas now just do something different depending on which TYPE is selected, but work on each row.

It may not be exactly what you need, but I'm just about to go out for the evening so didn't want to leave you hanging! Have a look and see what you think on the attached file. Let me know if I'm wide of the mark though

[ernhump11]

WOW!!! Thank you all so much for your help! I think I finally got it!

[shirleyxls]

Excellent! Glad to have helped.

Don't forget to tag your post as SOLVED.

If you have any more questions, this is a great forum with lots of people very willing to help, so just fire away