User Profile
adversi
Iron Contributor
Joined Nov 12, 2020
User Widgets
Recent Discussions
Re: Excel file too large
Karenmentions5 Depends on what the final goal is. Is there any excess data (rows/columns) that can be deleted (ie. any data that formulas are not referencing?) You can Save As the file to a binary sheet (.xlsb) to reduce the file size if it has a lot of data. You can utilize Power Query to manage your data ETL (extraction, transforming, loading) process if the file is expected to continue growing.Re: Grouping Rows
JessicaAuvil1 The best way is to continue adding more data to your table. At the moment it only contains two columns (Shirt, Fossil). Adding more columns (Name, Color, Resource, etc.) will provide a more fledged-out table to work with. From there, creating an ID system based on these other columns can help track, order and manage all your rowsRe: I don't know how to do this please help!!!!
lostcoz See attached a sample file on how conditional formatting works. Two most important steps is to (i) use the TODAY() function to get todays date to get total days passed from start claim date (ii) Apply the conditional formats in the same order. You can view them like this:Re: help referencing data from large spreadsheet to summary sheet
MaryR1330 There are different ways to go about this. The best next step is to send over your file, removing any sensitive data, for us to test out different methods of how to create a dynamic reference in your Summary tab. One immediate change would be to apply a SUMIF function that changes the weeks being summed together based on the current time, to avoid having to manually sum each tabs figures.Re: Possibility to have a more practical formula
AlbertM1960 Since the SUMIF seems to repeat based except for the sheet reference, maybe considered consolidating all the tables to one final sourced table. That way the SUMIF can then be updated to SUMIFS, taking into the account the new variable (ie. the month). If you can attach a sample workbook, removing any sensitive data, I can create a sample of what I mean.Re: The dreaded Apostrophe is turning numbers into text in a forms spreadsheet
Davidm54 For reference, you can also do the following (source😞 To remove single quote in front of numbers in a cell, you can highlight the entire column where the pasted text figure will be and Clear Formatting. Go to Home Tab > Editing Group> Clear > Clear FormattingRe: Can someone explain the meaning of this formula?
sabriakter This is a better way of reviewing the formula and the Nested If statement: =IF($B$11>DATE(103,1,1), IF($B$11<DATE(103,12,31),MAX(DATE(103,1,1),$B$11),""), IF($B$11<DATE(103,1,1),DATE(103,1,1),"")) The first line is checking if cell B11 is after 1/1/2003 If TRUE, it checks if B11 is before 12/1/2003 if TRUE, it will return the date in B11 if FALSE, it will return BLANK the last line is the FALSE statement if B11 is after 1/1/2003: The IF statement reviews if B11 is before 1/1/2003, if TRUE, it returns 1/1/2003 if FALSE, it returns BLANKRe: PLEASE HELP>>> Extract data from a single cell on a separate sheet using drop down
Dashdotdash Here's how the VLOOKUP https://support.microsoft.com/en-us/office/vlookup-function-0bbc8083-26fe-4963-8ab8-93a18ad188a1, this assumes that the Space column in Sheet2 is where the Column A reference would match
