# Excel Dropdown from Multiple Pages

#### juscook10

##### New Member
I am trying to create a spreadsheet that has a dropdown that pulls information from whichever sheet corresponds to the date selected in the dropdown. I have one sheet for each day of the month, and if the user selects 2 then it will pull the information from the 2nd day in the corresponding cells to the main front sheet. I have figured out how to do this with a nested if statement, but it is too long and excel just puts invalid for my formula after I save and reopen file. Any ideas on how to fix this?

### Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Is this just looking up the data for that one single date, or are you wanting to show the first fifteen days of data?

in cell N28, put in:

N28 = INDIRECT("'"&DAY(\$O\$25)&"'!"&ADDRESS(ROW()-3,COLUMN()-13)) then drag to the right then down..

Is this just looking up the data for that one single date, or are you wanting to show the first fifteen days of data?
I am wanting to pull the info from one specific date that the user picks from the drop down.

in cell N28, put in:

N28 = INDIRECT("'"&DAY(\$O\$25)&"'!"&ADDRESS(ROW()-3,COLUMN()-13)) then drag to the right then down..

That works perfectly. Could you give me a short explanation as to how this works? I have never seen a formula like that.

INDIRECT function returns the reference specified by the test string while ADDRESS function returns a text representation of a cell address.

In the formula above:
ROW() = 28 *row number of cell N28
COLUMN() = 14 *column number of cell N28
therefore the parameters of the ADDRESS function are ADDRESS(25,1) which returns the cell A25

INDIRECT function now uses the DAY of the date in O25 and the result of the ADDRESS function, so if the selection in O25 is 4/2/2013,
=INDIRECT('2'!A25)

I hope this helps.

INDIRECT function returns the reference specified by the test string while ADDRESS function returns a text representation of a cell address.

In the formula above:
ROW() = 28 *row number of cell N28
COLUMN() = 14 *column number of cell N28
therefore the parameters of the ADDRESS function are ADDRESS(25,1) which returns the cell A25

INDIRECT function now uses the DAY of the date in O25 and the result of the ADDRESS function, so if the selection in O25 is 4/2/2013,
=INDIRECT('2'!A25)

I hope this helps.

That is great. Thank you.

Replies
7
Views
294
Replies
6
Views
867
Replies
0
Views
260
Replies
4
Views
627
Replies
5
Views
377

1,203,644
Messages
6,056,525
Members
444,872
Latest member
agutt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back