|
 Hey all if anyone can help I'd really appreciate it. Pictured is a spreadsheet with no formulae. I spent some time looking on forums but couldn't make it work. I want to automatically populate column E with the names in column B that I have switched on, and they need to be in numerical order. B is going to be much longer, like 600 entries. Pretty much what I want to be able to do is activate and deactivate names in with column A to end up with a list of the names that are on, in order, in column E.
|
#
?
Jan 23, 2021 20:32
|
|
|
|
| # ? Feb 27, 2021 18:46 |
|
|
ugly hack, use column C to copy the values that are marked in A from B to C in each row and then do this to compile them all neatly in another column https://www.myonlinetraininghub.com/excel-remove-blank-cells-from-a-range
|
|
#
?
Jan 23, 2021 21:06
|
|
|
Before working on Excel, you might want to first become familiar with that Print Screen button on your computer!
|
|
#
?
Jan 23, 2021 21:47
|
|
|
shame on an IGA posted:ugly hack, use column C to copy the values that are marked in A from B to C in each row and then do this to compile them all neatly in another column Thanks! Canine Blues Arooo posted:Before working on Excel, you might want to first become familiar with that Print Screen button on your computer! Is that an app? Is it free?
|
|
#
?
Jan 24, 2021 03:27
|
|
|
Assuming you have a reasonably up-to-date version of Excel you can do this easily with the FILTER function. Put the following formula into cell E2 and it should spit out the list you want (and update in real time): =FILTER(B:B,A:A="X") This formula is filtering the contents of column B (B:B) based off rows where column A (A:A) equals the string "X", if you need to change the columns you are looking at or the value you are using to mark whether the row is switched on or off hopefully it is clear how. If this list gets very big to avoid human error causing you massive headaches I would also recommend putting Data Validation restrictions on column A to prevent non-intended values from being entered (have a look at https://support.microsoft.com/en-us...&rs=en-us&ad=us for a basic walkthrough). LIGHTNING EDIT: Forgot to mention the sorting numerically component, you can do this by wrapping a SORT around the above function, EG: =SORT(FILTER(B:B,A:A="X")) Latin Hobbit fucked around with this message at 07:08 on Jan 24, 2021 |
|
#
?
Jan 24, 2021 07:06
|
|
|
Latin Hobbit posted:Assuming you have a reasonably up-to-date version of Excel you can do this easily with the FILTER function. It's only available in Office 365
|
|
#
?
Jan 24, 2021 15:04
|
|
|
SidneyIsTheKiller posted:It's only available in Office 365 Latin Hobbit posted:Assuming you have a reasonably up-to-date version of Excel you can do this easily with the FILTER function. THanks but it's gotta work on office 2013. It's for my coworkers to use... Still trying thanks for all the help.
|
|
#
?
Jan 24, 2021 18:08
|
|
|
Does office 2013 have the query function?
|
|
#
?
Jan 24, 2021 19:27
|
|
|
Aruan posted:Does office 2013 have the query function? Nope.
|
|
#
?
Jan 24, 2021 22:08
|
|
|
BBQ Dave posted:Is that an app? Is it free? Probably easier for you to go to the start menu, and type in "Snipping Tool." Best thing to come out of Windows Vista.
|
|
#
?
Jan 25, 2021 09:23
|
|
|
Thanatosian posted:Big Boomer energy in this thread. Sorry I was joking back. Love me the snipping tool. Btw I figured it out. Thanks everyone
|
|
#
?
Jan 26, 2021 02:41
|
|
|
I wanna know what the solution was
|
|
#
?
Jan 26, 2021 19:05
|
|
|
Ive made a button that filtered out the zeros. Had to do it at the end, instead of populating a list in real time but it works. Not what I wanted but shame on an IGA put me on the right path. Everyone is very happy with the result even though it's janky and old. Hand typing 500 addresses a day gets old. Cheers everyone!
|
|
#
?
Jan 27, 2021 21:30
|
|
|
ninjewtsu posted:I wanna know what the solution was The real solution is "get a database" Aruan posted:Does office 2013 have the query function? It should still have MS QUERY which was its precursor, but that's probably overkill lol SidneyIsTheKiller fucked around with this message at 05:56 on Jan 28, 2021 |
|
#
?
Jan 28, 2021 05:47
|
|
|
You make a couple columns with formulas that build up your ordered list into a single cell with pipe delimiters (reminder: comparative operators work on strings). Then you make a couple columns that deconstruct the ordered list into cells. It's more advanced than typical excel monkey work but anyone with "analyst" in their title should have no problem with it.
|
|
#
?
Feb 2, 2021 20:38
|
|
|
|
| # ? Feb 27, 2021 18:46 |
|
|
Here's what I came up with just doing the thing. It's been a while so I'm sure it could be more elegant.
|
|
#
?
Feb 2, 2021 21:00
|
|