Re: VBA to Find value and paste into dynamic range
Many thanks Stephen.
I was trying to achieve this using the find function, but this is great.
Dave
Re: VBA to Find value and paste into dynamic range
Many thanks Stephen.
I was trying to achieve this using the find function, but this is great.
Dave
Hi
I'm trying to paste a value from oneworksheet to another (invSheet toPayment sheet), which sounds simple enough.
Ihave 4 ranges in the Payments sheet. These are PayNames, SeptPaid,OctPaid andNovPaid.
Myissue is that I need to find the name in the PayNames range that matches cell B4 on the inv sheet(is also on B16 onthe payment sheet), then paste in the amount from the produced invoice(Invsheet) - in this case £40.(B7) into the appropriate column for the month being used.
Eachmonth a different range for pasting purposes is used. I've attached a sampleworkbook .
Ihope this is clear.
Dave
Re: Return list of values based on adjacent cells
Thanks, that's working fine. Wanted to sort the list but it appears blank cells cause an issue when sorting from A to Z and they are placed at the top of the list
Guess I could replace them with ZZZ and do a find and replace.
thanks for the help
Hi Forum
I’m trying to create a list of names for a register. I have two sheets on the attached example where the attendance is in the source sheet and list of names ( those in attendance) in the register sheet. Sheet is blank apart from the days of the week.
I was going to use MS query connecting one worksheet to another to only return the name of the child who had a value of 1 for a given day, but this means 10 different queries for the 5 days of the week.
Is there a more efficient way of achieving this. Should I be using the offset command ?
Thanks
David
Re: Conditional Average By Criteria
sorry for not getting back to you guys earlier. I'll also read the "anatomy of a good thread title" as I often struggle with my thread titles.
I'm assuming if I want to include a 3rd criteria I simply extend the formula accordingly, ie the cycle type in column c, I add the range $C2:$C11 and check this against "cycle type" which would appear in a cell in L4 (replacing "vacuum sintering")
thanks
Dave
Hi forum
Firstly may I apologise for the thread title if it seems a bit vague as I'm not sure on which approach/solution to use.
I have a table of data attached(stripped down version) in the workbook below which I need to query information from. The resulting data must be shown in another summary table called " table1 vacuum sintering at 1360"
Each area(column A and column J) must match and also the cell K7 must be the average of all the corresponding cells(HC COR Mean) that have the criteria of being at the temperature 1360 (cell N4). Therefor the average would be 4.0
Obviously I need to repeat this scenario for other temperatures ie 1410 in the other table and other measurements ie COM Mean
I'd normally use a pivot table to summarise but I've been told to use this format.
I did try vlookup but didn't think this would work in this case.
Wasn't sure if index match would be the way to go with this one either.
Any help on this greatly appreciated.
many thanks
pukks
Re: Delete Rows If Data Exists On Other Worksheets
I'll just need to delete all the rows that contain any data.
thanks
pukks[hr]*[/hr] Auto Merged Post;[dl]*[/dl]tried the following but my result was that the contents in the data sheet were cleared and the other sheets had all their data still in it. I'm invoking the macro from a button and the code is place in the "this workbook" part of the vba project. Any ideas as to why the code isn't working.
many thanks
Dave
Hi.
I have a workbook thats starts life with just one sheet. This is always called "data".
On the click of a button several other sheets are generated with the new sheet names being of all of the 'distinct' items in column A of the data sheet. So if my list in the data sheet contains items x,y and z so the sheets are named accordingly. And I thank this forum for the code to do this.
Another macro beckons for the next bit.
I now however want to delete all of the rows with information in that exist in all of the other sheets other than the 'data' sheet. How can this be achieved if the sheet names change regularly.
Hope this is clear
thanks
pukks
Re: Paste Rows Into New Sheet When Criteria Met
I would have preferred the pivot option myself but the end user will have the workbook linked to powerpoint as a screen saver. Therefore each sheet needs to be separate.
HTH
Pukks
Problem now solved code working,thank you Mr Hawley and Co
Re: Paste Rows Into New Sheet When Criteria Met
Hi
I used the code option
but came up with a compile error, "invalid or unqualified reference"
The line highted in the vba was
and the word AutofilterMode was highlighted, Any ideas
thanks
pukks
Hello Forum
I have an example spreadsheet attached which contains 5 sheets. The 1st sheet ("raw data")contains the master data and the other sheets (which are blank at the minute) are the destination sheets.
I'm after a macro that will iterate throught the "raw data" sheet and when it finds the first cell in column B that starts with 'POPRS' it copies and pastes this and the remainder of the row (through to 'H') into the new sheet 'POPRS' and continue to do this for all instances where column B cells start with 'POPRS'. Likewise with the rest of the Grades in this 'Raw Data' sheet.ie Instances of PORAW,PORTP,POPRS etc. Column 'A' must be pasted also
Hope I've made this clear.
many thanks
Pukks
Re: Extract Nth Character Onwards To The End Of The String
Wigi and Andy
Thanks will put these to test later this morning
pukks
Hello Forum
Here's my dilema.
I have as an example two products. One called PORAW VC and the other PORAW WC30
I need to extract from the 7th character (in this case) to the end of the string. The string length always changes and to complicate matters some products dont have a space in their name.
eg PORTP060DUS ( here I want to extract just the 060DUS )
PORAW VC (Just need to extract VC)
PORAW WC30 (Just need to extract WC30)
I suppose this is some sort of dynamic extraction
Hope this is clear
pukks
Re: Odbc External Database And Query
I may be wrong but I think the odbc data source thats set up needs to be of a different type.
ie MS Access i think needs to use a "file.dsn" and MS Excel a "system.dsn"
Have a look in Control Panel/ My data sources to see which is active and set up for sybase as you may have to create a new data source with the sybase driver.
HTH
Pukks
Hi Forum
I've been playing around with a bit of sql as I need to create an sql pivot query that connects to an SQL2000 server.The "pivot" function is only available in sql05.
Unfortunatley I'm not having much luck with displaying or retreiving the results in the order in which they should be.
My sql qry attempt is thus:
SELECT
MAX (MLIADE.KFITNO), MAX(MLIADE.KFBANO),
MAX(MLIADE.KFANCL), MAX(MLIADE.KFACVA),
'W' = MAX(CASE WHEN KFANCL='W' THEN 1 ELSE 0 END),
'COM'=MAX(CASE WHEN KFANCL='COM' THEN 1 ELSE 0 END)
from PROD3_Staging.dbo.MLIADE MLIADE
where (MLIADE.KFFACI='155') AND (MLIADE.KFITNO='poraw wc25d') AND (MLIADE.KFBANO='0524')
group by
MLIADE.KFACVA
I may have got it totally wrong above as the output I want to display are attached in jpeg image below.
Note I will not be outputting the data to excel.
The field KFANCL is the Element(s) Measured ie W,MO,CT and KFACVA is the respective value.
Hope this makes sense
thanks
pukks[hr]*[/hr] Auto Merged Post;[dl]*[/dl]I forgot the attachment
Re: Sumproduct N/a Errors Returning
Thanks Ger
There's me looking for the over complicated option again.
Incidently, why did the sumproduct formula fall over ?
pukks
Hello Forum
Found another thread entitled similar to this one but no luck for an answer.
I have attached a sample workbook where in using the sumproduct function I get a #VALUE
error returning.
Basically I'm trying to sum all the weights of all the cells that begin with POREJ.
Cells C11:C22 contain numbers(weights) but not all the time and cells E11:E22 contain Item descriptions, but not all the time as they are sometimes empty too
My formula (h13)seems to work when there are no empty cells when I adjust the range accordingly say to C13:C15 and E13:15
Any ideas.
Pukks
Re: Pull 1st Word Only From Cell
Thanks Jindon
I suppose I can reverse this with the right function to only extract the right sided word from the cell.
pukks
Re: Dynamic String Manipulation On Merged Cell(s)
Should be at top of thread but here it is.
Re: Dynamic String Manipulation On Merged Cell(s)
should be attached now.