Why not just make a pivot table?

Because I can easily do it with a PT. I was looking to expand my knowledge on available functions and formulas since Excel is solid. Now I know about FREQUENCY. Yay!

I'm trying to do a SUMIFS for a mess of a spreadsheet I have. I'm trying to pull numbers using criteria from both a row and column like this:

A B C D
1/1
1/2
1/3
1/4

So I'm trying to pull the value where 1/1 and Client A meet up at. Everytime I write the formula, I get a #Value error. I've been looking on the internet and it looks like I need to do some sort of SumProduct Formula? Need Help!!

It's not clear to me what's in A2. Is it 1, or is it 1/1? What is it that you want to sum?

I don't get it either. Wouldn't there just be one cell in your table where row and column intersect?

Yeah, not sure what you're getting at, but it sounds like INDEX and MATCH are your friends here.

Figured it out with sumproduct. I had multiple issues. Had some blank cells as well as some of the numbers were percentages. Once I fixed that sumproduct did the trick.

I have an excel spreadsheet that uses the value in a cell to make some other calculations. It's in C16. The value in C16 changes based on other data.

I have C16 rounded to the nearest whole number, but I've noticed the other calculations are using the full number, not the rounded result.

For example C16 shows 43, but the exact value of the cell is 42.73. I need the calculations that use C16 to use the 43, not the 42.73.

If I have a simple calculation in D16 such as "=C16*10" the result will now show 427.3. I need it to show 430.


How can I make sure all calculations that use C16 to use the rounded result?


Thank you!!!

Use the ROUND function in the formula

=ROUND(C16,0)*10

Should show 430 in your example

Thanks pudley4! The problem is that I have many calculations using the value in C16. It would take quite a bit of time to go to each formula using C16 and add the ROUND function to the formula. I'd also be concerned that I might miss a calculation in my spreadsheet that is based on the value of C16.

Is there any way to fix things by making a change to the format of C16 so other calculations using it will use the rounded whole number?

Or did I just mess up this spreadsheet?

Thanks for the help!

well you could derive the value that's shown in C16 in another cell, say C17, then make C16 =ROUND(C17)

Thanks LostOstrich. C16 is just one of several of these types of cells I was hoping to figure out how to correct. I might have to do what you suggested if there's not a better way to just change the value in C16.

Is there any way to make a change to C16 so that other calculations elsewhere in the spreadsheet will use the rounded value?

Thanks all!

Use the formula "=Round(whatever, 0)" in C16. Anything based on C16 should then use the rounded number.

So you didn't actually change the value in C16, you just changed the format? Then yeah, bware's solution is correct.

You could also do a find/replace to change your formulas: find anything that refers to C16 and replace it with ROUND(C16,0)

Thanks guys! Didn't know about either of those last two options. Good to know

Problem solved!!!

I use a drop-down list via Data Validation to keep track of task completion. Using conditional formatting, I highlight rows in corresponding colors based on what is selected in the drop-down.

I add the Data Validation to the entire column initially, but this eats up space and is clunky. How do I only add the validation based on other criteria so it only has the drop-down when it's actually needed?

For example, it only adds the drop-down when the Task Description isn't blank? I swear I've done this before with INDIRECT or OFFSET or something.

Thanks!

Not sure if this is what you're looking for, but in Data Validation > List (for source - put the below formula)

=IF(B2="","",List)

B2 is the reference to Task Description and List is the named range containing the dropdown items.

If B2 is blank, there will still be a drop down button, but it will be an empty list and thus you won't be able to click/populate it. Not sure if you wanted to get rid of the drop down button all together if it is empty?

Sorry, maybe I didn't explain properly. With that method, only values are omitted until a task is entered. I don't want the list itself to be there at all until.

Imagine this: Column A will be a list of foods. Column B will a VLOOKUP that checks what food group they're in. But I only want that VLOOKUP code to be inserted when the adjacent A cell has something in it. I'm just trying to avoid copy/pasting or dragging down the formula itself. So if A2 is empty, B2 should be empty. When I enter "Chicken" into A2, some external thing (conditional formatting?) should trigger and insert predefined code (VLOOKUP) into B2 and do it's thing.

Is this capable without VBA? And if so, can I apply it to a drop-down list?

Does column B have to be blank for a reason or can it just appear blank?

1) You should be using index match
2) could do something like =if(Ax="","",index(column you want,match(Ax,column to find match,0))

Just copy that down the entire sheet?

I guess I am still explaining **** terribly. I don't want to copy ANYTHING down anywhere. That's the point of what I'm asking about. I want the cell completely empty until another cell value changes. Not hidden until the value changes. I don't think it's possible without VBA and copy/pasting or dragging down isn't the worst, just wanted to see if this was possible. Thanks though.

I download data daily from a a custom data set that I created in fangraphs. The data changes on a daily basis, is there anyway to automate this process so it downloads directly to and updates an excel spreadsheet?

Thank you in advance if anyone can help....

Possibly, depends how it's formatted and it might be some work. Easiest way is if you can import it using Data - from web then you can write a macro to automate it.

Not sure it will update daily, I assume the link url changes possibly but you can try this.

Open Notepad and paste your link in there then save the file with the extension .iqy

Double clicking that new file should open up an excel workbook that fetches the data. Now just move that sheet into whatever excel spreadsheet you're currently using.

Also with fangraph URL's you may need to change how much data it is delivering. Sometimes fangraphs breaks stuff up into pages and when fetching that data you may only get a small piece. To change this look for "page=" in your URL or if it's not there try adding it to the end with "&page=1_1000". That is basically just telling fangraphs to display the first 1000 data points on each page.

Im trying to create a workbook that will import a productivity factor into a cell based on the values of 3 other cells in that row.

Im rusty, is by best way to start using "nested IFs" or "VLOOKUP"?

Trying to not thicken this question with too much non-important information. If I need to explain further, let me know.

Thanks in advance.

How is it figuring out the productivity factor? Is it taking the values of each of those cells and looking them up based on a function? Or is it mapping those values to a 3-d graph and getting the results based on that? You'll have a different formula based on how you get the results.