I do appreciate the other responses. There is some pivot tables manipulation that you shared that I haven't done in years.
The reason I am working on continue to clarify on the formula response from from Hui is because there are no excel table equivalents when I port over to other applications...
Thanks for the clarification. One more question that I had is how can I differentiate between the two roles for Consultant and Engineer. I found a work around where in the Capacity Tracker I had a new column that has all Consultant or Engineer across the rows where it ties to the line items I...
Hi All,
I am trying to make a monthly capacity summary that is pulling data from a very specific format within a weekly capacity tracker. I am running into challenges on how I can automate and make it dynamic across a large number of employees and over many months.
Within the spreadsheet, I...
Hi All,
I am working on trying to figure out a way to take a many to one relationship with varying statuses and only count a particular status when the lowest common denominator (meaning the status indicating the last complete) is shown. The issue is that I cannot figure out how to take out...
The hyperlink function works perfectly since there is consistency in the naming of the tabs.
I was wondering is there a way to quickly add in a way for the A1 cell in each tab to link back to the index?
I was messing around with Macros but it didn't work for me / researching additional...
Hi All,
I was wondering if there are any quick ways to hyperlink 100+ tabs without manually right clicking and adding hyperlinks.
Essentially, I have an index tab that references 100+ tabs and when clicking on the tab # it will go directly to the tab. Then, on each Tab, cell "A1" will allow...
Hi Nebu,
Thank you for your response. I have encountered this type of issue before and was trying to see if there is a way without overlaying a dummy graph? Ideally, I was hoping to be able to easily upload the graph into PPT. In the past, I have manually added in all of the x-axis labels...
Hi All,
I am trying to figure out how I can change the X-Axis from displaying 0 - 6 to instead show Buckets 1 to Bucket 6. The Buckets 1 to Buckets 6 are the column headings and I want those to be displayed. Note the values driving the length of the bar is the total column which I want to...
Hi Bosco_yip - I was able to use the red text you provided to help me modify the COLUMNS($A1:B1) into MATCH($C$4,'Raw Baseline Data'!$B$5:$Q$5,0)-1) by also changing the headings to make it exact (indicating FTE / Avg). Also, I realized why you have to use Match("Temp") since there are two...
To clarify, in this case where the formula indicates + 8 (INDEX('Raw Baseline Data'!$B$5:$Q$43,MATCH($A5,'Raw Baseline Data'!$B$5:$B$43,0)+8), is there a way to calculate the number down rather than putting a fixed number which depends on the provided data set. If the data now has the total 10...
Bosco_yip thank you for your response. I was trying to avoid using offset because the formula will then depend on the data set. I have done this in the past, but it has caused errors if the data set changes, but the headings are the same. Is there any way around this to always calculate how much...
Hi Hui - Thank you for providing an answer. I was wondering if you can help decompose the formula for me so I can understand what it is doing? I used the Evaluate Formula in the Formula Auditing Tab; however, I am still unclear. The explanation should allow me to expand the formula to use it for...
Hi,
I am receiving raw baseline data outputs in the format attached and I typically would use index match match to grab the values; however, in this scenario I am stumped on how I would grab the values.
I have attached a workbook with the same information provided below:
- Essentially, I...
Hi,
I am having issues with my excel workbook where it is constantly trying to load data from hyperlinks (e.g., SharePoint). The main issues are twofold:
(1) Where can I find the source of these automatic excel link update sources? I copied in external tabs into my workbook for...
Hi p45cal,
Thank you for the help. This definitely does exactly what I was looking for.
I had two follow up questions:
(1) For the range, how can I update it to be dynamic?
I see that there is the statement:
Set myrng = Intersect(Target, Range("B3:B16"))
I am just not sure how I could...
Hi All,
I am re-posting my question; however, with a different explanation of what I am trying to achieve.
I am trying to auto-populate cells that have data validation. The closest thing that I could find to a solution was the youtube video below...
Hi Eibi,
Thank you for the advice; however, the issue is that the data validation cells disappear using formulas. I am trying to do something more along the lines of performing a data validation of a drop down list based on a 2nd validation list.
Thanks,
JuliusV
Hi All,
I am trying to figure out how to allow a user to select certain cells and based on the cell selection auto populate select cells in the row.
I know you can make a table and use index match to populate the cells; however, my issue is that there is an order of operations for the checks...
Hi,
I tried implementing the way you stated above; however, it highlights the row above. Therefore, for customer 16, the rejected does not have any shading.
How can I fix it so that it applies to the row at hand.
Thanks,
JuliusV
Hi,
I have been trying to do the CF as indicated above. However, I am having trouble making multiple rules work.
I provided a mock of what I was attempting.
Additional steps, in completing process could help too.
Thanks,
JuliusV