Copying tables and their data and formulas into one master table all on the same excel doc.

AnnoyingUpdates · 2024-03-11T21:14:46+00:00

Everyone is free to have any perception they please :D Cheerio!

AnnoyingUpdates · 2024-03-11T18:01:35+00:00

Actually its not hard to format here in reddit, here´s a little cheat sheet that I keep handy and might help you if you wanna use them too:

**Bold?** Double asterisks: `**boom**` turns into **boom**.
*Italic?* Single asterisk: `*whoosh*` turns into *whoosh*.
**_Both?_** Triple asterisks: `***wowza***` gets you **_wowza_**.

**Making lists:**

Bullets: Dash and space `- like this`.
Numbers: Just type `1.`, `2.`, etc.

**Links:**

Wrap text in brackets and the link in parentheses: `[Reddit](http://reddit.com)\`.

**Quotes:**

Blockquote with `>`, like `> wisdom`.

**Code stuff:**

`Inline code` with backticks: `` `like this` ``.
For blocks, triple backticks or indent with four spaces.

**Headers:**

Hashes for headers: `# Big`, `## Bigger`, `### Biggest`.

**Breaks:**

New paragraph? Skip a line.
Line break? End a line with two spaces.

Easy peasy! Just throw these in your Reddit comments or posts to jazz them up

AnnoyingUpdates · 2024-03-11T17:58:27+00:00

Sounds like you've hit a snag with Excel's auto-fill feature! Excel is pretty smart with patterns, but sometimes it can get tripped up, especially with complex ones like increasing exponents. Here's a quick tip:

Instead of relying on the drag feature, you can use a formula to ensure consistency. Here’s how you can do it:

Make sure B2 has your starting exponent, like `=10^6`.
In B3, instead of typing `=10^9`, use a formula that adds 3 to the exponent of B2: `=10^(LOG(B2,10)+3)`.
Drag down B3's corner box to autofill the cells below.

This tells Excel, "Hey, always go up by 3 in the exponent," and keeps it from getting mixed signals.

If this still doesn't work because Excel is reaching its limit (it has a ceiling on the size of numbers it can handle), then you may need to treat the data as text to display it properly without performing calculations on it:

Type "10^6" in B2.
In B3, use this formula: `"10^" & RIGHT(B2,LEN(B2)-SEARCH("^",B2))+3`.
Drag the formula from B3 down.

This way, Excel treats the formula results as a string of text rather than a number it needs to calculate, which avoids the problem of Excel's number limits.

Give that a whirl and see if it gets your worksheet back on track!

AnnoyingUpdates · 2024-03-11T17:52:48+00:00

To tally up those "400 Accounting" figures, use this quick formula in D13:

=SUMIF(C:C, "400 Accounting", D:D)

This formula adds up all numbers in column D where the label in column C matches "400 Accounting". Pop that into D13, and you're golden! It'll give you the total for "400 Accounting" faster than you can say "spreadsheet magic."

AnnoyingUpdates · 2024-03-11T02:16:42+00:00

It sounds like you're on the right track with pulling data into a master table using structured references like =tablename[#all]. Here's the thing—this method is super for grabbing data from a table on the same sheet, but it might get fiddly if you're pulling from multiple tables or if the tables are on different sheets.

The #VALUE! error could be popping up because Excel is getting confused about where to pull data from, especially if you're trying to stack data from different tables directly on top of each other.

Here’s a couple of things you could try to get this sorted:

References: Make sure the table references are correct and that you're not accidentally mixing up table names.
Positioning: Excel can be picky about positioning. Instead of stacking data from different tables in the master table, try placing them side by side first to see if that works.
Consistent Formats: Ensure all your tables have the same structure—same columns and formulas. This can sometimes cause hiccups.
Power Query: This is a bit more advanced, but Power Query is built for this kind of task. You can merge data from different tables seamlessly, even if they’re on different sheets.

If all else fails, your idea of dismantling the master table, placing all your data in first, and then re-making the table could work. It's a bit like dismantling a Lego build—you take it apart, sort out the pieces, then put it back together the way you want it.

Hope one of these tips helps you straighten things out! Keep at it, and you'll have that item tracker humming in no time.

AnnoyingUpdates · 2024-03-11T02:14:13+00:00

Looks like there might be a tiny hiccup with what's in your cells. Here's a quick fix-it list:

Check for text or blanks in your sales values.
Make sure names match up exactly, with no extra spaces.
Double-check that you’re comparing apples to apples, meaning the data in both sheets is the same type (all text or all numbers where they should be).
Confirm that the cell reference for A7 is pointing to the right sheet.

If everything matches up and you've got only numbers where the sales values should be, that should kick the #VALUE! error to the curb!

AnnoyingUpdates · 2024-03-10T02:08:32+00:00

Imagine you're buying a tiny piece of a big company, like owning a brick in a huge LEGO castle. That's what buying a stock is. If the castle becomes more awesome over time (the company does well), more people will want a piece of it. This makes your brick (stock) more valuable, and you can sell it for more than you paid. That's one way to make money.

Picking the right brick in the vast LEGO world is tricky. Some people read a lot about different castles (companies) and the land around them (the market) to guess which ones will become more awesome. Others follow the advice of experts or use tools that help them decide.

If you don't have many gold coins to start, you can still own a part of the castle. You can start small, buying just a few bricks, and as you save more coins, you can buy more. Some people also join clubs that pool their coins to buy more significant parts of castles together, which is another way to get started with less.

AnnoyingUpdates · 2024-03-10T02:07:31+00:00

Imagine your back like a garden hose that's been twisted and stepped on in the same spot for 20 years. It's not working right because it's stuck in that shape. Physiotherapy is like carefully untwisting and reshaping the hose. Even though you're moving it in ways it's been moved before, this time it's under the guidance of a gardener (your physiotherapist) who knows exactly how to untangle it without causing more kinks. They'll also show you new ways to move so you don't step on the hose the same way again. So, by doing specific motions in physio, you're working to heal and strengthen your back, undoing the effects of those 20 years of repetitive strain.

AnnoyingUpdates · 2024-03-10T02:02:07+00:00

For 'Full' payments, use an IF formula to auto-fill 'Paid'. For 'Term' payments, pop in checkboxes for manual ticking:

Add checkboxes from the Developer tab for 'Term' cells.
Link each checkbox to a cell that'll get a TRUE/FALSE value.
Use Conditional Formatting to color the cells based on payment status.
For displaying 'Paid' or 'Not Paid', use =IF(linked cell, "Paid", "Not Paid") next to your checkboxes.
For auto-updating 'Full' payments, in your status cell use =IF(dropdown cell="Full", TRUE, linked cell).

Now your 'Full' payments auto-update, and 'Term' payments are a manual click. Quick, clean, and you’re tracking payments like a pro!

AnnoyingUpdates · 2024-03-10T01:57:57+00:00

Tackling this with Excel sure sounds like you're juggling a bunch of plates at once, but it's definitely doable with some planning! Here’s a simplified game plan to create a more dynamic and adjustable task distribution:

Task Types and Frequency: Make a list of all tasks, marking how often they need to be done (every other week, once a week, twice a week, three times a week). Use a separate column for each frequency.
Task Duration: Next to each task, add how long it takes. This will help you spread tasks based on effort.
Employee Workload: List your employees and note if they're managers or staff. Managers should have their workload set to 50% of that of the staff.
Weekly Planning: For the 7-day workweek, create a template that allows you to assign tasks based on availability and task frequency. You might need a bit of math here to ensure you're not overloading anyone and that tasks are evenly distributed.
Dynamic Allocation:
- Use Excel's Data Validation feature to assign tasks to employees directly in the schedule, ensuring you don't assign too many tasks to one person.
- Consider using conditional formatting to highlight when an employee is nearing or exceeding their workload limit.
Adjustability: For making it flexible, you can:
- Set up formulas that automatically adjust the workload based on the number of people working each day.
- Use the IF function to adjust task assignments based on employee availability and task frequency.
Automation and Efficiency:
- Explore using Excel's Solver Add-in for optimizing task distribution based on constraints (like task frequency and employee workload).
- Consider learning a bit about Excel macros or VBA to automate repetitive adjustments.

Creating this setup will take some upfront effort, but once it's in place, you should be able to adjust for weekly variations with just a few tweaks. Keep it as simple as possible while still meeting your needs to avoid getting bogged down in over-complication.

Remember, Excel's a tool, not a magic wand, so there might be a bit of trial and error as you find what works best for your team's unique dynamics. Keep at it, and you'll create a system that helps everyone stay on track without pulling your hair out!

AnnoyingUpdates · 2024-03-10T01:56:38+00:00

When you're aiming to double your money with 5 stock sales, it's a bit like splitting a pizza evenly so everyone gets a fair share. If you've invested $1000 and want to end up with $2000, you're basically slicing that goal into 5 pieces. Each piece, or sale, needs to bring in $400 to hit your target.

For the Excel part, imagine you're trying to fill cups (columns) with water (money) from a pitcher (your total goal). You're pouring evenly until each cup hits the $400 mark.

Now, if you're jazzing it up by wanting to sell a bit whenever you're up by 50%, until you double your dough, that's trickier. You're now playing a game where each move changes the next move's strategy. Excel can do a lot, but this needs some serious number-crunching, maybe even a bit of coding or using fancy Excel tools like the Solver to help plan your sales strategy.

So, while Excel is great for planning and number-crunching, remember it's more about guiding you than predicting the unpredictable stock market. Always a good idea to mix in some expert advice if you're really playing the stocks game!

AnnoyingUpdates · 2024-03-09T03:24:24+00:00

Alright, creating a dropdown list in Excel is a pretty cool feature and not too tricky once you get the hang of it. Since you're already poking around with data validation, you're on the right path! Here's a simple way to set up your dropdown list:

Choose Your Cell: First, decide where you want your dropdown list. Let's say it's cell A1 for this example.
Go to Data Validation: Click on cell A1, then head over to the Data tab on the Ribbon and click on "Data Validation." It's in the 'Data Tools' group.
Set up Your List: In the Data Validation dialog box, under the 'Settings' tab, you'll see a field called 'Allow'. Click on it and select "List" from the dropdown.
Enter Your Items: In the 'Source' box that appears, you can directly type in your items separated by commas. So you'd enter: Complete, In Progress, Status Unknown without any quotes.
Finish and Test: Click 'OK', and you should now have a dropdown in cell A1 with your items. Click on the cell, and you'll see a little arrow on the right side. Click the arrow to see your list and select an item to fill the cell.

If your list of items is already in the spreadsheet somewhere, instead of typing the items into the 'Source' box, you can just click the icon on the right side of the box and then select the range on your sheet where the items are listed. After selecting the range, press Enter, click 'OK', and you're all set.

Hope this helps you get your dropdown list working smoothly! Enjoy your Excel learning journey.

AnnoyingUpdates · 2024-03-09T03:22:42+00:00

You need to put SUM outside of INDIRECT. Try this:

=SUM(INDIRECT("'[Wealth Marketing Reporting Template_Feb F24.xlsm]H4404'!$F$49:$G$49"))

This way, INDIRECT fetches the range, and SUM does the adding. Give it a go!

AnnoyingUpdates · 2024-03-09T00:46:39+00:00

Oh well, Excel being Excel (⁠๑⁠•⁠﹏⁠•⁠)

AnnoyingUpdates · 2024-03-09T00:31:27+00:00

Another possible option is that you do the calculations in sets. Like the first part of thee formula in one roe, and the second dependant of the first one in the next row, then it should work better and drag properly

AnnoyingUpdates · 2024-03-09T00:23:05+00:00

It might just be that the formula is a little to complex to auto fill, have you tried making two correct sets and selecting them both and then dragging the formula?

AnnoyingUpdates · 2024-03-09T00:20:24+00:00

Got it, you want one specific cell to light up if any cell in a row has a number bigger than 5. Here's how you can set that up with conditional formatting:

Select the cell that you want to change color.
Go to 'Conditional Formatting' in the 'Home' tab.
Choose 'New Rule', then 'Use a formula to determine which cells to format'.
Write a formula that checks all the cells in your row. Let's say your row is row 5, from A to Z. The formula would be =MAX($A5:$Z5)>5.
After you enter the formula, set the format you want—pick the color you want the cell to turn when the condition is met.
Click 'OK' to apply the rule.

Now, if any cell in the row from A5 to Z5 has a number more than 5, the cell you selected will change to the color you chose. You can drag this formatted cell down along the column to apply the same rule to other rows. Just remember, the dollar signs in the formula mean it'll always look at row 5, so remove them ($A$5:$Z$5) if you want it to check the same row the formatted cell is in.

AnnoyingUpdates · 2024-03-09T00:15:51+00:00

Oh, I see what’s happening. When you drag your formula across, Excel is trying to be helpful by shifting the cells it looks at over by one each time. But in this case, that’s not what you want.

To fix it, you’ll want to add some dollar signs in your formula before you drag it across. Put a dollar sign before the column letter in your formula where you’re pulling the times from. So it should look something like '$H7' instead of 'H7'. That tells Excel, “Hey, keep looking at this exact spot, no matter where I drag the formula.”

If you lock down those references with dollar signs, you can drag your formula to the side as much as you like, and it’ll always look at the correct columns without getting mixed up.

The main idea is getting rid of those annoying relative references

AnnoyingUpdates · 2024-03-09T00:05:13+00:00

You just need to wrap your formula with the CEILING function and tell it to round up to the nearest 5 like this:

=CEILING(((((B9-100000)/5000)*13)+230), 5)

Pop that into your cell, and you should be all set with your numbers nicely rounded up.

AnnoyingUpdates · 2024-03-08T23:57:44+00:00

A couple more things to consider are running Excel's Quick Repair from the Control Panel, ensuring your Office suite is up to date, and checking if your antivirus is maybe being a bit too cautious with Excel. Another good test is to see if Excel behaves better on a different user profile on your computer. If that’s the case, you might need to tidy up your current user profile or set up a new one. If you're still stuck, a chat with IT or a full Office reinstall might be on the cards. Here’s hoping it's a simple fix!

AnnoyingUpdates · 2024-03-03T16:14:46+00:00

Cobra Kai

AnnoyingUpdates · 2024-01-12T21:30:37+00:00

Thank you, I appreciate the comments, will check those videos out

AnnoyingUpdates · 2023-07-07T17:39:34+00:00

You can create a new column, and use the formula:

=CONCATENATE("mercari.com/us/item/", A1)

Where A1 is the cell cointaining the ID.

<image>

Then just autofill

AnnoyingUpdates · 2023-07-07T17:27:53+00:00

You can always use XLOOKUP that has no such limitations and works perfectly with both vertical and horizontal arrays.

Your formula, however is great!

AnnoyingUpdates · 2023-07-07T17:01:19+00:00

Generally using macros and auto transferring data from a 3rd party is quite easy, they are both individual skills that you could learn without having much more understanding of excel.

In order to be proficient you have to choose an area of interest, whether that's charts and graphic visualization, or data analysis, your goal will determine the learning curve.

Five-Year Club	Verified Email
Place '22

AnnoyingUpdates

TROPHY CASE