Hi

I am developing a toolkit that will automatically calculates assumptions based on data for those cells where data is missing based on an average of the data we have. In context I am trying to work out energy use across a number of sites where a small proportion of use has to be based on the averages of the others as we dont have the data; the assumption is based on per members of staff against the estate average - still with me hehe?

I have looked at colour coding under VBA and it works but I need my spreadsheet to not be so garish.

The problem I have is that if I use a SUMIF against another SUMIFS formula the overall underpinning data changes which affects the assumption calculation and the output number.

What have I done wrong here? =(IF( E16,(G15+E16))(F16,(G15-F16))) to add or deduct from a balance. Thanks

I need a formula that will do the following: If the current target is within 33% of the proposed target, increase/decrease to the proposed target, if the current target is greater than 33% away from the proposed target that add/subtract 33% of the current target, if the current target is 0 keep at 0. For example, if the new target is 100 and the current is 80 move to 100, if the current is 50 move to 83 (50 + 33), if the current is 110 move to 100, if 160 move to 107,etc. I created this formula but it only works when the current target is 0 or below the proposed target. I cannot make it work when I add to it for when the current target is above the proposed target. =IF((CZ4=0),0,IF(AND(CZ4>CW4*0.66666),CW4,(CW4*0.33333)+CZ4))

Any suggestions?

Can anyone please help?

I have a standard worksheet with some cells highlighted in yellow. The colour is random and of no particular significance. I’m trying to fathom out how to get a sum total of all these random yellow cells. I’ve checked on-line and found that the most common answer is to copy the links to streams of data and paste into a VBA, but this doesnt seem to work. I just keep getting the response #NAME? appearing in the cell. According to all other forums these links seem to be fool-proof but I’ve no idea what I’m doing wrong as I follow everyone’s instructions to the letter.

Please go easy on the IT jargon as I’m not an expert and still prefer vinyl to CD’s!

Hii

I have three coilumns

H I J

Net VAT TOTAL

I need to work out how once we enter each nett it will work out the vat and total. (iideally it can also work backwards from the total>vat>net)

However as we have a lot of entries I wonder if there is a way of making the whole collumn do the same sum rather than having to enter the forumla in each relevent box

I can then do a totaliser at the bottom of each collumn.

any help mucho apreciated

Richard

When you use Redim Preserve you may occassionally come across the error “Run-time Error 9: Subscript Out of Range”.

If you use Preserve then you can only resize the last dimension in your array.  You must also specify for every other dimension the bound it had in the existing array.

So, for example, if your array has one dimension then you can resize that dimension and still preserve all the data of that array because you are changing the only dimension, which also happens to be the last dimension.  If your array has multiple dimensions (two or more), then you can only change the size of last dimension if you use Preserve

I am trying ot set up a WIP along with a time line.

We would like to have ability to print as with a WIP or a time line.  Is there a way of having a sheet which is the master sheet where we input bot WIP and timeline (which correlates with the WIP) and then have 2 other sheets – one for just WIP (ie first 6 columns) and second fr time line (ie column A plus columns F-Z).

I have tried the =’MASTER’!A1but when a new row is added to the ‘MASTER’ sheet it is not replicated in the second sheet.

Is it possible to have the sheets set up so they automatic update from the MASTER (data and formatting ie colour)?

(sounds too good but thought would ask) L

I have a spread sheet with 5 sheets, sheet (SH)1 is the master sheet, sheets 2, 3, 4 & 5 are different routes, on the master sheet i have 4 columns, col1 route number, col2 is contract number, col3 is customer name and col4 is address, i want to be able to input info in to each row and the info is automatically droped in to the route sheet that corresponds to the number in the route column. is this possible in excel.

I hope i am making sence, looking forward to your reply

Hi,

I need some help with excel 2007 conditional formating. I have a sheet with payment due dates and a drop down box with a Yes and No option. I want the row to change colour to red, If the due date has passed and the drop is equal to No.

Hi Guys,

I was wondering if you could help please.

I have two spreadsheets,

Spreadsheet A is my data entry sheet and is split by commodity. Within each commodity TAB you have depots, months, invoice net value.

Spreadsheet B is my summary spreadsheet split by depot.

The problem I currently have is that I’m having the enter the data twice as it’s split into different sections.

Idly I’m looking to have a formula in spreadsheet B that picks the month, along with the depot and it’s nett amount from spreadsheet A and places everything into the right commodity/monthly cells.

For example;

Spreadsheet A

Is this possible?

Thanks for you help.

Hi Guys.

I’m really basic at this stuff! I have the same data input columns on 6 (reps) worksheets that I need to automatically populate 1 master worksheet (same document). Is this possible, can you help.

Thanks in advance.

Hi!

I am trying to construct a macro in Excel that fetches information from many separate workbooks. I would like my Macro to be able to run from Excel 2010 and fetch the information from other workbooks (which could e.g. also be running in Excel 2003/2007).

Problem (Excel 2010):
- For …Next doesn’t work properly
- As long as all workbooks can be reached by Clicking Excel Ribbon/View/”Switch Windows”, it works.
- When multiple parallell windows are open, only the workbooks contained in the same window are looped by the” For …Next” command.
—————————————————–
For i = 1 To Workbooks.Count
Workbooks(i).Activate
[lots of coding...]

Next
—————————————————–
Cheers, Andreas

Hey,

I would like to know how to apply conditional formatting to drop down lists in Excel 2007. I have got around 20 different drop down lists each containing different text. I would like to have a column next to the drop down list that shows an different coloured indicator when a particular option is selected, in this case to show that if the option is selected then our risk will increase. Hope this makes sense. If not, let me know and I will try to clarify further. Thanks.

Hello,

I need to lock columns a and b in place so that when i scroll right over to the right, i still have those 2 columns on the left no matter how far over i scroll, how do i do this?

Thanks,

Astra

hi,

when i  export  data from database to excel using DLL(for Excel2003)

there is no problem in creating excel file and we are able to successfully view the exported file.

recently we upgreaded our office 2003 to 2007 and after that when ever we try to open the exported file(2003) in 2007 it is throwing the POPUP as:

“Excel found un readable content and if you want to repair you may loose some content in the work book.”

when we press “YES” all the data is getting deleted and only numeric data is left.(all other format data is getting deleted)

can you repair the corrupted file or can you provide the reason ehy it is throwing error.

thanks in advance