What about ISNA?

You can create 2 rows/columns one with the NA values for the graph and the second is just a copy of the first but with NA replaced by "", you can then just hide the first row/column

Re: Graph titles not updating despite coming from an updated formula. I thought it was because I had somehow overridden the title cell by changing font etc, but I'm pretty sure it happens only if you click within the title. If you just select the title then change font/format/colour, it has worked fine.

Hopefully a simple one:

I'm going to get a bunch of sporting results, which can be trivially transformed into a format where I have columns of date (probably also have an indexing column), player, opponent, result, and then I want to have a column for a player's rating (basically Elo), for which I'll need to look up each player's previous rating from earlier in the table and grab the latest one, or return a default starting figure if a lookup returns no values. I'm thinking a vlookup on the player column will fail as it won't be able to find a single value most of the time, but as my data will be sorted I should be able to make a formula to return the last value found?

Edit - I'll have two rows for each match so any lookup will only have to search one column

I'm fairly sure that vlookup finds the first value that meets the criteria, but index/match find the last value. There're other ways to solve this kind of problem, but this seems the simplest to me.

More from the world of 'weird Excel problems': I used to be able to click on 5 sheets in explorer and open them all at once. Now, it loads 5 different copies of Excel with one in each. This is a problem because I can't move a sheet from one into another (without making a new tab and copy/pasting, which would take forever, as I have 700 odd sheets). Any ideas? I'll use VB meanwhile I guess.

In the unlikely event anyone gets this thing too: VB didn't solve it, but I found a workaround, where if you open Excel first, then go to Open, then select several sheets at once, it will open them in one copy of the program again.

I have up to 3 different dates in their own unique columns which correspond to a specific value on that date. I want to chart the "values" but by summing them on a dated timeline (preferably manually set).

Example:



So with this, I'd want a timeline from something like 11/20/17 to 1/15/18 that plots the sum of all values on each date. So 11/24/17 would show $31.77 coming in. I don't need to tie them to clients or anything. Just an overall tally for each day.

Is this easy to do without creating helper data columns?

I'm going to assume this table is on a sheet named "Data" and customer ID is in A1.

I'd make a new sheet with Column A titled Date, Column B titled Sum (Or Sales, or whatever it is)

Depending how many dates you have you can either manually put them in this column, or if you have a lot, fill down by putting in the first date in a2, and putting following formula in A3, and dragging down.

=a2+1

in b2 enter the formula

=sumif(Data!b:b, a2, Data!c:c) + sumif(Data!d:d, a2, Data!e:e) + sumif(Data!f:f,a2,Data!g:g)

Fill that formula down. If you manually put in the dates you're done, if you did the + 1 so you have all dates (and probably a few zeros) you can either just filter and hide those rows, or copy paste as values, and delete the rows with zeros.

Right, that's what I meant by the helper columns that I wanted to avoid (which I'd already built). I guess it's probably the easiest, though. Thanks for the response.

I have a column with numbers, some single-, some double-digits. I want the single digit numbers to look like " n", with a space in front, so that all numbers are properly aligned when I post them as (fixed-width font) text file. What cell format do I use to accomplish that? I'm currently using leading zeroes, but don't like the result for readability. Thanks in advance.

would:

define as string
check length
if 1, concatenate with a leading space

work?

I have an excel document that someone made for me using macros. It's essentially different player positions and ranges to play vs that spot. The was it's currently set up is a 13*13 grid with all the different hands in it. I am wondering if it's possible for me to edit it so that when I click bb vs button open for example, that I would be able to have an image show up as opposed to the grid.

Thank you

Hi all - probably a simple problem to solve but i'm stuck right now on this!

Have an excel workbook with 4 sheets currently for data for 4 classes exam results (and various formula working out averages and other things).

Would like a fifth sheet that takes the data entered (student name, results for various tests, other info etc) from the other 4 sheets and displays it in a long list. Each sheet I want to take data from has the same table of results and is formatted exactly the same. [so basically i want it to 'copy' the first 30 rows from sheet 1-4 and put them in the first 120 rows on sheet 5]. Obviously I could just copy/paste, but I want it to automatically copy it across each time sheet 1-4 is filled in.

I have tried to use the consolidate tool but that didn't seem to do what I wanted?

Help please!

Maybe someone can direct me on how to create this:

I have zero experience with Excel( if someone wants to knock this out i would be eternally grateful)

My wife has an Extensive inventory of high end beads she need to inventory.

each page will have a header that she can type a generalized description:

"Loom beads, seed size 8" for example (Left justified but add about 10 spaces from left margin)

Each page will have 3 columns
the top of the following page will be a continuation of the bottom of the previous page.

(ignore dashes this is the only way i could get it to format here)
Quantity ------------Color Number----------Description
0-999 ----------------8 alphanumeric---------Typed description


The "color number" (column 2) needs to control sorting, BUT the whole line to include columns 1(quantity) and 3 (description)for that line need to move with it .

The "color numbers" are in a number of formats
Numerical
34
Alpha Numerical in various forms:
X345
XX345
3456X
X34567X

She would like to sort first by the first numeral in the item number
then start to consider the Alpha in this order
345
345X
X345
XX345
X345X


Column 1 needs to accommodate 3 digits at most (0-999)
Column 2 needs to accommodate 8 digits
Column 3 can be set to be as wide as necessary to take up the rest of the space on that side of the page for description
there should be delineation for each line, but not each column (like lined paper)
_________________________________________________
_________________________________________________
but data in each column should be (invisibly)justified to the left in that column they all start "In Line"

She needs to be able to add a line ( a new addition to the inventory and insert it wherever she needs to and have the spreadsheet re-sort accordingly).

Finally these need to print nicely so they are easy to reference.

I appreciate any advice or direction on how to accomplish this.

Sorting is dead easy. You just select the columns you want to sort and use either the Sort or Filter function. It's very powerful and quite user friendly.

However your desired sort criteria are pretty convoluted compared to standard A-Z or High-Low. I'm sure it's technically possible, but it's going to be a challenge to implement. If possible, I would suggest adopting a new system of coding to conform to standard alphabetical sorting.

Alternatively, by using the Sort and Filter functions together, you may be able to achieve what you are looking for in another way. For instance, sort descending by quantity on hand, then filter for only the model #s that you are interested in.

I created a spreadsheet to help track some items I need to follow up on. This spreadsheet will be updated with new items and new dates often. They will all have dates for when I need to follow up on for that item.

Is there a way (maybe with conditional formatting???) that I can highlight these items based on today's date so i can easily see which items need to be followed up on today?

Ideally, I'd like to highlight items with today's date (when that item becomes one of today's todo items) in one color, the next couple days in another color, and this week in a third color. So these items will change based on the current day's date.

Right now, I am sorting based on date, but I'd rather not have to sort the entire list based on the date if I can just highlight the items based on today's date.


For example:
Follow up with John - March 1
Follow up with Bill -March 2
Follow up with Joe - March 3

When I open Excel on March 1, the "Follow up with John" item will automatically be highlighted in Red, the "Follow up with Bill" item will be highlighted in Orange, and the "Follow up with Joe" item will be highlighted in gray.

Then when I open Excel on March 2, the "Follow up with Bill" item will now automatically be highlighted in Red, and the "Follow up with Joe" item will now be highlighted in Orange, so on...


Any help is much appreciated. Thanks!

You can really do a lot with conditional formatting as you can use actual formulae. It may be possible to use the now() function if you want to highlight based on a specific proximity to the current date. Or, they have heat map options based on a number field and that may work OK with the dates.

I want to set up a new rule that means a cell will say 'N/A' every time another cell has a value of 70% or more.

If I set it up as follows it is close but only seems to work with TRUE or FALSE.

ie. :

=IF(F44>=70%,TRUE) sets the cell up to say TRUE. In this equation I was hoping I could just change the word TRUE to N/A but it doesn't work.

It should definitely work. Try:

TRUE and FALSE are recognized logical functions in Excel and N/A is not, thus, it needs to be a text value put in quotes like Spurious said. Alternatively, NA() is a function, so you can do =IF(F44>=70%,NA(),F44) as well, and it'll return #N/A and mark itself as a verified N/A. This allows you to count the N/As or run logical formulas on them with ISNA function.

Is it possible to partially fill in a cell? Ex 50%,75%,etc?

Conditional formatting -> data bars

Why do you continue to use this **** software?

Thank you, that's perfect!