Forum Widgets
Latest Discussions
Help with building a modelling tool
Dear Excel Community, Hope you are all keeping well ? I'm currently seeking some help in finishing off a modelling tool want to build in Excel (see attached). Essentially, the tool is to help inform material values based on 3 variables, Total Tonnage - manual entry Composition - From a selection Commodities - From a selection So in the attached workbook, the user will input the tonnage value on sheet "Model" in cell B2. They then select the Commodity values to use. This will be a choice of "low", "Average" or "High". Based on the choice made, i would like Excel to populate cells D10:D27 with the corresponding values from sheet "Commodities". The user then selects the Composition value in cell B6. As above, depending on the selection, i would like Excel to populate cells B10:B27 with the corresponding values in sheet "Composition". I would be really grateful if someone could give me some ideas or a solution on the easiest way to make this work. As always, thanking you in advance. Kind regards RajMr_Raj_CJan 25, 2025Brass Contributor26Views0likes1Comment#REF error; "not a valid cell/range reference" Help?
I was given a very broken Excel sheet that's supposed to calculate scores for our PT test (Army). There are errors everywhere. I know what the problem is, I don't know how to fix it. It should be able to take what someone earned on a certain event (deadlift, plank, run, etc.), calculate the score they got for each respective event, and total the scores up. The scores should be determined based on gender and age (per the headers referenced), find their score amongst the correct table, and output the points corresponding to said score. If I input the scores manually, it tallies it up just fine (picture- third row down: 60x4=300) So the input from the "point score" section is fine. It's the "raw score" section that's not working to find the points earned. On a separate tab on the same document, there are tables of the score charts separated by event, age, and sex. The score calculator is supposed to be referencing those. "Function INDIRECT parameter 1 value is 'MDL_F[[#Headers],[17]:[72]]'. It is not a valid cell/range reference." "MDL_F" refers to the Max Dead Lift event for Females "[17]:[72]" is the cell range of ages for that event. I am at a loss. Please help.35Views0likes1CommentExcel 365 Focus Cell - "Show Auto-Highlight" feature is disappeared
Hi everyone, I recently update the Excel 365 to and I found the "Show Auto-Highlight" feature is disappeared. From Microsoft website: Increase ease of navigation with Focus Cell in Excel | Microsoft Community Hub, this feature is very handy when using the find fuction. how can I get this function back? Thank you!joeyc380Jan 25, 2025Copper Contributor69Views1like4CommentsSearch Formula help!
Help you lovely genius people. Is there a formula to check a text string in a cell and if it contains a sequence of 4 or 5 numbers in a row return the 4 or 5 digit number? For some backstory... I'm looking at a 2 columns of data from Xero and within some of the cells there will be a Job reference, but the text strings are not in any specific order/format. I was using this formula before... =IF(M1824="Expense",IFERROR(UPPER(MID(E1824,SEARCH("JOB",E1824,1),3)&"-"&MID(E1824,SEARCH("JOB",E1824,1)+4,4)),UPPER(MID(D1824,SEARCH("JOB",D1824,1),3)&"-"&MID(D1824,SEARCH("JOB",D1824,1)+4,4))),"") Columns D & E contained the text and this returned a result in a format I could then use for lookups i.e. "JOB-####", or an error if "JOB" wasn't in either column. This meant a lot of manual checking of error cells. To add to the fun, we have just breached JOB-9999 so I'm now also having to look for JOB-10000+ (I don't envision having to worry about 6 digit job reference but if there is a way to future proof then bonus.) Annoyingly, some of the data is system generated when invoices are processed and POs are matched, but some comes from expense claims and is manually entered, so although Xero will always give the reference as "JOB-####" or "JOB-#####" sometimes the reference will appear as "Job ####" or "job no ####" or just "####" or any other random combination the engineers think to put on their expenses, so if i can just reference a cell and pull back any 5 digit number and if there's no 5 digit number then look for a 4 digit number? I hope someone out there can help, I've tried a few things but can't seem to get anywhere close to a useable result. Living in hope, MarcusSolvedEmJayLeppJan 25, 2025Copper Contributor71Views0likes6CommentsIF with multiple conditions please
I have a spreadsheet in which I have created a credit card statement. I have managed to get most functions to work but an IF function is puzzling me how to get around it and is not coming back with the additional payment correctly resulting in the balance becoming a negative figure. So, in column: F is the regular payment, G with a suggested extra payment, H with a manual extra payment, J with the interest paid per month and L with the resulting balance I have IF function that in column G "=IFERROR(IF(L49>F50,L$39,IF(L49<I49,0,L$39)),0)" that returns a suggested extra payment based on column J multiplied by 105%. This, however, when comes to final payment in column L produces a negative amount. I have tried to do SUM function with IF and IFS but cannot get it to return the value needed. I want the IF formula in column G to calculate and return the value suggested in L39 but return the difference between column L and column G when the final balance is less than the regular payment but more than the suggested regular payment.WPHJan 25, 2025Occasional Reader29Views0likes1CommentSorting numbers
Hi, I have a excel sheet with x number of columns. I have formated the rows so that if there is a specific number it is marked in red. See picture below. The numbers that are marked are Top row 0 - 3 and 9 - 13. So numbers 4-8 shall not be marked Middle row 0 and 6 - 13. So numbers 1-5 shall not be marked Bottom row 0 - 1 and 7 - 13. So numbers 2-6 shall not be marked What I now whant is to count all columns that are not marked red. As of now I do this manual with marking with "1" below and use function Count. Someone that can help me with a function for this? Most thankful.SolvedPerJan 24, 2025Copper Contributor141Views0likes13CommentsY-Axis Not Grouping?
Hi there, I am working on creating a block schedule chart, you can see the table at the top left and what my chart looks like right now. The y-axis represents a different room. I want the block schedule to overlay rooms so that there are multiple blocks in the same row. For example, TUA TOPS OR INTRAOP 2 would have the 4 blocks displayed on the same row. My problem is that I cannot seem to get them to group together. You can see that in the table I have identified duplicates to ensure that the naming conventions are the same. I have also formatted column C as text. I have also assigned the Axis Type to be "Text axis" as you can see on the right of the screen. Any suggestions? I think ChatGPT is sick of trying to help me at this point lol.ErikElisson21Jan 24, 2025Occasional Reader22Views0likes1CommentSkipping cell while pasting data
Hi, I'm trying to figure out if there's a way to skip cell at constant interval while pasting Huge amount of data. Something that will act as a pasting template/guide eg.: I need to copy huge amount of data (around 5000 cell) in a row but I need to let 2 blank cell every 5 Data cell. So lets say column C is where I want to paste the data, but I need it to skip cell C7,C8 and then C14,C15 (and so on) while pasting the data. like this: Thank youSolvedCyressvirusJan 24, 2025Copper Contributor35Views0likes1CommentRemoving a line between two points expands the legend
Hi all, I'm wondering how to avoid the following problem: When I remove the solid line between two points, where there's a break in data logging, the legend seems to expand to describe each point's x value instead of the series name. Is it possible to avoid this? Images below. Many thanks, FazFazJan 24, 2025Occasional Reader11Views0likes1Commentget count from multiple rows in one cell by unique identifer
I have a tab "Tab1" with a column named DND that has multiple rows with the same cell data. See my pictures, for example there are three lines for "location 1". I need to count the totals in the "Count" column for that location and put the result in my second tab named "Tab2". So, on "Tab2" for "location 1" the answer should be 3.ssuuzzeeqqJan 24, 2025Occasional Reader14Views0likes1Comment
Resources
Tags
- excel42,067 Topics
- Formulas and Functions24,389 Topics
- Macros and VBA6,335 Topics
- office 3655,910 Topics
- Excel on Mac2,610 Topics
- BI & Data Analysis2,316 Topics
- Excel for web1,869 Topics
- Formulas & Functions1,716 Topics
- Need Help1,703 Topics
- Charting1,604 Topics