Hello, instead of doing lookups can you change cell references? For example, I'm looking up data in a different sheet for January. Let's say that's =Sheet1$D3.
Can I change that value based on a month? So, for example if I select February, that reference will change to =Sheet1$E3. Thanks.
Perhaps use INDEX/MATHC, instead?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
Yes, that's we've been working through haha. I was just wondering if I could create something that adds 1 column based on the month of year. So if January = Sheet1$13 then when I input February(return month value 2) it would change to Sheet1$F13 etc.
Essentially, I could return the value of the month. Let's say I input January that would be equivalent to 1. And then I base my formula of that value. I'm just not sure how to do that. My current worksheet is set up with just those regular formulas and by doing this it would sort of make the formulas dynamic (I would just have to change the month value) and I wouldn't have to go the process of setting up indirect/match.
How would I do the same thing for YTD? Thanks. Let me know if you understand what I'm saying or if I need to attach a file.
That is why I always recomend using real dates, instead of just month names. You can format the dates so that they look like just a month name/year, but, because they really are dates, you can use them in formulas much easier. You can see that from your other thread...How would I do the same thing for YTD? Thanks.
http://www.excelforum.com/excel-gene...ml#post4024016
where I used...
=SUMIFS(Sheet1!$F3:$O3,Sheet1!$F$2:$O$2,">="&Sheet2!C$3,Sheet1!$F$2:$O$2,"<="&Sheet2!D$3)
and then also put a SUMPRODUCT() together so you can use different rows...
SUMPRODUCT(--(Sheet1!$F$2:$O$2>=Sheet2!C$3)*(Sheet1!$F$2:$O$2<=Sheet2!D$3)*(Sheet1!$C$3:$C$5=Sheet2!$B4)*(Sheet1!$F$3:$O$5))
Hey Mr. Dibbins. I'm not sure I explained myself correctly. I understand your index/match solution and I understood why you should use real dates but what I'm asking here is slightly different. I don't want to use indirect/match. In this spreadsheet, I've already manually selected the cells associated with January. So, for example, =Sheet1$F13 is referencing the January column in another sheet. I don't want to do index/match (because I'll have to change the lookup names as they don't match). So rather than doing that, I just want to set column F as the base, essentially (1) and then have that value change based on the month that I input in the first sheet (it's really not even the month, I could just input 2 and that would equal February and that would look at Column G). Does this make sense? A simple example is this, I have =F10 in a cell. I want this cell to change to G10, if I write "2" in another cell.
OK then perhaps you need to look at the OFFSET function for this?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks