Read today's date and pull relevant data

Hey guys, bit of a tough one here...

I need my spreadsheet to now what day of the week it is (not date) then grab the correct information. To make myself clearer I shall explain a little more...

I have a 'Capacity Workbook' that shows people's hours of work per day. These are fed into a main 'Data Workbook' via formulae etc.

The main thing I need is for Excel to think 'Its Monday today! I'm going to give you Monday's data' and then read the relevant destination files. Then if it's tuesday, do the same thing.

Will this be possible? I realise that it's a bit vague at this stage but can't really think of how to describe it any further!

Regards

Thankyou, attached is a file that is a much simpler version of what I currently have.

In this workbook I've just copied the number that's on the other sheet. In reality instead of simply putting =A2, my actual formula would be =SUM(('My File Path[Capacity 2013 (2).xls]17 June - 28 June'!$AB$83)*60/27.84)*80%

If it isn't possible to do it automatically depending on the day of the week then I may simply have to refer to my macros but the problem is that my workbook has, for some reason, gone from 1mb to 56mb (no idea why, I've tried deleting all macros to see if they were the cause but they weren't, its rather frustrating).

Book1.xlsx

Thanks for your time guys, much appreciated!

Many thanks, I shall adapt my spreadsheet for this to work. Much appreciated. Do you have any idea as to why a spreadsheet would go from 1mb to 56mb? I tried saving as 2003-2007 workbook instead of a macro enabled book but then some of my macros don't work.

My apologies for the last post, I didn't read the last bit of your reply. Thankyou very much.

Jeffrey, my apologies for my simple nature this evening, I can't seem to get it to work on my actual sheet...

=IFERROR(HLOOKUP($J$9,Capacity!$A$29:$E$30,ROW(D9),0),"")

The code for 'today' is in J9, the table is in a sheet called Capacity and ranges from A29 to E30 in the format of the days on top with the numbers on the bottom similar to the workbook I uploaded earlier. However I don't really get what the 'ROW(D9)' bit means. Where should I focus this? At the moment it's not returning any data.

Perfect! Makes sense, thankyou very much. There is an area of this workbook where I require multiple arguments of this nature.

We have established the method for the day which I can't thank you enough for. However this one requires one cell to be 'Week One' and if the other cell says 'Monday' then call week ones Monday data.

I tried inserting another HLOOKUP function into the formula but it wouldn't work. Any ideas?

Hi Jeff

That's perfect, it's exactly what I need. Thankyou! I've tried changing the formula but I can't seem to get it to function. I've been fine up until this HLOOKUP function was needed and now I can't do it without your guidance haha. It feels like being a complete novice again.

I've adapted it to =HLOOKUP($L$16,OFFSET(Capacity!$A$23,MATCH($L$14,Capacity!A23:K25,0),0,2,11),ROW(A2),0) by analysing where your formula was pointing to and myself pointing to the relevant data. However due to simplicity I've had to alter the layout of my current workbook and it's not quite in the same format.

I would happily show you the file but as I mentioned earlier in the thread it's 56mb and that ghost data thing you mentioned hasn't seemed to work yet.

I've replicated the layout of the Capacity sheet on this workbook. I changed the '$A:$A' to A23:K25 as this is where was located. My apologies for ruining your code, it worked beautifully until I got my hands on it!

Book2.xlsx

You are my new hero! I physically can't thank you enough. It also won't let me add any more reputation. Thankyou, you've just saved me using a total of 22 Macros!