The new forums will be named Coin Return (based on the most recent vote)! You can check on the status and timeline of the transition to the new forums here.
The Guiding Principles and New Rules document is now in effect.
More Excel Auto-Populate Help!
The Crowing One
Registered User regular
Registered User regular
This is a follow-up to an older thread of mine, the relevant part deals with Excel formulas and, specifically, how I can get an auto-populate onto a separate tab for contact dates more than a week old. I.e. I need to auto-populate a tab from the main tab with every client that hasn't been contacted in over a week.
I plugged in the below formula, but it seems something is missing. I want to, hopefully, be able to put in a single value and not have to re-enter it each week.
=IF((TODAY()-Sheet1!$B2)>7,Sheet1!A2,"")
Size and bold emphasis is where I think I could be going wrong.
The second is that I need to be able to check multiple cells for pulling the data.
Thanks so much,
Crow
I plugged in the below formula, but it seems something is missing. I want to, hopefully, be able to put in a single value and not have to re-enter it each week.
=IF((TODAY()-Sheet1!$B2)>7,Sheet1!A2,"")
Size and bold emphasis is where I think I could be going wrong.
Checking to see if Today's Date is more than 7 days after the Last Contact Date and then deciding to either pull that information into the sub tab or generate a blank would give you a formula like this: =IF((TODAY()-Sheet1!$B2)>7,Sheet1!A2,"").
You can also check multiple cells in deciding to pull data or blanks, but I can go into that if you want.
Let me know if you have any questions or something doesn't seem right.
The second is that I need to be able to check multiple cells for pulling the data.
Thanks so much,
Crow
The Crowing One on
0
Posts
Is there any way to protect and lock the cells AND be able to still sort?
see http://www.cpearson.com/excel/datedif.aspx
I find it very useful, if you ever need other excel help don't be shy of dropping me a PM. About half of my job is financial and economic modelling so I pretty much live on planet excel
=DATEDIF((TODAY()-Sheet1!$B2)>7,Sheet1!A2,"")
I know that's wrong, but I may need a little spoonfeeding. Thanks.
Second question is that I've been using
=IF(Full!$C2="Approved - Permanent",Full!A2,"")
to pull all "Approved - Permanent" status into a new sheet. What would I do to change it so it would pull both if it were, say, "Approved - Permanent", "Active" and a few others?
Encapsulate the others in your if statement, like so:
=IF(Full!$C2="Approved - Permanent",Full!A2, IF(Full!$C2="Active",Full!A2,""))
At least I think that's how you do it (I use other tools for long convoluted nests of if/then's)
http://www.techonthenet.com/excel/index.php
Origin: theRealElMucho
If I'm understanding correctly, you want to pull a line of data into a new sheet if it has "Approved - Permanent", or "Active", or a few other values all in one cell? Or do you want to only pull data if it has "Approved - Permanent" in one cell and "Active" in another cell, and something else in another, etc? Or is it some combination of both?
As for your first problem in your OP, I'm not sure I'm clear on the issue yet... but let's sort this one out first.
So I need to get it to populate over if it's "Approved" or "Approved - Permanent" or "Active", etc.
That's really easy:
=IF(OR(Full!$C2="Approved - Permanent",Full!$C2="Active",Full$C2="AnythingElseAtAll"),Full!A2,"")
The OR formula will satisfy the IF if it's A, or B, or C, or D, or whatever. So if any of the tests within an OR is TRUE, it will return TRUE for the purposes of the IF Formula.
I guess the last tricky point would be getting it to populate if a date is over 7 days past? I'd prefer (with my lack of understanding of excel) not to have to change the code each time I run the report.
Spoilered because I really may be on the wrong track here:
For the report that looks at the date, I guess you'd have to enter that formula and fill down/across each date... It all depends on how you're using the sheet, I guess.
For example, I'd have one Master sheet with the Full tab (full data) and then additional tabs for each type of "pull" I want to make. For example, Overdue tab (pulling files that are >7 days), Approved, Active, Etc. tab (pulling files meeting the new formula we just made), Canada tab (for all files that are located in Canada), etc. These sub tabs would be set up "large" enough so that the pulling formula would capture all the data in the Full tab as well as have room for expansion of the tab (if Full is 1000 rows, the sub tabs would have the pulling formula in 1500 rows or whatever); either that, or I would remember to always expand the formula area in the sub tabs to match the Full tab.
Then, when I needed to "run" a report, I would sort the appropriate sub-tab, culling out the unnecessary bits of info, and then save a copy of the report, leaving the Master sheet unchanged.
In this way, you wouldn't have to re-enter the formula or fill down or whatever when you wanted to run the >7 day report on Tuesday after having already run in on Monday.
What kind of error? A #VALUE! error? I got that when =IF((TODAY()-Sheet1!$A2)>7,Sheet1!A2,"") wasn't actually pointing at the right column that had Dates in the source sheet. Otherwise, it works just fine for me. It's hard to diagnose what's going on...
It returns every cell as "0"? Hm...
EDIT: *facepalm*
It was returning zeros because I hadn't entered the data on that particular sheet. It works like a charm! Thank you!
Ah.
Glad to hear it's working. Feel free to let me know if you have any other questions.
The other thing you can do, to "hide" the pulled data when there's nothing entered in the source sheet (returning blanks instead of those zeroes) is wrap the whole formula in another IF that checks to see if the source cell is blank. The >7 day check formula would look like this:
=IF(FULL!$G3="","",IF((TODAY()-Full!$G3)>7,Full!A3,""))
You can do this for any of your formulas, actually. It checks to see if the source cell is empty. If it is, it returns a "blank". If it's not empty, it does the work of the rest of the formula, checking to see if it should pull the data over or not.