# Replace part of the formula with pull-down menu choice

#### mc_mice

##### New Member
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

### 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
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
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
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

Replies
4
Views
139
Replies
4
Views
170
Replies
1
Views
195
Replies
1
Views
77
Replies
2
Views
87