excelnoviceneedshelp
New Member
- Joined
- Jun 13, 2017
- Messages
- 5
Hey I’m new to excel and have a basic understanding of most of the functions and what they do, but I’m having some problems creating a spreadsheet I’m working on.
Any Images I refer to can be found in the following link: Mr.Excel Images - Album on Imgur
I’m essentially creating an excel template where you can copy paste in data from another sheet and this template will reorganize and analyze the data. The blank template looks like Image 1 in the Imgur link. The goal is to identify and analyze the instances where the number in the pink section (far right) is negative for each month. The data is comprised of only positive and negative numbers.
In order to identify the negative values, I gave each line a reference # (far left) and used a simple IF formula in the 3 rows shown in Image 2 to display ‘Yes’ if the value is negative and ‘No'
In a new worksheet, I created 3 tables stacked on top of eachother, one for each month. The goal is to output a list of each reference # that yields a negative value in the pink columns, as well as other info from that row. I used another IF function referencing the yes/no columns for each month, and IF yes, then return the reference number. If no, return ‘null’.
Now I have 3 lists 10,000 rows long that displays null values for any positive inventory number, but shows actual values for the negatives. This is reflected in Image 3. For example, in row 17, reference #14 and indexed data appears because there is a negative value for that row in the pink columns on the original data sheet.
If I use the table filter to remove ‘null’ values and ‘DUMMY’ lines that I placed at the top and the bottom, then the charts display the full list of negative reference numbers and other info from that line using an INDEX function. The filtered tables are shown by Image 4.
Now I graphed the data on a scatter plot and am trying to reiterate the data tables in a more clear way in like a ‘dash’ view. This is displayed in Image 5. I tried to use the copy/paste ‘link’ special paste to redisplay the table on this page, but it doesn’t seem to be dynamic when the data is changed. I also tried using the OFFSET function, but the way the table is filtered it still shows all the null values, which I don’t want included. How is the best way to display this data but have it update automatically when the old data is replaced?
I also want to have the option to show/hide the little blue help bubbles with the 2 buttons I created at the top of the page on Image 5. I know this can be done through VBA or macros, but I have never used those before and cannot figure it out even when looking at other examples.
The final sheet should look like Image 6, which I ‘cheated’ on to create as the data is not dynamic. What is the best way to do this?
Sorry if I was unclear about anything, feel free to comment with questions and I’ll answer the best I can! Thanks for your time, I appreciate it! Any input or suggestions are welcomed as well.
excel, easy, macros, dynamic, table
Any Images I refer to can be found in the following link: Mr.Excel Images - Album on Imgur
I’m essentially creating an excel template where you can copy paste in data from another sheet and this template will reorganize and analyze the data. The blank template looks like Image 1 in the Imgur link. The goal is to identify and analyze the instances where the number in the pink section (far right) is negative for each month. The data is comprised of only positive and negative numbers.
In order to identify the negative values, I gave each line a reference # (far left) and used a simple IF formula in the 3 rows shown in Image 2 to display ‘Yes’ if the value is negative and ‘No'
In a new worksheet, I created 3 tables stacked on top of eachother, one for each month. The goal is to output a list of each reference # that yields a negative value in the pink columns, as well as other info from that row. I used another IF function referencing the yes/no columns for each month, and IF yes, then return the reference number. If no, return ‘null’.
Now I have 3 lists 10,000 rows long that displays null values for any positive inventory number, but shows actual values for the negatives. This is reflected in Image 3. For example, in row 17, reference #14 and indexed data appears because there is a negative value for that row in the pink columns on the original data sheet.
If I use the table filter to remove ‘null’ values and ‘DUMMY’ lines that I placed at the top and the bottom, then the charts display the full list of negative reference numbers and other info from that line using an INDEX function. The filtered tables are shown by Image 4.
Now I graphed the data on a scatter plot and am trying to reiterate the data tables in a more clear way in like a ‘dash’ view. This is displayed in Image 5. I tried to use the copy/paste ‘link’ special paste to redisplay the table on this page, but it doesn’t seem to be dynamic when the data is changed. I also tried using the OFFSET function, but the way the table is filtered it still shows all the null values, which I don’t want included. How is the best way to display this data but have it update automatically when the old data is replaced?
I also want to have the option to show/hide the little blue help bubbles with the 2 buttons I created at the top of the page on Image 5. I know this can be done through VBA or macros, but I have never used those before and cannot figure it out even when looking at other examples.
The final sheet should look like Image 6, which I ‘cheated’ on to create as the data is not dynamic. What is the best way to do this?
Sorry if I was unclear about anything, feel free to comment with questions and I’ll answer the best I can! Thanks for your time, I appreciate it! Any input or suggestions are welcomed as well.
excel, easy, macros, dynamic, table
