Hi all,
I have been provided with some data that appears to have been produced from a pivot table. The data (1 column, 22 rows) has also been provided with the original level (or indentation) number. What I am trying to achieve is to write one formula that can convert the data back into the...
Hi p45cal,
I've finally got it all working (see attached).
Just wanted to let you see the outcome to close it out with you and help out others who might need this for calculating tax rates.
Thank you very much for your help with this.
Matt
PS - I have no idea why the ATO truncate and add .99...
Hi p45cal, sorry maybe helper wasn't the correct term. I have updated my spreadsheet to show a larger dataset and the formulas that are currently working that I'd like to replace. I have tried modifying your formulas (cells P1 & P2) however when I paste them into the cells below they return the...
Hi p45cal,
Wow! Thanks you for your comprehensive response. Just when I thought I knew a bit about Excel...
I was hoping to copy formula from J10 and repeat down page for a larger data set (I'm going back over the years to calculate how much tax I should have paid for various different pay...
Hi, I have 2 values (date and number) that I need to do an approximate (less than) lookup on 2 columns. I have attached relevant file with my current VLOOKUP formulas highlighted which only have 1 lookup value (number). I'm trying to add the date to this formula so that the lookup range will...
Hi p45cal, works perfectly. I didn't quite articulate exactly what I wanted in my initial post but your first solution is exactly what I was after ie having the source worksheet sheet name in every row in column A. Many thanks! Matt
Hi all, I have the following code which combines multiple worksheets in a workbook into one sheet called "COMBINED". I would like to modify this code so that it includes the source sheet name in column A of the "COMBINED" sheet and have all of the worksheets pasted from column B onwards. Any...
Hi all,
I have a spreadsheet (attached) where I record lotto number selections and results. I would like to modify a couple of formulas as follows;
Column K - I currently have to manually adjust each week to change lookup range of results eg in cell K3 it refers to C$17:I$17. Formula would need...
Hi vletm. The "Flights Required (return flights)" table only captures when a return flight is booked and paid ie C45. The "On-site Manning Levels per Roster Crew" table shows the number of men on site for each week on the project. So for roster crew 1, there will be 5 flights booked and paid...
Thank you for your detailed response Peter. You lost me after "matrix equation"... I have had a look at your attempt and note that it has negative values (S24, T24 etc) which is not correct. I was also hoping to not rely on "helper" formulas although I understand if this is not possible. Would...
Hi. I am trying to automate a spreadsheet to calculate manning levels per roster crew as per attached spreadsheet (range C24:V27). I have tried many types of formulas (COUNTIF, OFFSET etc) without much success. I'm thinking it might be too much for a formula given the variables involved. Any...
Thanks Bosco. Works well except for item 3 in your post. Is there any way that this formula could be applied to "Cable Schedule" sheet "Drum No" column? This is the field that is used to select an appropriately sized drum and it would be great to see metres left on drum when making this...
Hi. I need to create a dependant data validation list based on the adjacent cell value. I have done this before as per link below
http://www.contextures.com/exceldatavaldependindextablesindirect.html
This method however does not suit my requirement. My spreadsheet is attached and my requirement...
My bad. It works fine now that I am back on the company network and the connections are active (they were inactive when I tested earlier).
Thanks Chihiro!
Hi SirJB7. I followed this link and assume you are referring to the link you suggested at comment #13? This addresses part of my question but does not specifically relate to tables and table headers being used in code; is this possible?
Hi Chihiro. Thanks for your reply however I am receiving an error message as follows;
Compile error:
Syntax error
The following code appears in red;
cn.Delete
qr.Delete
Any ideas?
Matt
Hello. I'm trying to apply VBA to conditionally format a table as per attached file. Specifically, I would like to use the table header references within the code. I can do this manually via the ribbon however Excel has a tendency to fail with conditional formatting when you insert rows hence...
Hello. I have a workbook that I use to create a report for our client. It has 5 sheets and each sheet has it's own Data Connection (DC), 2 from Excel files, 3 from our company's proprietary software ie a .rpt connection. I have written a macro to filter each of these DCs, copy and paste visible...
Thanks again Nebu.
Still not quite there though. I've looked at your code and can see PasteValuesAndNumberFormats and then you have applied various other formats. Is it possible to paste everything except for formulas? This would then include all formats that I'm wanting ie numbers, alignment...
Hi Nebu.
Works great. Is there any way to export all formats as well? (numbers, alignment, borders and shading) I have split the description field on the Variations sheet because I need to add some data above the headers and need to align it differently. Due to this I have had to shade the cells...
Hello,
The intent of my spreadsheet is to use it as a 1 stop shop for pricing and tracking project variations. It is structured as follows;
1. Register - Summarises all data from Variations sheet
2. Variations - Data entry sheet to populate all variations information
3. Database - List...