Vlookup Return Cell Reference on Another sheet

[mco5044]

I'm trying to use a vlookup to return the cell reference on another sheet so I can use it in a formula. The other sheet is called sales.

For some reason, I can get it to return Sales!A5 or =Sales!A5 but it doesn't count as a cell address for my other formula. It just treats it like text. Anyone know how to fix this?

It needs to be a cell reference so when I drag down it drags on the Sales spreadsheet to A6, A7, etc. instead of dragging on the active sheet.

Hopefully I explained this enough. Kind of tricky. I can create some example spreadsheets if that would help

Thanks for all of your help!

Mike

 

[Aladin Akyurek]

What is the formula you tried? This, in order to get right references...

 

[mco5044]

I think this definitely needs more explanation.

Sales spreadsheet:
[TABLE="width: 319"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD][/TD]
[TD]Weekday[/TD]
[TD]Product Sales Units[/TD]
[/TR]
[TR]
[TD="align: right"]4/01/14[/TD]
[TD][/TD]
[TD]Tuesday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: right"]4/02/14[/TD]
[TD][/TD]
[TD]Wednesday[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]4/03/14[/TD]
[TD][/TD]
[TD]Thursday[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: right"]4/04/14[/TD]
[TD][/TD]
[TD]Friday[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: right"]4/05/14[/TD]
[TD][/TD]
[TD]Saturday[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 319"]
<colgroup><col span="2"><col><col></colgroup><tbody>[TR]
[TD="align: right"]4/06/14[/TD]
[TD][/TD]
[TD]Sunday[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD="align: right"]4/07/14[/TD]
[TD][/TD]
[TD]Monday[/TD]
[TD]9[/TD]
[/TR]
</tbody>[/TABLE]

Week1 Report Spreadsheet:

[TABLE="width: 342"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Date[/TD]
[TD]Weekday[/TD]
[TD]Product sales Unites[/TD]
[/TR]
[TR]
[TD]4/01/14[/TD]
[TD]Tuesday[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]4/2/14[/TD]
[TD]Wednesday[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]4/03/14[/TD]
[TD]Thursday[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4/04/14[/TD]
[TD]Friday[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]4/05/14[/TD]
[TD]Saturday[/TD]
[TD]7[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 342"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]4/06/14[/TD]
[TD]Sunday[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]4/07/14[/TD]
[TD]Monday[/TD]
[TD]9
[/TD]
[/TR]
</tbody>[/TABLE]


Week 2 would obviously show from 4/8 to 4/14

My goal is to make it so on the weekly dashboard all I need to do is select month=April and all of the dates will update based on the data in Sales spreadsheet. My methodology was to create a vlookup to find the cell reference for the 1st day of every month. Then on week 2, I would just add 7 or use the last cell address from the bottom of the week 1 report spreadsheet.

If I can figure out how to do this to update the dates, then it should be a very similar formula for all of the other data on the Sales sheet.

From my research, it also looks like people can achieve this with offset and index with named ranges. Been trying solutions like that but can't figure it out either

Thanks,

Mike

 

[Aladin Akyurek]

You could try to adapt from the following:

http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

 

[mco5044]

You could try to adapt from the following:

http://www.mrexcel.com/forum/excel-questions/307832-multiple-vlookups.html#post1511354

Thanks for the resource Aladin.As far as I can tell, I think that's a different application. Or at least I can't visualize how I would be able to incorporate that solution.

I don't know if Im explaining this well or not. Kind of a complicated solution I think for what I'm looking for. I might end up just hiring a freelancer to help me because I think this probably goes beyond the scope of free help for simple solutions. I don't want to take advantage of anyone's time here

If any of you are on odesk and would like to apply pm me and Ill look for you

Thanks,

Mike

 

[Aladin Akyurek]

Thanks for the resource Aladin.As far as I can tell, I think that's a different application. Or at least I can't visualize how I would be able to incorporate that solution.

I don't know if Im explaining this well or not. Kind of a complicated solution I think for what I'm looking for. I might end up just hiring a freelancer to help me because I think this probably goes beyond the scope of free help for simple solutions. I don't want to take advantage of anyone's time here

If any of you are on odesk and would like to apply pm me and Ill look for you

Thanks,

Mike

Try Excel Consultant | Excel Consulting | Microsoft Office Consultants