# back

1. ### Referring back to calling spreadsheet from presonal.xlsb

I have a bunch of macros that I keep in a personal.xlsb spreadsheet so they are avaialble to all of the workbook I use. When in a macro in personal.xlsb, I refer back to the calling spreadsheet using activeworkbook. That works as long as I only have 1 spreadsheet up but if I am using excel and...
2. ### Fixed running total

Hi I know how to total sum cells together but- I need for example, if f5,f6,f7 = 1 (f5 has 1 in it) and i change the value in f5 back to 0 the sum total stay fixed at 1, so if f6 was to show 3 it would =4 and keep an ongoing total regardless of numbers changing back to 0 in above cells, make sense?
3. ### Missing report tabs

New to Power Bi, first report. Not sure what I did, but had two named report tabs on the bottom. Now it just says "Page 1 of 2" with no tabs. Any help with getting back the tabs would be much appreciated!
4. ### Dual function within cell

I want a cell to calculate a value (in one cell) x rate (in another cell) unless: I type text or a number directly into the cell. I want to be able to overwrite the cell formula as long as I type something in, but for the formula to come back and apply if I thereafter delete its contents...
5. ### Reverse Formula.

I'm looking for a reverse formula that will give me back the number of the beginning meaning : 0212010 would give me back 2436 ? in cell : R8 Q8 =IF(\$I8="","",INT(LEFT(MOD(SUM(\$H8/1),4)))) P8 =IF(\$I8="","",INT(LEFT(MOD(SUM(\$H8/4),4)))) O8 =IF(\$I8="","",INT(LEFT(MOD(SUM(\$H8/16),4))))...
6. ### Counting the top "x" ammount of cells

Hi, I have a spread sheet that has the "date" and "volume" in the same row for a market going from newest to oldest. I want to somehow build a driver that i can change the look back but say i want to see listed the top "10" or "20" or "25" highest volume days, how can I do this? As you can...
7. ### sumif, index match (multiple??)

Hey everyone ... first post and hoping someone can help I have this formula and i want to amend it to include 2 more match criteria but i dont know how ....:mad: =SUMIFS(INDEX('SKU REPORT'!\$D:\$AG,,MATCH(\$J\$2,'SKU REPORT'!\$D\$4:\$AG\$4,0)),'SKU REPORT'!B:B,DATA!A:A,'SKU REPORT'!A:A,DATA!K:K) can...
8. ### can i set a custom background for a cell from external file as a fill?

can i set a costume back ground for a cell from external file as a fill?
9. ### Conditional Formatting

Hello, I have a spreadsheet with two columns, one has an area name in and the second column has the branch name. Some areas could have 5 branches and some could have 15 branches. I would like to format each row based on the area name. So if its area 1 then say a light grey, if area 2 then a...
10. ### Form disabled worksheet

Morning all, I have an file I open(file defaultsd to cell C2 which Must be filled in.) with a click box in a form. when the workbook opens I can't edit the fields. the cursor is a thin lined cross , when i click it turns to a plus sign then back. when I try to close I get my message to fill in...
11. ### Data source reverts back to old selection

On my pivot table, I click 'change Data source' and click the table range that I need and click OK. I go back to the pivot click refresh, nothing happens, go back to the Change Data Source' and the old Data source is listed. Range - 'Open PO Raw Data'!\$A\$1:\$AB\$26160 (old source) range -...
12. ### Rounding macro that doesn't change the cell contents (universal)

What I want is a Ctrl - o (say) marco, that takes an existing cell, whatever's in it, and adds "=round(" to the front and ",0)" to the back. If I try to record a macro, with or without relative references, when I call it again, it repeats the cell contents I was recording the macro in...
13. ### why is copy and paste so tricky with vba?

maybe its just me? anyhoo, i have a routine which opens another workbook, moves the data around and adds an autofilter. It should then copy the autofiltered range back to my source workbook. With ESMwb.ActiveSheet.AutoFilter.Range .Offset(1, 0).Resize(.Rows.Count - 1).Copy...
14. ### DATEDIF when two dates are the same

Hi I have this formula which i am using to find the number of days difference between from two dates DATEDIF(F5,P5,"D") when the two dates are the same it is coming back with 0, how do i change this formula so when the dates are the same it brings back 1? Thank you
15. ### Chart Colours driving me insane

Hi I have set up some charts (40) from a large table of data Each chart shows 7 different values on a combo chart off 2 vertical axis.Misture of clustered column and line with markers I have set up 2 drop down boxes that allow me to select the date and number of days back from selected date to...
16. ### Limit to Last row with data

I use this line below to get the last row with data and it works fine. In a search, the last row came back with 1,048,532. It did have some text in a cell, so it worked properly. How can I limit the search so that it does not come back with a row greater than 10,000 ? lastrow =...
17. ### How can I make this spreadsheet work?

I'm working on a spreadsheet which needs to populate info for an entire year. What I need the Total Meter Worked columns to do is if the end meter is a zero, to populate a zero. If the end meter has a number, then to subtract the end meter from the previous month. However, if the previous month...
18. ### how can i get rid of #DIV/0! error

his guys i have the below formula which pics up the average day from a different sheet IF(AND(MONTH(MAX('Days'!\$Q:\$Q))=5,YEAR(MAX('Days'!\$Q:\$Q))=2019),AVERAGEIFS('Days'!\$S:\$S,'Average Lead Days'!\$E:\$E,\$D17,'Days'!\$R:\$R,"Qualified"),"-") however when there is no data it comes back with a...
19. ### Autofilter Default

I have a sheet that goes back some years. Its a pain to view old data so I auto filter it to the current month. But other ppl access it to and mess up the filter. I am now turning off the filter at close and back on when opening. How can a set the filter to only show the current months...
20. ### Asus computer corrupts xlsm file?

Hi all, I have a 1 year old Asus laptop (gamer, so pretty high specs) a 3 year old MSI PC (also gamer, practically a mini nuclear power plant) and a 6 year old Lenovo (which barely lives and even 6 years ago was very poor specs.. I used it to take it to school to make notes, and thats about...

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...