Posts by Chrischris

Quote from royUK

I'll take a look as soon as I can

Hi Roy, I'm sure you have loads on your plate at the moment but I was just wondering if you had a chance to look at this yet?

As always - much appreciated and hope you and your family are keeping safe and healthy

Thanks Roy, much appreciated

That is true, but you know how difficult it can be with stuff like this when people have a specific stuff in their minds....

I do think it is the last thing they really want so hopefully almost there

Hi Roy,

Yeah apologies for that. Think they have a very specific format in mind in terms of usability and they are pushing for it.

Thanks a lot for your help and input, I greatly appreciate it

Hi Roy,

Thanks so much for this, I think that, in terms of row adding functions, is exactly what the team is after. Is there any chance/way to also have sort of option box that would allow the user to choose people from the list on the other tab of people to add in newly added rows' C column?

In ideal world the list would be searchable in the box(?) that pops up when the rows are added but unsure if that is possible.

Appreciate your help a lot, thanks a lot

Everyone argues that they would use all the rows

Do you not think one button could do the trick?

But as always - very welcome to any ideas and demos you can provide

Hey,

yeah they really want a button that would insert multiple rows (one above each "Total")

I did and I really liked the double click function but I ran it past some of the managers who will need to use the template the most and all of them really want to have a button that inserts all the rows above the "Total" cells in the C column o was just wondered if there is a way to do that (and highlighting the cell etc as per my previous reply)?

I really appreciate your help and input of this so thank you

Hi royUK,

Any chance you could help out with the part for a Button that would insert new,blank table rows above every "Total" in column C? And in the ideal world text like "New Employee" would be inserted in those added rows' C column and become "selected" in order to be able to type a new name once and all inserted rows would duplicate the same name.

Hope ^ makes sense

Thanks a lot in advance

Hi Bosco_yip,

Thank you for your reply - I have tried to use the formula you provided but the "Total" rows come up with no value, any chance you.

Those references to column T slightly confuse me, any chance you could elaborate?

Thanks in advance

Hey Excel Gurus,

A quick question - I have tried to google around as in my head this seems like a straight forward thing.

I basically want the excel autocomplete/predictive typing in Sheet 1 Column C to pull the prediction text (used for autocomplete, whatever you want to call it) from a list on names that is in Sheet 2.

Is thee a straight forward way to do this without having to copy the whole list above the cells in Sheet 1 and then hiding those rows? Also I don't really want a dropdown validation, just straight forward suggestive text.

I have the Sheet 2 names' list as a named range etc; but not sure if what I want to do is viable?

Thanks for any help in advance, much appreciated.

Good afternoon all,

I am building a customer template to import system generated reports. I am slowly getting there but I have hit an annoying obstacle that I am unsure how to get around.

I have attached the a sample file.

And I know having subtotals in the middle of the table is an awful practice but there is no way around it as it is auto generate report.

Basically, I am using an IF statement (O column) to tackle this and to basically sum totals for hours (will use it for total cost too), if the C column has "Total".

The current formula is

=IF(C12="Total",SUM(OFFSET(IF([@[Employee Name]]="Total",[@[Total Hours]],""),-1,0,-COUNTA(B8:$C12))),G12+N12)

with B8 part being the one I can't figure out.

P.s I am not very experienced with formulating my formulas so I completely get that it might be ugly

I think in its essence it works, however what I can't figure out is the COUNTA part of the formula as I need the formula to automatically get to first cell is column B that is not blank. In the uploaded file I have just free-typed "B8" because that is the part I can't figure out.

Because I'd like the formula to find/use the range between every "Total" in column C and the first cell in column B above it, that isn't empty.

Hopefully this makes sense from the upload file. (I have dragged the formula down to every other cell in the O column, but you can see how my "free-typing" makes it from for other totals.

Also, I want the formula to be able to handle the fact that I have a macro that inserts an empty table row above "Total" if I double click it, so I'd want the formula to be able to count for it too.

I hope this makes sense but do let me know if it doesn't and I will try to explain. Also a lot of thanks in advance to anyone who spends their time and tries to help me out; greatly appreciate it

Demo file for Macro help.xlsx

Everything in Columns A to L are just copied so the totals for each Task are there and realistically people need to see those totals, so I am just trying to figure out a way to tailor Columns O to R to mimic the same format in terms of information shown.

Hi Roy, thanks for your reply.

The problem is that the auto report, which is partly copied into the said table to build the rest of it has to include those subtotals.

Hence, I always knew that conventional excel functions won't be able to understand what I want them to do.

My idea was to somehow make excel understand what I want a sum in the last table column if the column C is "Total" and to lock in the sum range - somehow identify the cells based on column A and/or B values not being empty?

Unfortunately, there is nothing I can change about the formal of the data export and it is quite crucial I don't change it because loads of teams use it in the current format so I'm looking for a clever way to basically work around the excel rule breaking

Hello,


Been using the forum for a while to find all my VBA related query solutions and been pretty successful so far. However, I am in a process of working on an increasingly complex template and with my limited knowledge of excel and VBA - need some of the Guru's help.

I have attached a sample file that, in its essence, is the same as the template I am working on.

Data is produced by a system and copied - everything from column A to column L and columns O, P, Q and R are manual and has some formulas in.

What I want is a button on the sheet that, when clicked, adds a new line in each of the tasks (above each "Total" in column C) - ideally I'd want the new lines to also have formulas that other lines in columns P, Q & R have.

To make it even more annoying, in an ideal world I'd like the newly added lines to give an option of selecting the employee from the same list that the vlookup is referring to in the Rates table column. (e.g. 3 new lines are added in the existing table providing an option of some sort to select, which employee to add (the same for all three lines)).

I hope that made sense.

Onto Problem number 2 - I have tried to look, but can't seem to find a solution that works for me. I need a quick an easy way to Add up the totals within the table for each task. e.g O19 should =sum(O14:O18) and R19=sum(R14:R18). Usually once I paste the system data, there are hundreds of lines so I am trying to find a good way to automate these calculations (perhaps another button that can be used once the inside data has been manipulated as needed and ready to total up?)


I hope this makes sense but definitely let me know, if I can clarify some things up and huge thanks in advance, I really appreciate any help and time anyone is willing to spend replying [Blocked Image: https://www.excelforum.com/images/smilies/smile.gif].

Demo file for Macro help.xlsx