Trying to have the cells in a table represent 365 days and have them pull data from a prev

[crobertstsl]

I work with people with special needs. We as a mandate have to keep information tracked on behaviors, goals, and medication use. I have a basic workbook set up that I created from the excel online survey option. My hope is to have one place where all the data can be compiled so that we only have one place to look.

Currently I am having some issues getting some of the other sheets in the workbook to populate with data from previous cells. I have a sheet that has 4 individual pages, each having 1 - 4 tables on them. I need these tables to represent 365 days of the year. I then need these individual tables to pull from a previous sheet, search for a certain date, match it with the cell that represents that date, and if another column in that row has a certain value (yes/no) then i need it to input the data in a 3rd column (initials) into the table that is on the page with 3 other tables beside it.

This is simply done with if(and with a lot of typing and forumula placement. However, there are 3 other tables that pull similar data, but get information from different columns. It would still look for the date first, then in that column find the yes/no (which is a different column than the previous) and if yes it will put in the initials. Of course if answer is no then it will leave it blank.

Is there any way I could get help on this? this may be something that would be better in say a google hangout or teamviewer where I can share my screen with you and you can see exactly what I'm talking about.

Thanks in advance

Chris

[Richard Buttrey]

Hi and welcome to the forum. Unfortunately your post does not comply with Rule 1 of our Forum RULES. [B] Your post title should accurately and concisely describe your problem.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[crobertstsl]

This is a non descriptive form of the workbook that i'm working with.

as you can see i've used a couple types of formulas. I however couldn't get vlookup to work with the type of table and data structure that I have.

[Richard Buttrey]

Hi,

It's by no means clear which sheets/cells you are referring to. You need to accurately describe where your data is, and where you expect to see your results. In the results area you should manually add some typical calculated answers and explain in a note how you arrived at the result.

In other words we need to see a before and after situation.

[crobertstsl]

Sorry I thought I had uploaded the one where I put notes on it.

Here is the one with notes added (actually merged cells and typed cause comments are hard to see for me)

[Richard Buttrey]

Why does your note say that the 6 values in R3:R13 on BDS are correctly filled when there are
a) only two records on the Survey sheet, and
b) neither of those two records are dated March which is what column R represents?

[crobertstsl]

That is just an example of what it should look like when filled in correctly, minus the highlight of course. That is where I am having issues, with getting information from the previous sheet to plug into this sheet without having to spend a lot of time typing out individual formulas for each table. Which then cause me to have to go into the sheet every day to make sure that it is lining up correctly.

[Richard Buttrey]

Hi,

It always helps if you can accurately show the results you expect rather than some arbitrary results.

I've added formulae to the 2nd Table. I've also changed the dates to formulae in P3:AA3 and these use their source year from C1.
P2 is also an important cell since it tells the formulae which column to use on the Survey.

See attached

[crobertstsl]

That looks exactly like what I need it to do. I'm curious, do I have to label each table with a number now? for example you have 2 labeled as 2 above it highlighted, and i'm assuming I have to do the same with 1 and 3 and 4 etc?

[Richard Buttrey]

Hi

Yes indeed. You'll see a reference to P1 in the current formula. In the third table you'll need to enter 3 in AC1 and then change the reference to AC in the 3rd table.

You could I suppose simplify it by entering 2 in P1:AA1 and 3 in AC1:AN1 etc, and then just change the $P$1 in the formula to P$1 so that the column becomes relative. Then you can just copy the formula from table 2 to all the other tables without any further modification.

[Richard Buttrey]

Hi,

I'm also adding a second method if you are prepared to capture your data in a different layout - sheet Survey2. This uses a Pivot table which is much more flexible and obviously doesn't need any formulae.

[crobertstsl]

Ok I have tried several attempts at getting your code over into our original document. I can get it all to transfer except for the first page where you have written in cell E26

Please Login or Register  to view this content.

It keeps returning an N/A and keeps my bds sheet from filling out. Is there another calculation that you did somewhere else, or a different format of number maybe that would be preventing me from using this code? I'm going to upload my actual document, it still has no information on it yet but it will atleast let me get the help I need on copying over your formula to make it work.

[Richard Buttrey]

Hi,

Ignore the 2 in E26. I was just using that as a test. You can delete it.

The only thing I did was convert your Survey1 data from being a Table to just being a standard range. The MATCH() function in the BDS formulae objected to the table labels for some reason and I didn't have time to fathom out why. I'm not in any case a big fan of the Table functionality since I just think it complicates the look of formulae which use it and unless there's some over-riding reason to have a table I don'g bother.

[crobertstsl]

Ty so much for your help.
Is this an easy conversion? And will it effect the actual survey populating?