Quick Excel Help Page 6

  • localnotail 15 Jul 2009 14:38:38 23,072 posts
    Seen 8 years ago
    Registered 13 years ago
    excellent. thank you very much, I thought it didn't work for a while and then I remembered that I have a header row.

    \o/ wow, excel is like magic.
  • Lutz 15 Jul 2009 15:01:10 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Personally I'd have done a vlookup up for that, to save your self masses of nested IF formulas, and allowed quick editing should you want it more accurate, and/or different depreciation values.

    On seperate tab:

    Column A:

    New
    Very Good
    Good
    Fair
    Rough
    Very Rough
    Dead

    And in B put in your values (1 for New, 0 for Dead, and scale as you wish)

    then use a vlookup in your "value" column to look at your text, look it up on sheet 2 column A, and return the value of column B.

    If it were me.
  • localnotail 15 Jul 2009 15:37:03 23,072 posts
    Seen 8 years ago
    Registered 13 years ago
    I remember using vlookup in my last job but I have forgotten how.

    I dunno, it's pretty simple as Twinkle wrote it. I just want to use it for a basic valuation expectation thing, and a guide to what price to start selling all this stuff at. There are only 5 values involved, it's not that badly nested)

    I'm probably going to end up over-writing some of the valuations anyway, as some things depreciate more than others.
  • Lutz 19 Aug 2009 14:18:13 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    *snip*

    Ignore me.
  • gang_of_bitches 23 Sep 2009 14:02:23 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Once again I call on you great excel gods.

    I've checked several of the usual excel help sites and no joy. Basically I've got 2 columns of data sorted by month for 2008 (column a) and 2009 (Column b) with a Year on Year % change in column c. But I want a running average % change YoY that ignores months that haven't happened yet which currently all show as -100%. I'm sure its pretty simple, but I'm buggered if I can do it.

    lots of love,

    stupid head.
  • X201 23 Sep 2009 14:04:05 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Not tested but

    Sumif date( less than) now




    EDIT: bloody forum and no arrows :(
  • gang_of_bitches 23 Sep 2009 14:18:01 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Thanks x201

    @blizeH - excel is for hardcore unformatted data. If you want pretty colours I suggest you try Powerpoint.
  • gang_of_bitches 2 Oct 2009 10:46:48 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Back again, you guys are way too good not to ask for advice.

    So I'm putting data into a template. Some of it's just a copy and paste job, but some of the cells are dropdowns, so despite wanting to enter the same value most of the time, I can't just paste.

    Is there a way of selecting the same value in a column of dropdowns without actually going into each individual cell and clicking on it? Please god say yes.

    thanks in advance.
  • Lutz 2 Oct 2009 10:53:30 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Hmm... depends how they're set up, but on every dropdown list in excel that I've seen you can just copy and paste into the cell, so long as what you're pasting is a valid entry from the drop down list.
  • gang_of_bitches 2 Oct 2009 10:58:33 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Lutz wrote:
    Hmm... depends how they're set up, but on every dropdown list in excel that I've seen you can just copy and paste into the cell, so long as what you're pasting is a valid entry from the drop down list.

    Phew. I was mis-informed and then had a little panic. thanks Lutz.
  • X201 2 Oct 2009 10:59:49 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    I've just done a test with VB and it doesn't record any type of status change for a value in a validated list dropdown. So no way of automating it.
  • Lutz 2 Oct 2009 11:02:22 48,870 posts
    Seen 4 years ago
    Registered 18 years ago
    Ah. Maybe not... I just tested it, and actually overwrote the drop down. :(
  • gang_of_bitches 2 Oct 2009 11:15:40 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Lutz wrote:
    Ah. Maybe not... I just tested it, and actually overwrote the drop down. :(

    As long as you copy from another drop-box it's fine, but if you paste the exact same value from another cell you're right, it kills it.

    thanks again guys.
  • BravoGolf Moderator 19 Nov 2009 15:34:46 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    *bump*

    Hope you lot can help me on this one. I'm trying to setup a new report document on various projects and I'd like to have one detailed tab in Excel that captures all the information and then various other tabs with give a specific bits of information.

    So, on Tab A I have, say, 8 columns, the first column being the project line.
    On Tab B I'd like to have something that says "grab all the projects from Tab A and for each of those projects only give me the following column names".

    How do I do that?
  • gang_of_bitches 19 Nov 2009 16:35:09 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    I'd love to help repay my excel help debt, but I'm not quite sure I understand what you're trying to do. It sounds incredibly simple and I can't imagine you'd be seeking help if it were as simple as I imagine?

    You want to to have one tab with a full set of data and then draw on various areas of that data in other tabs? Wouldn't you just use lookups??

    It can't be that simple....
  • X201 19 Nov 2009 17:05:18 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Validated Lookups

    They require a bit of manual tweaking to work exactly how you want them but given your description they sound like the things you need.
  • BravoGolf Moderator 19 Nov 2009 17:08:04 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    gang_of_bitches wrote:
    I'd love to help repay my excel help debt, but I'm not quite sure I understand what you're trying to do. It sounds incredibly simple and I can't imagine you'd be seeking help if it were as simple as I imagine?

    You want to to have one tab with a full set of data and then draw on various areas of that data in other tabs? Wouldn't you just use lookups??

    It can't be that simple....

    Lol, now I feel stupid. Yes, that simple really :) So I assume Validated Lookups (from X201's post) compare one entry against another, that's perfect.

    But - how do I automatically populate a sheet of projects from Tab A based on, say, their status, without repeating?

    So, Tab C is "automatically populate Column A in this tab with projects that are shown as Closed". I can then use the validated lookup, I assume, to retrieve specific data for each of those.
  • Grunk 19 Nov 2009 17:11:22 4,717 posts
    Seen 7 years ago
    Registered 16 years ago
    Not sure what you mean, do you want to create a new tab for each project or just reference one worksheet from another?

    a) Try recording a macro, then create new worksheet, then right click and rename it to the correct name.

    b) click on the second worksheet, click a cell then type "=" (without the quotes) then click back on the first worksheet and the desired cell, that will show you how to do that reference.
  • BravoGolf Moderator 19 Nov 2009 17:17:24 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    Heh, OK, I'll explain.

    I have one Excel file and would like to have several tabs, one of which is a manual entry one with lots and lots of columns. I'd then like to have several other summary tabs which present the same information in a different way (for management reporting and so on).

    So, for example, one summary tab will be a capacity management tab and I'll want it to grab all projects from the detailed tab that have projects that are not closed and then for each project retrieved grab particular columns from the detailed tab (like start and end).

    Same logic applies to other tabs: populate the sheet with projects that meet a particular status and pick particular columns for each of those projects to show.

    Hope that makes sense!
  • X201 20 Nov 2009 09:26:13 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    This code is from a small table that has peoples names in the rows and the columns contain data like age, sex, wages

    =INDEX($C$6:$F$11, MATCH("bert",$C$6:$C$11,), MATCH("age",$C$6:$F$6,))


    To make the code a bit more flexible, so that you can drag and copy it you can change the references to "bert" and "age" to be cell references. That way you can create one sheet and then copy it multiple times and then only editing you need to do is change the row heading and the formula will automatically pull the correct column back for each sheet.
  • BravoGolf Moderator 20 Nov 2009 10:51:39 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    That's really helpful, thank you. This and the validated lookups allow me to get data for any entry selected. So it's fairly explicit in that it must have a name/value to look for.

    So for now I have a drop down that's auto populated and when I select a value from that drop it will populate the remaining columns with bits of info.

    Is there a way to avoid having to explicitly select the project first and simply return all projects with a status of, say, Closed? Using the posts above I can do it per project but not automatically for all projects.
  • X201 20 Nov 2009 11:02:37 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    If you want it to only return certain data then you're going to have to use (gulp) Pivot Table Reports (cue 1950's DAH DAH DAAAAH)

    I want to state here and now, I will not be responsible for any hair-loss or rage induced damage from me pointing you towards Pivot Tables. :D


    They are really powerful things to use, but can be an absolute pain to tweak when you're setting them up for the first time.


    But they will allow you to have dynamic tables that only return data based on certain characteristics.
  • gang_of_bitches 20 Nov 2009 11:40:37 5,707 posts
    Seen 2 years ago
    Registered 16 years ago
    Pivot Tables give me the fear.
  • BravoGolf Moderator 20 Nov 2009 14:33:41 12,787 posts
    Seen 14 hours ago
    Registered 18 years ago
    lol, after several hours I too hate pivot tables :( What I want to do I could do via SQL and PHP but it needs to be Excel
  • X201 20 Nov 2009 14:36:40 22,082 posts
    Seen 4 hours ago
    Registered 15 years ago
    Only other thing I can think is a Lookup combined with Subtotals
  • Grunk 20 Nov 2009 14:45:53 4,717 posts
    Seen 7 years ago
    Registered 16 years ago
    VBA
  • MrWorf 22 Sep 2010 11:38:35 64,172 posts
    Seen 3 days ago
    Registered 20 years ago
    Oh Ye Excel Gods, please hear my prayer.
     
    My boss just chucked me a mind bender.
     
    He has a list of names in column A, many reoccurring, and
    amounts they’ve paid in column B.
    i.e.
     
    A ____
    B
    Name _ Amount
    Ann ___ £10
    B en ___ £5
    Craig ___ £20
    Ann ___ £35
     
    What he wants is a summary list of the total of what each
    person paid which pulls totals from column B. I suggested he could just use an
    auto filter and manually work it out, but he needs something that will update
    on the fly, rather than constantly checking it.
     
    i.e.
     
    A ___
    B
    Name _ Total
    Ann ___£45
    Ben ___ £5
    Craig __ £20
     
     
    Is there any way to do this?
  • Saucy 22 Sep 2010 11:42:08 2,775 posts
    Seen 6 months ago
    Registered 12 years ago
    Use SUMIF.

    On the app, otherwise I would be more helpful.
  • Clive_Dunn 22 Sep 2010 11:53:51 4,862 posts
    Seen 2 years ago
    Registered 18 years ago
    Sumif works, but a standard pivotable is perfect for this ?
Sign in or register to reply

Sometimes posts may contain links to online retail stores. If you click on one and make a purchase we may receive a small commission. For more information, go here.