*Official* Excel help thread Page 3

  • Page

    of 10 First / Last

  • Psychotext 14 Apr 2009 13:53:31 55,032 posts
    Seen 3 days ago
    Registered 8 years ago
    Shall give that a go. Thanks.
  • ruttyboy 14 Apr 2009 14:15:39 7,949 posts
    Seen 3 months ago
    Registered 12 years ago
    ming wrote:
    No, sumif is not clever enough for that. You can do it with sumproduct or by adding a column with the criteria test in it and then use that with the sumif.
    Oh never saw this ming as I'd solved the problem via another route. Thanks though :)
  • Deleted user 20 April 2009 09:50:14
    I've inserted a few pictures, one in each cell. If I choose Picture -> Size and Properties, the "Alt Text" tab will tell me the name of this picture on disk.

    I want this name in another cell as text, is there any way to do this? Have Excel dig out the "alt text" field and print it for me automatically?

    Thanks a bunch!
  • Deleted user 20 April 2009 15:31:18
    I seem to have killed the (already dead) thread with my questions. Never mind, solved it. For whoever's interested, the answer is:

    Worksheets(1).Shapes(i).AlternativeText()
  • opalw00t 20 Apr 2009 15:40:54 11,297 posts
    Seen 44 minutes ago
    Registered 10 years ago
    'Twas a bit out of my area :)

    not a force for good

  • Lutz 3 Jun 2009 09:14:31 48,854 posts
    Seen 2 years ago
    Registered 12 years ago
    Hope I'm not imagining that you can do this...

    I have a column of figures. These figures are pulled from a vlookup. I don't want to paste special values, as the column will be changing quite a bit all the time.

    However, I need to add up said column, and it contains #N/A.

    How do I get SUM to add up the column and ignore the #N/As? ATM it just keeps returning #N/A itself.
  • rutter 3 Jun 2009 09:41:28 1,919 posts
    Seen 9 months ago
    Registered 7 years ago
    I'm sure it's doable, shouldn't SUM just ignore the #n/a values?

    What format are the cells? Does it make a difference if you make them numeric?

    Alternatively, have another column alongside with an IF statement saying IF value = #n/a put 0, otherwise put value. Then SUM that?
  • Lutz 3 Jun 2009 09:42:39 48,854 posts
    Seen 2 years ago
    Registered 12 years ago
    Dunno if it should, but it isn't. The cells I've tried in general and number.
    Yeah, I did the dual column thing in the end.
  • X201 3 Jun 2009 09:42:57 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    something like sumif isna(range)=false
  • rutter 3 Jun 2009 09:43:10 1,919 posts
    Seen 9 months ago
    Registered 7 years ago
    Just tried, you're right - it doesn't ignore the #n/a. Piece of shit!
  • X201 3 Jun 2009 09:47:15 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    It evaluates the whole range and if there's one NA then if counts it as false and doesn't SUM
  • X201 3 Jun 2009 09:49:22 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    Try a column of ISNA(cell)

    and then compare it with

    =SUMIF(G7:G12,ISNA(F7:F12),F7:F12)



    G is the ISNA column
    F is the numbers to be SUMed
  • X201 3 Jun 2009 09:56:07 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    Single cell solution

    = SUM(IF(ISNUMBER(F7:F12),F7:F12))

    but you have to enter it with CTRL+Shift+Enter

    Don't just hit Enter
  • delbert 3 Jun 2009 09:59:42 2,893 posts
    Seen 23 hours ago
    Registered 11 years ago
    Or put your vlookup in an if with an iserror statement to set #N/A to 0

    e.g. if(iserror(vlookup(blah))=true,0,vlookup(blah))

    edit: or use "" instead of 0 if 0 is a valid result
  • Merlinho 3 Jun 2009 19:06:27 5,908 posts
    Seen 2 days ago
    Registered 9 years ago
    Aaarrgh Excel! Whizzes, help please.

    I'm have two spreadsheets of account balances, one from a few months ago, one as of now. I want to find the line items (rows) that are in both spreadsheets. This is made more difficult by the line items not having a unique identifier, the only way you can uniquely identify is if the data in two of the columns are the same.

    I've tried messing around with V Look ups but I'm struggling to think of an exact solution to my problem. Any ideas?
  • Deleted user 3 June 2009 19:32:28
    Use concatenate to join the relevant two columns on each sheet then vlookup to match one to the other.
  • Twinkle 3 Jun 2009 19:33:01 542 posts
    Seen 3 years ago
    Registered 10 years ago
    If for example the data is in column A of both spreadsheets, in the latter spreadsheet put in this formula in an empty col row 1 then fill down:

    =IF(COUNTIF('Older Spreadsheet'!A:A, A1) > 0, "Y", "N")

    so its just a Y/N of if the value in the later spreadsheet is in the old one.
  • Merlinho 3 Jun 2009 20:27:22 5,908 posts
    Seen 2 days ago
    Registered 9 years ago
    Cheers guys, the combination of concatenate and countif worked a treat, eventually.
  • Nexus_6 10 Nov 2009 16:25:35 3,903 posts
    Seen 2 hours ago
    Registered 10 years ago
    I am looking to do something so amazingly simple yet am getting weird results and dont know why.

    I am not an excel user usually, but i want to keep a track of hours that people in the office put in for CPD events.

    I have a column for each person, and i will add to the rows with titles each time a new event occurs. I have a total at the bottom (or top, it could be the top just as easy) and i have used

    =sum(a1:a20) to add up all the numbers in that column.

    this works for a few rows down the a column, but then i put in a number in a cell and the total changes to #

    what the fuck?
  • X201 10 Nov 2009 16:26:53 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    Nexus 6 wrote:
    I am looking to do something so amazingly simple yet am getting weird results and dont know why.

    I am not an excel user usually, but i want to keep a track of hours that people in the office put in for CPD events.

    I have a column for each person, and i will add to the rows with titles each time a new event occurs. I have a total at the bottom (or top, it could be the top just as easy) and i have used

    =sum(a1:a20) to add up all the numbers in that column.

    this works for a few rows down the a column, but then i put in a number in a cell and the total changes to #

    what the fuck?

    Widen the column
  • opalw00t 10 Nov 2009 16:27:02 11,297 posts
    Seen 44 minutes ago
    Registered 10 years ago
    The number might be too large for the cell. Make the row wider?

    Edit: Slow, and yes, column would work better :)

    not a force for good

  • Phily50 10 Nov 2009 16:32:11 2,219 posts
    Seen 22 hours ago
    Registered 8 years ago
    How do I get rid of the 'tabs' at the bottom of the sheets on Excel. I'm sure there's a way I just can't remember how to do it.

    Ta.
  • X201 10 Nov 2009 16:33:43 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    Phily50 wrote:
    How do I get rid of the 'tabs' at the bottom of the sheets on Excel. I'm sure there's a way I just can't remember how to do it.

    Ta.

    Do you mean hide or delete?
  • Phily50 10 Nov 2009 16:34:16 2,219 posts
    Seen 22 hours ago
    Registered 8 years ago
    Just hide them, but not actually hide the sheet itself, just the tabs if you get what I mean?
  • Nexus_6 10 Nov 2009 16:34:48 3,903 posts
    Seen 2 hours ago
    Registered 10 years ago
    Guys, many thanks!

    I can't believe it was that simple a problem - good effort in helping so quick!

    :-)
  • X201 10 Nov 2009 16:36:50 15,743 posts
    Seen 1 minute ago
    Registered 9 years ago
    Phily50 wrote:
    Just hide them, but not actually hide the sheet itself, just the tabs if you get what I mean?

    Move to the left hand edge of the horizontal scroll bar and drag it across so that its the width of the screen
  • Phily50 10 Nov 2009 16:38:07 2,219 posts
    Seen 22 hours ago
    Registered 8 years ago
    Damn, so simple! Cheers X201!
  • samrat65 24 Nov 2009 13:45:37 1 posts
    Seen 5 years ago
    Registered 5 years ago
    DVD to AVI converter the most efficient, easy, reliable,and user-friendly DVD to AVI converter software that allows you to back-up your DVD's as AVI files on the hard drive which can be later transfer to another media. I verry much like this converter software!It's really awesome....So, guys u can also check it out http://www.dvdtoaviconverter.com/
  • Page

    of 10 First / Last

Log in or register to reply