Need help making an IF statement return a value from another sheet cell

[redokamiwolf]

Was trying to make a running sheet for my Church of who all prayed so we could ask other members. I am trying to make each sheet monthly and then just have it transfer the last date to the newest sheet.

As you can see in D4 it puts the date, but what I need is to have a function that if all the inputs on that sheet miss it's retroactive and goes through the other sheets for that cell and puts the date from that sheet into the new one.

As you can see in D1 formula there is a date in the December sheet but it's not returning the date just my if value (which I know is wrong) just how do I correct it to where it returns the date in that cell from another sheet?

[:) Sixthsense :)]

Please attach a sample workbook with enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).

[redokamiwolf]

Okay I uploaded a screen shot (at least I think so)

But basically as you can see in D5 and D6, I got my if statement set up right where if I put XP it'll put the date in Column D of the date it's under, but what I need is the last part of the formula shown (=IF(E2="XP",$E$1,IF(F2="XP",$F$1,IF(G2="XP",$G$1,IF(H2="XP",$H$1,IF(I2="XP",$I$1,IF(ISTEXT(December!C2),"XX",)))))) How to get the D column to update from previous sheets, since I am only doing one month per sheet, so I would like the if statement to be retroactive if the XP isn't in that month it searches the old sheets to find a date in the D column of the person and places that instead.

[FDibbins]

Six asked for a sample workbook, not a picture

Please do not upload a picture of your file...rather, upload a sample of your workbook, showing what data you are working with, a few samples of your expected outcome is (manually entered is ok) and how you arrived at that. (exclude sensitive info). Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you Also, not all members can upload picture files (Company firewalls and stuff)

[redokamiwolf]

I don't know how to do that since I work on Google docs, how do I upload the workbook then? Since I can't link you into it it has personal information on it and I don't have Excel on my computer.

[:) Sixthsense :)]

In Google Docs Click File and Select Download and select Excel format. That's it you will get the file downloaded in your system then attach the downloaded file here...

[redokamiwolf]

Alright, should be good now, thank you for helping me. But basically C2 cell on Sheet 2, I want the date from Sheet 1 to come to sheet two but I don't know the formula ending part of it.

Basically on sheet 2, I want the date to be listed in the C Column from other sheets if the current sheet doesn't fit the if statement, and left blank if no other sheet has it listed either. Basically just the most recent date they did pray.

[:) Sixthsense :)]

There is no need of nested if just a LOOKUP FUNCTION will do it for you… But please show the expected output clearly for giving exact solution.

[redokamiwolf]

I would like it for Bob on sheet 2 to display the last date prayed from sheet one C2, and as for Dave to do the same but since in 1/5/2014 it would override the 12/8/2014 that should appear.

Basically a running tab of if statements for the current sheet if the XP is in place to generate the date in the C Column and if not it would be retroactive looking at other sheets for a date in the C column to place in the current sheet.

[FDibbins]

Would this ARRAY formula work for you?

=IF(COUNTA(D2:H2)=0,"",MAX(IF(D2:H2<>"",$D$1:$H$1,0)))

[:) Sixthsense :)]

@ FDibbins,

I remember, one OP reported Array Formula is not working in Google Docs.

I have not used Google Docs so I don't know how it works. But I suspect there is a huge chance that google docs can't handle the array formula's like Microsoft Excel.