I have a large range of cells that I would like to add two or more values to, depending on which option is chosen from a drop down menu. Is this possible?
I have a large range of cells that I would like to add two or more values to, depending on which option is chosen from a drop down menu. Is this possible?
It may or may not be... it's not entirely clear from your post what you actually mean.
Could you explain a little more?
Perhaps attach an example workbook with a before & after?
If I've been of help, please hit the star
Look's like a SUMIF issue.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
A simple example could be this. Cell A1 is a drop down menu, with two choices "1" or "2". Cell B1:F10 would contain two different values. The visible value is dependent on whether"1" or "2" is chosen from the drop down menu. Im a little rushed atm, but if that doesn't make sense I will make an example worksheet. Thanks for your help.
Data Dependent on Drop Down.xlsx
In the example I attached, all of the red question marks would change to A data, B data, or C data, depending on what option I pick from the drop down.
try this...
In B2, copied down and across, use this...
=OFFSET(B$1,ROW(A1),MATCH($C$1,$F$1:$P$1,0)+2)
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Hmmm, I got it to work for cell B2 only. How would I get this to work for the whole range B2:D5?
On the sample file you provided, that worked fine when I copied down and across.
If your actual range is different to what you posted, adjust the ranges accordingly
excel problem 1.xlsx
Thanks so much, I got it to work on the example worksheet. However, when I tried to alter that formula to make it work in the actual worksheet I am making, I was having troubles. If it isn't too much too ask, could you make the formula for the new attached worksheet. Cell W2 contains the drop down menu and the data are in the 3 bottom red boxes. Thanks again!
OK yes, I can see how what I gave you wouldnt work, your tables are oriented differently
Try this approach instead.
1st give each table "heading" a name range. for instance, right-click W29, select "name a range" and click enter (if should already have picked MR as the name). Repeat for all other tables.
Then use this formula in R4, copied down and across...
=OFFSET(INDIRECT($X$2),ROW(A1),COLUMN(R1)-COLUMN($X1)+1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks