Forum Discussion
Inputting data to correlate with a date
I am guessing that Joe Blow's tab is named Joe Blow. If you want a cell on the master worksheet to show the results for Joe Blow on a certain date, you can use the INDIRECT function to build the reference on the fly. The INDIRECT function lets you build the formula pointing to a cell (or range) as text. INDIRECT then returns the value from that target cell.
Let's suppose that you want to return a value from Joe Blow's cell C4 today, D4 tomorrow and E4 the day after that. If Joe Blow's name appears in Master cell A2, you could copy across and down a formula like:
=INDIRECT("'" & $A2 & "'!" & CELL("address",C$4)) & ""
The formula concatenates an empty string in case Joe Blow's cell C4 is empty. Without the & "" at the end, the formula would return 0 if the target cell were empty. Potentially offsetting that good feature, concatenating an empty string will turn a number into text that looks like that number--so the trick is best to use when you know cell C4 will either contain text or be blank.
Brad Yundt I think I understand what you mean, but unless I am mistaken I don't think that will help me the way I have the spreadsheet set up. I'll try to use a better example.
Date 1 2 3 4 5 6 7 8
Alan
Bob
Chris
David
The way I have it set up, Alan would click on his name today (the 1st of the month), which would hyperlink to a simple list of questions. These questions would result in a single digit overall "score" for that day. That score would populate under 1 just using the "=!AlanC5" for example. This all works fine. The problem comes when tomorrow Alan clicks his name and gets the same set of questions, but potentially resulting in a different overall "score" - how do I get the master document (shown above) to put the new score in box 2 and not change box 1?
Hope that all makes sense! I am happy to link what I have created so far if that helps.
- I’m so sorry to inform you that no amount or quality of explanation would equal the clarity of a sample file.
Twifoo Please see attached file. This is an early work in progress to show the current problem. All I would like to demonstrate is Alan logging in today and filling out his information, this should be saved under the correct date in the master tab. When Alan logs in again tomorrow, the data should be saved under the new date but the old data/date should not be changed.
To achieve your desired results, you need VBA, which is not my forte. Certainly, the other experts here can help you with that.
