Need help with an Excel formula to pull data from one worksheet to another based on the current month.?

Question

Help with Excel?
I have a spreadsheet for an annual budget with the monthly detail data on one worksheet (the tab is labeled Monthly Detail) and I summarize the Current Month and Year to Date data on a different worksheet (the tab is labeled 2014 Summary).

I would like to find a way do pull the "current" month's data from the current month cells on the Monthly Detail worksheet and put that data in the "CURRENT MONTH" cells in the 2014 Summary worksheet.  I can't seem to figure out how to do this for more than 2 months. (Yup!  I got January and February to work with an IF/Then/Else...but since it peters out after a TRUE/FALSE/THEN I'm stuck!)

So, each month's detailed data is located in worksheet "Monthly Detail" cells (Jan) B47, (Feb) D47, (Mar) F47, (Apr) H47...and so on...through (Dec) X47.

On the 2014 Summary page, in cell B49, I want to (A) determine the current month, then (B) pull from the appropriate cell of the "Monthly Detail" worksheet, the correct data, and put it that correct data in cell B49 on the 2014 Summary worksheet.

FYI: On the 2014 Summary worksheet, in cell A2, I have =MONTH(TODAY()) which returns a value of 1 for Jan, 2 for Feb, 3 for Mar and so on.....

That said,

So for January 2014, I want a formula in B49(of the 2014 Summary worksheet) that goes to the worksheet, "Monthly Detail", and pulls only January data and puts it in B49.  In February, I want to go to "Monthly Detail" and pull February data and put it in B49 since that will be the 'current' month.  Then in March I wan to pull March data and so on through December of 2014 cause then someone else will have to figure this out!

Right now I can't figure out how to code a formula on the 2014 Summary worksheet to pull the correct (Monthly Detail) MONTHLY cells into the correct CURRENT MONTH cells on the 2014 Summary worksheet.  Heck, I don't even know if I'm doing this the right way!

Any help would be gratefully appreciated! Just remember 2 things, first I'm the dumbest coder ever! and second, don't forget number one!

Thanks (in advance!)

? · Accepted Answer

it would be easier to create 13 seperate worksheets in one book
12 worksheets titled  jan, feb, mar etc the 13th being the yearly summary

once you've created it save it somewhere safe for following years

now go to jan's figures select cell range (I'm assuming 1 column only) right click - select copy - go to annual summary jan column right click - paste special - select link

that's the easiest option

you can have seperate workbooks for each month and the option to import (copy cells) from one workbook to another workbook would be the same (only the formulae would include in it's ref "workbook"

Please also protect the data on the summary annual page so that people cannot overtype

Chandra · Answer

Your lengthy query lacks the essential thing. Do you want some data to be just copied with the link, or summation. for getting same months data from one worksheet to other, just Paste Link would do.Just enter = and then move the cursor, which you want to link and then enter key to get the link.If you want summation, That could also be accomplished with the link and use of +operator.
Date calculation is very tricky, in that excel tries to calculate time and date, whenever a sheet opens, and the date and time changes for all the cells, because excel recalculates. Your question is ambiguous

Trending News

Need help with an Excel formula to pull data from one worksheet to another based on the current month.?

2 Answers