The Excel Questions Thread

[CunningSmeagol]

Glad to help. Mr G, can you please elaborate on your question? I don't really understand what you need to happen, because as I said, what I think you want doesn't seem all that desirable to me.

 

[Neo1824]

Originally Posted by dsgNYC

The simple macro I wrote in my last post gives you the path of the file, which will vary based on who is running the macro and what letter their share drive is mapped to. So, if you're coworker retrieves the file from their X:\\ drive and you retrieve it from your W:\\ drive the macro will return "X:\\filename.xls" if your coworker runs it and "W:\\filename.xls" if you run it.

Alternatively, would referencing the ActiveWorkbook work?

Code:

With ActiveWorkbook
.BreakLink "Link_Name", xlLinkTypeExcelLinks
End With
​

This worked! Short and sweet.
Thanks!

 

[haganah]

I'm tired today and can't think.

1) If you're plotting ROI figures over a year, would you use a linear trendline or logarithmic or one of the other options?

2) And is there any way to show the beginning value for the line and the end value?

 

[FLMountainMan]

How do you autofill a formula that pulls from multiple tabs? Like I want to add all the values in Cell A3 from 50 tabs and then all the values from cell B3 from 50 tabs and the all the values from Cell B3 from 50 tabs, and so on.

It won't let me just drag down the box and autofill.

 

[CunningSmeagol]

Originally Posted by FLMountainMan

How do you autofill a formula that pulls from multiple tabs? Like I want to add all the values in Cell A3 from 50 tabs and then all the values from cell B3 from 50 tabs and the all the values from Cell B3 from 50 tabs, and so on.

It won't let me just drag down the box and autofill.

Type it like this:

=SUM(Sheet1:Sheet50!A3)

EDIT - Assuming the sheets are consecutive

You should be able to drag down. You just can't use that formula in cell A3 on Sheet 1 b/c then it would be referencing itself.

Are you sure there are no "$"s in the formula you are trying to use?

 

[CunningSmeagol]

Originally Posted by haganah

I'm tired today and can't think.

1) If you're plotting ROI figures over a year, would you use a linear trendline or logarithmic or one of the other options?

2) And is there any way to show the beginning value for the line and the end value?

1 - I am not a banker. I have no idea.

2 - Not sure what you mean, but I would think you could just make another series based on the trendline, make it invisible, and use a label from one of the first points.

 

[FLMountainMan]

Originally Posted by CunningSmeagol

Type it like this:

=SUM(Sheet1:Sheet50!A3)

EDIT - Assuming the sheets are consecutive

You should be able to drag down. You just can't use that formula in cell A3 on Sheet 1 b/c then it would be referencing itself.

Are you sure there are no "$"s in the formula you are trying to use?

There are $s....let me guess, that's a basic no-no?

 

[CunningSmeagol]

Originally Posted by FLMountainMan

There are $s....let me guess, that's a basic no-no?

It's a yes/no. They hold either the letter (column) or number (row) in place when you drag. They are especially useful if you're dragging right and then dragging the whole thing down or vice versa.

Also, if you reference a cell in a formula, try hitting F4 a few times after clicking the cell. It will toggle between the permutations of dollar signs.

 

[dhc905]

How can I check to see if a number is within an array? I.e. I'm trying to place quarterly payments by saying if the counter of the number of quarters that has gone by = a selection of quarters where a payment should be made, return me 1 (or the payment or whatever).

I've tried if(match(counter,list of quarters where expenses should be paid),1,0) and If(or(counter=list of quarters),1,0). The last one gives me the right answer if I highlight the equation and hit F9, but VALUE's out when I'm not hard calculating it. WTF.

Help!

 

[snuffy]

This will be an easy answer. (Need to improve Excel skills).

I have 6 worksheets with only two columns each. Column A has a listing of names, Column B has a listing of percentages corresponding to the names.

I need to add up the listing of Names(A) with the percentages across the 6 worksheets. Essentially consolidating into one. Keep in mind some of the same names appear in the 6 sheets, with different percentages.

Any suggestions?

Thanks

 

[dsgNYC]

Originally Posted by snuffy

This will be an easy answer. (Need to improve Excel skills).

I have 6 worksheets with only two columns each. Column A has a listing of names, Column B has a listing of percentages corresponding to the names.

I need to add up the listing of Names(A) with the percentages across the 6 worksheets. Essentially consolidating into one. Keep in mind some of the same names appear in the 6 sheets, with different percentages.

Any suggestions?

Thanks

I'm not quite sure I understand what you're trying to accomplish. Are you trying to get a final list of only the unique names from column A and the sum of all of their corresponding percentages from column B across all 6 worksheets?

 

[CunningSmeagol]

Originally Posted by dhc905

How can I check to see if a number is within an array? I.e. I'm trying to place quarterly payments by saying if the counter of the number of quarters that has gone by = a selection of quarters where a payment should be made, return me 1 (or the payment or whatever).

I've tried if(match(counter,list of quarters where expenses should be paid),1,0) and If(or(counter=list of quarters),1,0). The last one gives me the right answer if I highlight the equation and hit F9, but VALUE's out when I'm not hard calculating it. WTF.

Help!

I would need an example spreadsheet. If you zip one up and attach it, I might be able to help. I am trying but can't make sense of the question. Also PM me if you do so I don't miss it.

 

[snuffy]

Originally Posted by dsgNYC

I'm not quite sure I understand what you're trying to accomplish. Are you trying to get a final list of only the unique names from column A and the sum of all of their corresponding percentages from column B across all 6 worksheets?

Yes. The final summary is list of unique names from A and the sum of the corresponding percentages. Any tips/excel functions I can look into?

Thanks

 

[superego]

Originally Posted by haganah

I'm tired today and can't think.

1) If you're plotting ROI figures over a year, would you use a linear trendline or logarithmic or one of the other options?

2) And is there any way to show the beginning value for the line and the end value?

1. Depends on what you're trying to depict. In this case, I can't think of a good reason why you'd use a log-scale to plot ROI over a given year.

2. Like, a number over the first data point, none for those in the middle, and a number over the last data point? Whenever I've needed to do that, I've simply inserted the labels and then deleted those I didn't want. Alternatively, you could simply insert a text box into your plot showing the values you wish to display and move them to wear you want them.

 

[dsgNYC]

Originally Posted by snuffy

Yes. The final summary is list of unique names from A and the sum of the corresponding percentages. Any tips/excel functions I can look into?

Thanks

I don't know of any formula that'll automate the process of getting a list of unique values across multiple worksheets. VBA code could definitely automate this. Your issue is very simple to deal with in a database, but will require more complexity to complete in Excel.