Hey there - thank you for the feedback. Maybe I goofed or maybe doing over VPN is acted different. After seeing your reply I tried again by changing to code to WriteResPassword:="zzzzzzzzzz" and I tried with local files and it appears to work. Thanks again!
Hello,
I am trying to run a macro that loops through files in a folder path and reports Yes/No if it has a write protect password. The script below works, but a user has to wait for each file to open and hit "esc" on the password dialog box if the file has a write protect password. I tried...
Thanks Shrivallabha! I plugged this in and it works but my concern is if I start sorting data in the table it can "break" in the sense that the total will change. Sorting will inevitably happen with our sales team as they will sort by customer or want to sort by volume or price for different...
Peter - thank you for the feedback. I do have Office 365 but do not recognize any part of your second, more elegant solution! The first solution I can follow but my concern is if the sales team starts sorting and moving data around in these tables that contain vol/price. I am trying to...
Hello,
My title describes what I am trying to do but this may not be the best approach. In the attached file, I am trying to create a formula that will give me a revenue total (Vol*Rate) for all customers where the active scenario column = TRUE and the company column = Comp Alpha. In yellow I...
Thank you Nebu, I have been experimenting with Get and Transform and getting more familiar. I set this up incorrectly. I want to be able to have an Excel table that receives updates from Get/Transform query (my 2nd tab in my example file) while also have fields to the right that can be user...
Nebu,
Thank you for the update, this test file appears to do as desired (yay!) and I am trying to re-create it. I think your update works because in the query under "changed type" you have the manually created fields included (the columns listed as forecast). How did you modify or create the...
Thank you Nebu. I am not strong with Get/Transform...how and what did you do? I cant replicate it because it gives an error trying to find the file on your machine. It looks like you did a function to expand columns (add my 3 columns for user input) on the tbl Financials query. I wasnt sure...
Hello,
I am trying to figure out a way to have a data table that both refreshes based on a Get/Transform query (power query), and also maintains user input data in fields/columns that I have added to the far right of the results query table. Is there a good way to maintain a data table with...
Haz/Alan/Bosco - sorry for the delay, for some reason I didnt get an email notice and checked in to see results. Thank you for the help these are great solutions! I rarely use the SEARCH function and never thought through solutions like these. Bosco - thank you for the Macro, I like that it...
Hello,
I have tried searching the forums for something like this and I feel like I am overlooking something easy. I have data with descriptions that include vendor names. My goal is to do some kind of lookup that looks into one table of names and if a cell contains any of those names that...
This is perfect, I just tweaked it to include a condition where a user could enter non text in the next column (so the user can put an X or enter something like "confirmed on X date so they can track what has been completed and just filter to highlighted dates).
Thank you both for your help...
Hello,
To answer your questions, for NJ it is 2 separate taxes and for WY it is both a tax and a fee. For our main data, there are other fields that flag what type of tax or fee it is that I can reference to. The ME and RI dates are odd but they are correct, for MD I had that flagged as...
Hi there,
Yes I should have stated, this is actually a file that fellow colleagues used as a tracker. I can modify and definitely need to modify the file.
What are your you thinking in the second paragraph? Have a separate cell for each unique date and just use some of the date functions to...
Ah I was thinking incorrectly that I would hit the nested IF limit. I just took your formula and used LEFT and LEN to trim off the extra space and comma. Very long but it works, thank you!
Hello,
I am looking for advice. Attached is a file that has many different deadlines for states in the US regarding tax due dates. I also have a second tab with a slightly different visual but still not good.
I am trying to think of a way to visually depict our deadlines and use this as sort...
Hello,
I was curious how I could find subsequent matches when referring to a set of data that has multiple hits across the rows? I tried searching for how to find multiple returns or how to find subsequent returns but I could not find a post that shows how to do what I think should be rather...
Wow...thank you, thank you, thank you! This is really cool and I now want to go learn more about these functions. Plus I never use the substitution formula so I will look at this more. Thank you again, I really appreciate you showing me this!
Wow I think this works and this is really cool. Although I dont really understand how those functions work. It is really the evaly and frmla functions right? If I can learn how to adjust this to a larger dataset I think this would be great!
Thank you for all your help.
Great question...it depends on the state, and also which of the 4 deadlines. The formulas in the model reference one of 2 dates listed at the very top of the model so the "rules" are already built in to the model. Most states rely on just one or the other date but look at AZ...deadline 1 is...
First, thank you for all the feedback! I see the limitation with using these "older" options in Excel. I am not opposed to user defined functions or VBA, it is just I have no experience with user defined functions and I am very light VBA user.
Attached is maybe a better example. I have my...
Thank you so much for the reply. I see the named ranges but not sure I understand how it works. Also, is there a way for it to pull in the actually formula? I could live without but that would help. Also, I will have to look into this more and see if this works....and it can be applied to...
Thank you for the reply. I am trying to make this dynamic because the model I am referencing has 100+ formulas (I just used 3 for an example.) Do you think there is a way to do this?
Hello,
Not sure I am describing what I am aiming to do. I am trying to do some kind of LOOKUP or INDEX/MATCH that returns the actual formula from somewhere else (not the value.)
Attached is an example of what I am trying to do. Right now I do many manual steps to make this work by naming...