|
factorialite posted:I have a crazy question. You need a custom function to spit out the worksheet name on a worksheet. Here's how to do it: http://www.ozgrid.com/VBA/return-sheet-name.htm Once you have that, you can use that in your lookup functions.
|
# ? Oct 9, 2010 00:32 |
|
|
# ? May 11, 2024 16:37 |
|
TheAngryDrunk posted:You need a custom function to spit out the worksheet name on a worksheet. This is a lot of help, but I'm not there yet. If I wanted to grab detroit's rating while on the chicago tab, I would enter ='Detroit'!a13. Now, I need to use the LOOKUP function to grab the value to the right of whatever the tab name that sheetname() gives me. I've got the sheetname() function but I don't know which lookup to use. I thought it'd be VLOOKUP but I can't specify across sheets with that, can I? I'm imagining that INDIRECT will come into play, but I'm not sure how to combine the two successfully. Thanks again for both your expedient response and any other help you might give!
|
# ? Oct 9, 2010 00:57 |
|
The vlookup range can be on another sheet, sure. =VLOOKUP(SHEETNAME(),'Alpha Tab'A:B,2,FALSE) Or something along those lines should work.
|
# ? Oct 9, 2010 07:49 |
|
I've got data set up like so:code:
Would there be a way to do this with one master macro, rather than having a macro for every department? I'm not really familiar with VB, but any sort of guidance would be appreciated.
|
# ? Oct 9, 2010 22:01 |
|
Not the most efficient way, but it will do what you've asked:code:
|
# ? Oct 9, 2010 22:15 |
|
gwar3k1 posted:Not the most efficient way, but it will do what you've asked: It was failing, but I see what the problem is. This should work okay, thanks! Ok, no, it's still failing. It's giving 'subscript out of range' after a few rows, and it's skipping the first two sheets. I thought that maybe it was checking the object name instead of what the sheet was named, but it doesn't seem to be the case. kitten emergency fucked around with this message at 23:13 on Oct 9, 2010 |
# ? Oct 9, 2010 23:04 |
|
serewit posted:It was failing, but I see what the problem is. This should work okay, thanks! I've made the assumption that your department sheets are named exactly the same as the department code. Also, I have made the assumption that there is a sheet for every department on your master list sheet. I bet its saying subscript out of range because a sheet name doesn't match / the intended sheet doesn't exist. Can you provide an example of department code and sheet name, and say which line is causing the exception (highlighted yellow ehen you click debug).
|
# ? Oct 9, 2010 23:26 |
|
gwar3k1 posted:I've made the assumption that your department sheets are named exactly the same as the department code. Also, I have made the assumption that there is a sheet for every department on your master list sheet. code:
code:
ed: the sheets are set up as follows code:
|
# ? Oct 9, 2010 23:39 |
|
serewit posted:stuff It needs to be run from the main sheet. Here's some ammended code: code:
|
# ? Oct 10, 2010 00:02 |
|
gwar3k1 posted:It needs to be run from the main sheet. Here's some ammended code: Works brilliant! Thank you! edit: would you care for a plat upgrade? let me know - username at gmail dot com. kitten emergency fucked around with this message at 00:17 on Oct 10, 2010 |
# ? Oct 10, 2010 00:11 |
|
edit: ^^^ No thanks, just glad I could help someone for a change. I'm always asking. Not really a coding question, but I have a template that does multiple functions (Add client, edit client, etc.) and I want the users to be able to select these functions from the ribbon. Calling the template from the ribbon opens the template and not a regular copy of it. How can I make the ribbon force template functions to open as non-template functions? Or should I just disable saving (they shouldn't require the need to save anything anyway). This is an example line from the ribbon customizations: code:
gwar3k1 fucked around with this message at 21:23 on Oct 12, 2010 |
# ? Oct 12, 2010 21:20 |
|
Thanks for the help guys, I'm very nearly there! My next problem is a really weird one. I've got the sheetname(a1) function defined, that's no problem. I think I've got the vlookup function correct (I use =vlookup(sheetname($a$1,true),'Master'!$a$1:$b$33,2). This actually does pull the data I want it to... most of the time. If I have this data in master: pre:Chicago 1 Detroit 2 Minnesota 3 Green Bay 4 NY Giants 5 pre:Chicago 1 Detroit 2 Minnesota 4 Green Bay 4 NY Giants 5 pre:Chicago 1 Detroit 2 Minnesota 3 Green Bay 4 NY Giants 5 Dallas 6 pre:Chicago 1 Detroit 2 Minnesota 6 Green Bay 6 NY Giants 6 Dallas 1
|
# ? Oct 14, 2010 05:58 |
|
factorialite posted:=vlookup(sheetname($a$1,true),'Master'!$a$1:$b$33,2, false) When you don't specify a last argument for a vlookup, it defaults to "True". This makes it look for an 'approximate' value, which gives you weird results if your list isn't in alphabetical order. Setting it as 'False' makes it look for an exact match, and you don't have to reorder anything.
|
# ? Oct 14, 2010 06:09 |
|
esquilax posted:Try changing your formula to: false gives me the N/A error. If I do sheetname(a1,false), it still works, so it likely isn't the sheetname function that screws me up. If I have to alphabetize, it's not optimal but it's doable for sure.
|
# ? Oct 15, 2010 04:30 |
|
factorialite posted:false gives me the N/A error. If you're getting an N/A with the last argument set to 'false,' but it works with 'true' then the exact value you're looking up isn't on the list. Also, make sure your formula has expanded to include the new data.
|
# ? Oct 15, 2010 19:32 |
|
nevermind
IT Guy fucked around with this message at 15:53 on Nov 1, 2010 |
# ? Nov 1, 2010 15:32 |
|
I've got two questions I could really use a hand on. First, i've got a pivot table setup with a filter for Invoice Numbers and the need to select multiple items. When just one invoice is select, the drop down box shows that invoice number. If more than one is selected it just shows (Multiple Items). Is there a way to get a copy of the multiple items select to print on the report without manually typing them in somewhere? Secondly, I also have a Data Validation list with hundreds of choices referencing data in another sheet of a workbook. Is it possible to get it to filter down when someone clicks that cell and starts typing instead of having to scroll through the list by hand?
|
# ? Nov 2, 2010 20:25 |
|
Not sure I understand the first question, but the answer to the second one is 'no.' You can stick a combo box on the worksheet and it will fill in the first item on the data validation list that starts with the same letter, but you can't do it with a cell.
|
# ? Nov 2, 2010 20:48 |
|
TheAngryDrunk posted:Not sure I understand the first question, but the answer to the second one is 'no.' You can stick a combo box on the worksheet and it will fill in the first item on the data validation list that starts with the same letter, but you can't do it with a cell. Didn't think so Oh well. In response to the second, here are some screen shots to hopefully help explain. When you select one item to filter by, it shows the item in the list like in this picture: When you select multiple items to filter by, it shows (Multiple Items) We then have to manually type in the following row those items. I'd like to avoid them having to type those in and have it print out the selected filter items somewhere.
|
# ? Nov 2, 2010 21:12 |
|
I don't know if you can do that easily. You may be able to access the filter criteria via VBA and display it on the sheet.
|
# ? Nov 3, 2010 04:08 |
|
Quick and dirty solution in VBA. Create a module, create this sub:code:
code:
Sub Par fucked around with this message at 16:37 on Nov 3, 2010 |
# ? Nov 3, 2010 16:33 |
|
Sub Par posted:Quick and dirty solution in VBA. Create a module, create this sub: Sub Par, thanks for that, it does what I need but has a small quirk. It's pulling in random Timesheets that were used previously for testing, but that is no longer listed anywhere in the work book. See the picture for the filter items available and what it's printing. Is there a way to clear those out somehow?
|
# ? Nov 3, 2010 17:24 |
|
Did you refresh the pivot after clearing the data out of the workbook?
|
# ? Nov 3, 2010 19:33 |
|
For my job I'm taking a bunch of words written in a phonetic alphabet, and converting them to another phonetic alphabet. Essentially this is the same as transliteration. First, I looked for some sort of transliteration module or such, but they're all preset for Latin<->Cyrillic and aren't customizable in the least. Second, I found some selection.replace macros for Excel, which do what I need except for one thing, I'm dealing with unicode phonetic symbols. For example: code:
What I need this code to do, it take something like "tɬ" as input, and output "ʟ̣" I could normally just use the Find/Replace window but I need to do this with 100 separate worksheets. Alternatively if there was some sort of easier way to accomplish this, that would be great too!
|
# ? Nov 3, 2010 19:42 |
|
IceHawk posted:For example: Does simple concatenation work: "ChrW(&Hxxx) & ChrW(&Hxxx)" code:
code:
gwar3k1 fucked around with this message at 19:57 on Nov 3, 2010 |
# ? Nov 3, 2010 19:52 |
|
gwar3k1 posted:Does simple concatenation work: "ChrW(&Hxxx) & ChrW(&Hxxx)" Excellent. code:
Except I don't quite understand what your second macro does (I'm a Linguist and all I have is one year of computer science under my belt), all of my data is formatted so that column C contains the empty fields that I need to put the translation into, and column E contains the source.
|
# ? Nov 3, 2010 20:23 |
|
IceHawk posted:Except I don't quite understand what your second macro does (I'm a Linguist and all I have is one year of computer science under my belt), all of my data is formatted so that column C contains the empty fields that I need to put the translation into, and column E contains the source. Sorry, the macro assumes you have a sheet named "TransLookup". On that sheet you have a column (A) which contains your original characters, and a second column (B) which contains the characters that col A should be changed to. I'm assuming your task is as simple as "change xx to yy" without any conditions (i.e. not accounting for "change xx to yy but if xx preceeds xxz then change it to ccc"). The macro loops all the worksheets in your book that aren't TransLookup (which it is using as reference) then it is making the replacement for every replacement you have instructed it to do (all rows on TransLookup). Does that make sense?
|
# ? Nov 3, 2010 20:49 |
|
gwar3k1 posted:Sorry, the macro assumes you have a sheet named "TransLookup". On that sheet you have a column (A) which contains your original characters, and a second column (B) which contains the characters that col A should be changed to. I'm assuming your task is as simple as "change xx to yy" without any conditions (i.e. not accounting for "change xx to yy but if xx preceeds xxz then change it to ccc"). Yeah that does, thank you very much. And it greatly simplifies matters!
|
# ? Nov 3, 2010 23:04 |
|
Sub Par posted:Did you refresh the pivot after clearing the data out of the workbook? I did fresh it but that didn't help. After some screwing around with it this morning I tracked down the problem. Under the Pivot Table Options, and the Data tab there is a drop down for "Number of Items to retain per field: Automatic" I changed that to None and it updates correctly now. Thanks for all of the help, this sheet will now work wonders for us.
|
# ? Nov 4, 2010 14:27 |
|
What is the best way to check for matching cells in a range using VBA? Example: I have a range of 10 cells, I want to check all 10 cells to see if any are the same. e. Actually, I'm just going to post what I'm trying to do because I don't have a clue on how I'm going to accomplish this. I have a table like so: I want a msgbox to display if more than 1 cells in column B are "yes" AND the cells in column A (within the same rows of those cells in B that are "yes") are different dates. Nothing needs to happen if the dates are the same and nothing needs to happen if the cells in B are "no" This should be pretty easy but I have no idea how to do this. e2. So I think I may have an idea how to do this but I'm not sure on the syntax if someone could help me with this. code:
IT Guy fucked around with this message at 17:13 on Nov 4, 2010 |
# ? Nov 4, 2010 15:44 |
|
Here's a sub that'll do what you want (identify duplicate positive dates):code:
|
# ? Nov 4, 2010 18:46 |
|
gwar3k1 posted:Here's a sub that'll do what you want (identify duplicate positive dates): Actually, I made a mistake, I need to check array2 to see if every value is the same or unique. Will that still work? After Googling around, it appears the FREQUENCY() function may do what I need, I just don't know how to use it properly.
|
# ? Nov 4, 2010 18:56 |
|
IT Guy posted:Actually, I made a mistake, I need to check array2 to see if every value is the same or unique. No, the sub I posted will store a list of unique values and return true if there are any duplicates. You can validate by doing: code:
code:
|
# ? Nov 4, 2010 19:14 |
|
gwar3k1 posted:No, the sub I posted will store a list of unique values and return true if there are any duplicates. You can validate by doing: Thanks for your help on this. I don't think I'm explaining the situation properly or maybe I'm being dumb and not understanding what is going on here. It is alright if the dates are duplicated but if there are more than 1 distinct values then that is the problem. I'll try to clear this up a bit. I need to do this: code:
IT Guy fucked around with this message at 13:50 on Nov 5, 2010 |
# ? Nov 5, 2010 13:48 |
|
Finally got it doing what I want. Here is how I did it: code:
|
# ? Nov 5, 2010 15:37 |
|
I don't think inColl does what you think it does.
|
# ? Nov 5, 2010 18:02 |
|
Zhentar posted:I don't think inColl does what you think it does. I noticed that too. How should I fix that? So let's say I choose Nov3 in the first cell inColl will return false because there is nothing in the collection. I choose Nov4 in the second cell. inColl returns false because Nov4 <> Nov3 I choose Nov 3 in the third cell. (here is the problem right?) inColl will be assigned true when it sees Nov3 = Nov3 the first item in the collection, but then when it checks it against the next item in the collection it will return false because Nov3 <> Nov4 However, the sheet seems to be working so I'm confused again. Is this what you were referring to? IT Guy fucked around with this message at 18:31 on Nov 5, 2010 |
# ? Nov 5, 2010 18:05 |
|
Yeah. You want to do something like this: code:
|
# ? Nov 5, 2010 18:47 |
|
I would have posted a modified version of my sub, but Zhentar's is much better.
|
# ? Nov 5, 2010 19:00 |
|
|
# ? May 11, 2024 16:37 |
|
Hey thanks, both of you.
|
# ? Nov 5, 2010 19:43 |