Hello again!
Here is my issue. I have a workbook with thw sheets. One will have 48 columns in the end, 4 for each month of the year. I have attached a sample workbook with twelve columns for reference.
I need to pull the 4 monthly columns of data to the Monthly Data sheet. I can do this with Match and Index for one column but can't get it to work for all four.
What can or do I need to do to make this work? Any help will be appreciated.
Tim
In B4, enter =INDEX('Full Year By Month'!$A$4:$M$170,ROW()-3,MATCH(B$1,'Full Year By Month'!$A$1:$M$1,0)) to see if this works
Try this formula startign in B4, fill right and down
=INDEX('Full Year By Month'!4:4,MATCH(B$1,'Full Year By Month'!$1:$1,0))
If you liked my solution, please click on the Star -- to add to my reputation
If your issue as been resolved, please clearly state so and mark the thread as [SOLVED] using the thread tools just above the first post.
I've added a named range called DATA on your main sheet that consists of all the data cells.
I've added a drop down to your Monthly sheet, just select the month you want to see.
The rest of the cells below are using a standard INDEX/MATCH/MATCH method to bring over the data from the matching "row".
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Tried both and get a #NAME error. I have attached the updated workbook.
Tim
The formula needs to be entered on the Monthly Data sheet, you have a Name error on the other sheet, so when you see that you are actualy pulling the correct cell.
Your formula in B5 is correct, copy it UP.
You do not need the list in column I. Look at my sheet again and the DV in B1. Then look at the formulas in C1:E1. Just selecting February in B1 (not B2) will change the headers across the top and thus the formulas below would pull from the matching columns.
I also noticed you greatly reduced the INDEX range. Thats fine, but INDEX is robust and will work fine on the larger range I set which would have allowed you to enter more data without changing the ref formulas.
EDIT: I like that formula from post #3. I'd use that with the DV setup from my sheet.
I used your suggestions from the original post and yours works perfectly. My message was for the other posts suggestions. Sorry for the confusion. I will review your suggestion for another formula from a different poster. I need to get my head around the logic here. Could you explain what it is the formula is doing here. I try to understand these things well but sometimes...
I will be expanding the book for all the months and columns. 48 in all plus quarters. Do I need to adjust anything in the formula? I actually have 2064 budget lines. also, can I shorten the column names?
Tim
If you just use the workbook I posted, you can add more columns as you wish to the data sheet.
=INDEX(Xy-Range, row-ref(x), column-ref(y))
Did that fix your problem?
Thanks to all Of you who helped me with this issue. I couldn't have done this without your help. The formula and style worked perfectly. I only wish I could do this with the ease you all do.
Tim
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks