Replace part of the formula with pull-down menu choice

mc_mice

New Member
Joined
Dec 20, 2005
Messages
6
I need help with this issue:

I have a formula ( example =sum(A1) ) . I need the 1 to be configuratable from a pull-down menu. So, if I choose from the pull-down menu 4, the formula will print the summary from the cell A4

Thanks to all that will help
 

Some videos you may like

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
Hello, mc_mice
Welcome to the Board !!!!!

take a look at the function INDIRECT
=INDIRECT("A" & B1)where B1 houses you dropdownmenu
if B1 = 4 then the function will return the contents of A4

kind regards,
Erik
 

mc_mice

New Member
Joined
Dec 20, 2005
Messages
6
Ok, the INDIRECT-command solved the issue partly, I should have been more precise:

- The data is on another sheet
- The command that I am using is

=SUM((COUNTIF(Sheet1!A3:A10;"1"));COUNTIF(Sheet1!A3:A10;"0"))

where I have to change the A to something else ( B,C etc.)

It seem that I cannot implement the INDIRECT to this formula
 

erik.van.geit

MrExcel MVP
Joined
Feb 1, 2003
Messages
17,832
but you can
Code:
=SUM((COUNTIF(INDIRECT("sheet1!" & A1 & "3:" & A1 & "10");"1"));COUNTIF(INDIRECT("sheet1!" & A1 & "3:" & A1 & "10");"0"))
A1 houses the column letter
 

Watch MrExcel Video

Forum statistics

Threads
1,119,002
Messages
5,575,499
Members
412,670
Latest member
Khin Zaw Htwe
Top