Populating worksheet using the Indirect function and copying that formula across the cells

[makomborero]

down vote favorite

Hi I am currently creating a budget and am stuck on the formulas i need to get the data on various worksheets pulled into specific sheets. I have created worksheets for Jan to Dec for my household expenditure and have also another worksheet which i intend to use as the summary sheet from which when i select a month will be populated by the data from months worksheet. I have managed to create a dropdown for selecting the months and use the indirect function INDIRECT(""&$E$3&"!E8") to get from a single cell. The challenge comes when i try to copy the formula on the rest of the cells to pull data from, it retains the same value. How do i make it a dynamic formula so it becomes relative to the cell i have copied to whilst maintaining the dropdown selection for the months? Another question is , i have also created a frequency dropdown with daily, weekly, monthly, etc. Which function can i use so that when i select for instance daily, the value enter in a cell will apply a function that multiplies it by 365/12 to give a monthly value. Your help will greatly be appreciated

<tbody>
</tbody>

 

[DKaur]

I have a solution, a bit long winded but a solution nonetheless.

Where you have the equation
=INDIRECT(""&$E$3&"!E8")
Convert it to something like
=ABS(E8)
And drag this down, this will updated the E8 correctly.
Then highlight all the cells and do a find and replace all. Find =ABS( and replace with £INDIRECT(""&$E$3&"!
Then do another find and replace all. Find ) and replace with ")
Finally another find and replace all. Find £ and replace with =

This should update all the formulas and work like you want.

 

[makomborero]

Thanks Dkaur, i managed to follow that and it worked. But as you mentioned it's a bit winding but you get there