What's new
USCHO Fan Forum

This is a sample guest message. Register a free account today to become a member! Once signed in, you'll be able to participate on this site by adding your own topics and posts, as well as connect with other members through your own private inbox!

  • The USCHO Fan Forum has migrated to a new plaform, xenForo. Most of the function of the forum should work in familiar ways. Please note that you can switch between light and dark modes by clicking on the gear icon in the upper right of the main menu bar. We are hoping that this new platform will prove to be faster and more reliable. Please feel free to explore its features.

Excel Help v. 1,000,000

AMC

Registered User
I'm having sort issues in Excel. I have four columns: A: Name, B: Data1, C: Data2, D: Data3. I want to sort by Data3. However, D consists entirely of formulas drawing data from different cells in B and C. For example, cell D16 needs to get the total from C4 and B19. If I just sort by D, it messes up my data because what is in C4 and B19 changes. This is also a working file, so I need to be able to go to, say, Name in A19, change the value in B19, which changes the value in the D cells that relate to B19. Then I need to sort by D so that the names and the totals in Data3 line up.
 
Re: Excel Help v. 1,000,000

I'm having sort issues in Excel. I have four columns: A: Name, B: Data1, C: Data2, D: Data3. I want to sort by Data3. However, D consists entirely of formulas drawing data from different cells in B and C. For example, cell D16 needs to get the total from C4 and B19. If I just sort by D, it messes up my data because what is in C4 and B19 changes. This is also a working file, so I need to be able to go to, say, Name in A19, change the value in B19, which changes the value in the D cells that relate to B19. Then I need to sort by D so that the names and the totals in Data3 line up.

Did you try dollar signs before the C, the 4, the B, and the 19?
 
  • Like
Reactions: AMC
Re: Excel Help v. 1,000,000

I could kiss you. Is there a way to make the $ automatically appear when I click on a cell to use in the formula or do I have to manually enter them all?
 
Re: Excel Help v. 1,000,000

I could kiss you. Is there a way to make the $ automatically appear when I click on a cell to use in the formula or do I have to manually enter them all?

while in the formula bar, you can hit the F4 key. It cycles through the $ in four ways:

$b$4 always refers to cell b4. $b4 always refers to column b but when you copy or sort the formula, the row changes. b$4 alwas refers to row 4, but when you copy or sort the formula, the column changes (well, in a vertical sort the column won't change).

So if you have a series of numbers in b1 through g1 and a column of numbers in a2 through a6, entering =$a2*b$1 in cell b2 and then copying it from b2 through b2-g2 and b6-g6, you'll multiply whatever is on the top row by whatever is in the leftmost column.
 
  • Like
Reactions: AMC
Re: Excel Help v. 1,000,000

Terrific! This has been the largest excel project I have ever worked on, I'm learning all kinds of cool stuff.
 
Re: Excel Help v. 1,000,000

Need some help making a combined multiple-bar/line graph.

My coworker has a data table of derps that I'm helping her make a chart of. They're sorted by the different types of derps per quarter, then the total number of all derps per quarter at the bottom of the chart. I have the types of derps sorted per quarter as multiple bar graphs. We need to add the total number of all derps per quarter as a line graph on the same chart.

Apparently you could do this in Excel '03, but we can't figure out how to do it in '07.

I'll try to get you a screen grab or something so you can see what I'm talking about, but I don't know where I'd host it from (and I might not be allowed to anyway.)

tl;dr: how do I combine a line graph over top of a bar graph in Excel '07?
 
Re: Excel Help v. 1,000,000

Need some help making a combined multiple-bar/line graph.

My coworker has a data table of derps that I'm helping her make a chart of. They're sorted by the different types of derps per quarter, then the total number of all derps per quarter at the bottom of the chart. I have the types of derps sorted per quarter as multiple bar graphs. We need to add the total number of all derps per quarter as a line graph on the same chart.

Apparently you could do this in Excel '03, but we can't figure out how to do it in '07.

I'll try to get you a screen grab or something so you can see what I'm talking about, but I don't know where I'd host it from (and I might not be allowed to anyway.)

tl;dr: how do I combine a line graph over top of a bar graph in Excel '07?

Can you save the bar/line graph as a template in '03 and then load it into '07?
 
Re: Excel Help v. 1,000,000

Need some help making a combined multiple-bar/line graph.

My coworker has a data table of derps that I'm helping her make a chart of. They're sorted by the different types of derps per quarter, then the total number of all derps per quarter at the bottom of the chart. I have the types of derps sorted per quarter as multiple bar graphs. We need to add the total number of all derps per quarter as a line graph on the same chart.

Apparently you could do this in Excel '03, but we can't figure out how to do it in '07.

I'll try to get you a screen grab or something so you can see what I'm talking about, but I don't know where I'd host it from (and I might not be allowed to anyway.)

tl;dr: how do I combine a line graph over top of a bar graph in Excel '07?

There's a quick "cheater's" way, you build two graphs and in one graph set everything to clear except the line graph and its legend (and perhaps its Y axis?), and then you superimpose the two graphs. One shows through the other.
 
Re: Excel Help v. 1,000,000

Figured it out. Just have to click the individual series of data you want and hit Change Series Chart Type.
 
Re: Excel Help v. 1,000,000

Figured it out. Just have to click the individual series of data you want and hit Change Series Chart Type.

Thanks, I'll remember that!


Here is a tip for those who use lookup functions a lot, and have to collaborate with others.

Suppose you have a table in cells b4:f53 that represent 50 years of data. You want to create a summary that pulls every five years, say, and so you use vlookup(#,$b$4:$f$53,5,false). then someone else inserts a column (or deletes one) and now your lookup function no longer works, the number of columns you want is no longer 5 but 4 or 6.

Instead of inserting a fixed number, use the column function, then reference the column function output in your lookup function.

So, for example, in cell b1 you type =column(b1)-column($b1)+1, and then copy that to the right in row 1.

Then your lookup function becomes (#,$b$4:$f$53,f1,false). If you insert or delete columns between b and f then the column function automatically changes the value, giving you the right number in your lookup function every time. The same can be done with hlookup and the row function.
 
Re: Excel Help v. 1,000,000

Need some help making a combined multiple-bar/line graph.

My coworker has a data table of derps that I'm helping her make a chart of. They're sorted by the different types of derps per quarter, then the total number of all derps per quarter at the bottom of the chart. I have the types of derps sorted per quarter as multiple bar graphs. We need to add the total number of all derps per quarter as a line graph on the same chart.

Apparently you could do this in Excel '03, but we can't figure out how to do it in '07.

I'll try to get you a screen grab or something so you can see what I'm talking about, but I don't know where I'd host it from (and I might not be allowed to anyway.)

tl;dr: how do I combine a line graph over top of a bar graph in Excel '07?

Out of curiosity, why wouldn't you just use stacked cluster columns graphs for that? The colors would give you the type of derp within a class, clusters would let you distinguish between multiple derp classes (if needed) and the column height would give you the total derps per quarter.

Cluster_Stack_Bars_Chart.png
 
Last edited:
Re: Excel Help v. 1,000,000

Out of curiosity, why wouldn't you just use stacked cluster columns graphs for that? The colors would give you the type of derp within a class, clusters would let you distinguish between multiple derp classes (if needed) and the column height would give you the total derps per quarter.
Sometimes a line added to the chart is a better representation.

Like if you were showing a bar chart of customers per month and then a line showing the average per month for the previous year(s) overlayed.
 
Re: Excel Help v. 1,000,000

Sometimes a line added to the chart is a better representation.

Like if you were showing a bar chart of customers per month and then a line showing the average per month for the previous year(s) overlayed.

Ah. Yes, I can see that when you're comparing against averages, definitely. Hi/low temp charts, for instance. Good call.
 
Re: Excel Help v. 1,000,000

I re-discovered a function I'd used a year ago, a wonderful tool if you work with multi-worksheet workbooks.

The "indirect" function. If you know how to write a text string, you can write an address and the "indirect" function will find whatever is at that address.

Frequently I have to produce analyses of a population. I set up a worksheet for one member of the population, then copy it so that every worksheet has the same data in the same place, then name the worksheets in some sequential order, and fill in unique data for each member of the population into each worksheet (the SUMIF function is great for this task in some cases).

then, on my "summary" tab, the INDIRECT function lets me write multiple addresses from multiple worksheets by formula, so that I don't have to physically link to those cells in order to produce the summary results. an enormous time saver which also greatly reduces error rate.
 
Re: Excel Help v. 1,000,000

Got a question. Be aware that I'm using OpenOffice instead of Excel, but it's the still with spreadsheets:

I have a formula to get unique values in an array. It's pretty standard:
{=INDEX($A$2:$A$8,MATCH(0,COUNTIF($B$2:B2,$A$2:$A$8),0))}

Here's my issue: Inside of my original list, I have entries "ABC" and "BC". I need both to show up. Unfortunately, only one or the other shows up, typically whichever is listed first. How would I get both to show up?
 
Re: Excel Help v. 1,000,000

Resurrecting this....

This might be solved, somehow, using the hint that FreshFish posted a few posts early, but I can't quite figure out how.

I want to have 2 different spreadsheets, one containing several tabs, each containing data, and the other with a form that I want to populate with the data. The second sheet is a template for a datasheet that I need to create and publish, and first will contain all of the data for all of the variations of the datasheet that I'll need. What I want to be able to do is to go into the second sheet, and select, from a drop down menu, a tab from the first sheet, and have the data from the selected tab populate the template. I am not sure if this is even possible, but I need to give it a try,or else I'll end up with 30-40 spreadsheets that I need to change every time there is a template change.

Any help is greatly appreciated!
 
Re: Excel Help v. 1,000,000

Resurrecting this....

This might be solved, somehow, using the hint that FreshFish posted a few posts early, but I can't quite figure out how.

I want to have 2 different spreadsheets, one containing several tabs, each containing data, and the other with a form that I want to populate with the data. The second sheet is a template for a datasheet that I need to create and publish, and first will contain all of the data for all of the variations of the datasheet that I'll need. What I want to be able to do is to go into the second sheet, and select, from a drop down menu, a tab from the first sheet, and have the data from the selected tab populate the template. I am not sure if this is even possible, but I need to give it a try,or else I'll end up with 30-40 spreadsheets that I need to change every time there is a template change.

Any help is greatly appreciated!

not sure I completely follow; if you have a file-share site to which you can post your workbook, post the link on line, then give me the password in rep I can try to look at it.

Here's my initial thoughts.

Have it all in one workbook to start, with the form on the first tab with appropriate formulas and drop-down menu, and all the other tabs having data (you can populate the form as needed, then copy / paste special / values from that form into the other workbook so that the output for each scenario you run is all hard-coded there).

If you are familiar with the data validation menu, you can set up your drop-down menu that way.

Then a combination of the indirect function and a lookup function (or a sumif function) can then populate the form in the master workbook.

I've forgotten how to use the database functions; someone with more up-to-date experience with those would have to help you if need be for that part.
 
Re: Excel Help v. 1,000,000

Resurrecting this....

This might be solved, somehow, using the hint that FreshFish posted a few posts early, but I can't quite figure out how.

I want to have 2 different spreadsheets, one containing several tabs, each containing data, and the other with a form that I want to populate with the data. The second sheet is a template for a datasheet that I need to create and publish, and first will contain all of the data for all of the variations of the datasheet that I'll need. What I want to be able to do is to go into the second sheet, and select, from a drop down menu, a tab from the first sheet, and have the data from the selected tab populate the template. I am not sure if this is even possible, but I need to give it a try,or else I'll end up with 30-40 spreadsheets that I need to change every time there is a template change.

Any help is greatly appreciated!
Would/do the values in the template point to the same location in each of the data tabs or would it have to point to a different location depending on which tab you were pulling information from?

Even if you're pulling from different locations, you could use named ranges so that it pulls from a consistent place (instead of having it switch from cell A5 on one tab and A10 on another, set up A5 to be "VALUE1" on the first tab and have A10 be "VALUE1" on the second tab and have the form pull its information from VALUE1 on the relevant tab).

Data validation is definitely the way to go on the dropdown box. And then just making sure that the values in the template point to the correct place is probably helped by the INDIRECT method mentioned earlier or the INDEX(MATCH()) type stuff.
 
Back
Top