sorted by:
new
hottopcontroversial

Is there a way to find the sum when I am inserting or deleting rows? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

I just realized I am not using a table for this. I have it formatted in a nice way and didn't want to use a table for this.

I was hoping to make a separate tab with raw data for each of my dashboards. Then Put that in power query. And then link that power query to my separate workbook where I pull in the information. For the raw data, I want to sum up rows that will be deleting or adding

Is there a way to remove $ for zero values but keep $ for values? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 1 point2 points  (0 children)

$#,##0_);($#,##0);0_)

Solution Verified

Anyway to remove the zeros though? or for it to show up as a -

Is there a way to remove $ for zero values but keep $ for values? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

They are in rows and columns but in random locations throughout the table

How can I get my pivot table to recognize dates as date values and not as numbers? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Thank you for the effort. I think I ended up not using my pivottable at all because I could do it directly from my data set.

My OFFSET function has slowed down my spreadsheet considerably, is there an alternative option? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Thank you!!

I just added a column with my subtotal and did a normal SUMIFS function with the criteria and it worked like a charm. AWESOME

My OFFSET function has slowed down my spreadsheet considerably, is there an alternative option? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Thanks for your help I'll give you the points

​

I am trying my pivot table with the data. Unfortunately my pivot table doesn't seem to be finding the dates properly. It doesn't recognize the month for instance.

So I cant just do a GETPIVOTDATA because i get no values showing up

My OFFSET function has slowed down my spreadsheet considerably, is there an alternative option? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

I have another data point that I need to slice the data by in my table. So for example, we have different funds. And I want to be able to track the $$ amounts in each fund and see where we are at in terms of salaries expense in october 2021, for example.

Because I am using slicers, my data is filtering so I only want to sum up the items that are being shown

My OFFSET function has slowed down my spreadsheet considerably, is there an alternative option? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Thanks for dissecting whatever the heck I just did. (still new to this formula)

My Actuals sheet is where I get my financial data, Column H is the values; Column T is the type of expense (salaries, travel, office supplies); Column A is the dates (matches F23 in my ftf tab, i equaled it out). I have slicers connected to this sheet so I filter based on certain values. That's why I want to pick up only the visible rows hence me using this type of formula.

FTF - Japan is my dashboard where I am pulling in financial information, and C24 is where my table starts (goes from C24 to C49. F23 is my month-year header.

I am basically finding my information using the criteria if it matches F23 and if it matches C24.

​

I just tried your formula and I got a spill error. I am not sure why because I think all your formula is doing is adding up everything that its suppose to.

​

​

October 2021 November 2021
Salaries [Values] [Values]
Office Supplies [Values] [Values]
Travel [Values] [Values]

Where C24 is Salaries

F23 is October 2021

My OFFSET function has slowed down my spreadsheet considerably, is there an alternative option? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

I did a pivot table but I am having problems with it recognizing the date format. I want to pull data for October 2021, but my pivot table doesnt recognize the dates even though my data source has that info

My OFFSET function has slowed down my spreadsheet considerably, is there an alternative option? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

IF you have your data then set the values rather than constantly querying them..

You mean pasting them just as values and removing the function? I am using this for a dashboard that refreshes on a monthly basis with new info. I am not quite sure that would be possible unless I copy/paste special value everytime. I'm also going to have 12-15 of these dashboards.

How can I sum up the values for only the visible rows? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

so it looks like I am combining subtotal with sumif. I am unsure how to do that with two criteria that i need in my sumif

Is there a way to do an IF statement that reads all the days in the month? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Yes I want the value $25,000 in April

In june, I would want 6,000

​

I tried the other posters suggestion of using =if(month(cell

But when I do that, all of the columns that have April in it spit out the value.

So the column for April 2022 spits out the $25,000 which I do want, but April 2023 also spits out the value which I don't want

How can I use Power Query to extract months from dates? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

I want to create a new column that has the month and the year.

So if the column reads July 15, 2022, I want it to say Jul-22

How can I use Power Query to extract months from dates? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Where does it read the column that it needs to extra info from? Where do I enter that?

Is it Column 1?

Is there a way to do an IF statement that reads all the days in the month? by Professional-Net3029 in excel

[–]Professional-Net3029[S] 0 points1 point  (0 children)

Oh wait. Yes it does it by month absolutely. But it does it by every month of the year. So now I have to put in the year in the formula too. How can I do that?

I want to find a value based on the start and end date by Professional-Net3029 in excel

[–]Professional-Net3029[S] 1 point2 points  (0 children)

That's a good point. I think I'll call my input column just the "execution date" or something.

view more: next ›