Posts by pukks

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.