TrowaD's profile

Populate data from a column in one worksheet to a cell in multiple other sheets [Solved]

In a workbook, I want to be able to copy all the cells in column B of sheet "Raw Data" into multiple copies of an existing template sheet corresponding to each value. (note: not all cells have values). Column B in the "Raw Data" sheet has about 800 cells/values. I want the workbook to be able to: 1. Pick up the 1st value from colum... Read more

Hi Vee, Since I don't think the provided links will guide you to your answer, you can try the following code: Sub RunMe() Dim shName As String Sheets("Raw Data").Select For Each cell In Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row) If cell.Value = vbNullString Then GoTo NextCell shName = cell.Value Sheets("Templa...

Using IF to compare time values [Solved]

Hello, So basically I have a huge column of time values (00-00-00 format) and I am trying to write a code that takes the time in one cell (B) and compares it to the time in the cell before (A). If the time in B is more than 1 second later than the time in A, I want it to return the number 1 in the adjacent column. If the time in B i... Read more

Hi Keh, It works for me, at least after matching your query with the formula: =IF(B1>SUM(A1+TIMEVALUE("00:00:01")),1,"") Comparing column A to B and leaving the cell blank, when condition is not met. Does it work now? Best regards, Trowa

Extracting images from a database but named with a specific cell content.

Hi there people! I could use some help... I'm currently importing product images from a database into Excel using the Visual Basic code below: Sub URLPictureInsert() 'Updateby Extendoffice 20161116 Dim Pshp As Shape Dim xRg As Range Dim xCol As Long On Error Resume Next Application.ScreenUpdating = False... Read more

Hi Gordon, "Image #" is the default name given. If you want to change it to the values in column B, add the following line: Selection.ShapeRange.Name = cell.Offset(0, 1) Insert it after this line: If Pshp Is Nothing Then GoTo lab Best regards, Trowa

How to hide text in a Excel cell and display it when needed [Solved]

Hi friends I have doubt in excel. I have a column called 'Status' and want to show the text either as 'Pending' or 'Completed' depending on the result received when two other columns (Total Fees $ Total Paid) gets added. I have added the formula and applied to the entire column. The formula works. But the text 'Pending' is showing... Read more

Hi John, To do that, you will need to put in another IF statement: =IF([@[Total Fee]]="","",IF([@[Total Fee]]-[@[Total Paid]]>=0,""Pending","Completed") Best regards, Trowa

Seperating time from text [Solved]

Hello, I have a task in my office that i can't get done and it is very impossible to finish within 1 week as these are 14,000+ orders. The sheet looks like this. The underline time are the pick up time which I have to extract and put on the last row of the sheet that is under "pick up time" Please tell me how i can extract th... Read more

Hi Luigi, The formula below will extract the last 5 character of the text string of column H. The extracted string is changed into time format, so you can apply any time format you want. When the extracted string is not a time, then the destination cell is left blank. Here is the formula: =IF(ISERROR(TIME(LEFT(RIGHT(H2,5),2),RIGHT(...

conditional formatting to show when job is done

HI, I have work tracking file. all is running good only i want to have conditional formatting in it. for example:- in sheet dme, column f12:f50 is for planned work. all the dates in f12:f50 are sign of planned work column g12:g50 is for actual work dates(when the work is getting done, actually) my requirement is that if f12 is not ... Read more

Hi Sanjeev, Here is the conditional formatting window: 1) =OR($F12=$G12 The order of the rules is important. Also check the applied range. Best regards, Trowa

Excel project

I’m in pompano beach Florida searching for a freelancer for this excel project. Not much success yet. Any help would be appreciated

Hi Jpatierno, You are asking at the wrong place. We at CCM are enthusiasts who are willing to help others. Why don't you start with your Excel project. Whenever you get stuck and don't know how something is done, you come back here and ask a question. Moving you a step closer to completion. Good luck. Best regards, Trowa ...

Update Master / Summary Tab with all new entries in other sheets / tabs

Hello, I have a workbook with 9 worksheets; one of the sheets is the master; created to be the other 9 sheets compiled into 1. I need to set it up so that any new data entered into the new separate sheets is automatically entered into the master sheet, in the first blank row. It'd be great to add a "Run" button to refresh updates. ... Read more

Hi Santosjo, Consider using your Master sheet as your input sheet. That way you only have to implement 1 code and you can add as many sheets as you want. Otherwise you will have to create a button and implement a code for each sub sheet. Read through the following How To: https://ccm.net/faq/55882-how-to-auto-transfer-data-from-mas...

Working on the same workbook from 2 locations

Hello, my name is Marshall and I'm a Logistics Planner. I have created a load schedule on Excel for every day of the week, depending on how many vehicles we load. I have this workbook on my laptop, is there any way that i can display this specific workbook on a seperate screen that is situated in another office? When i type in the maste... Read more

Hi Marshall, Check your Review ribbon. Here you can find Share workbook. I have no experience with it and it seems to work different for different versions of Excel. Best regards, Trowa

adjusting code transfer data from combo box & textbox into userform to sheet

Hello, i would help adjusting this code it doesn't work it gives me error when i transfer data to sheet i want to begins transfer data from a23:g23 of course automatically when i fill a new data insert a new row under a23:g23 with the same format Private Sub CommandButton1_Click() Dim My_sh As Worksheet Set My_s... Read more

Hi Abdel, You are getting an error because you have TextBox# and ComboBox#, but not a TextBoxComboBox#. You could change: .Cells(Lastrow, i).Value = Me.Controls("TextBox" & "combobox" & i) Me.Controls("TextBox" & "combobox" & i) = "" into: .Cells(Lastrow, i).Value = Me.Controls("TextBox" & i) & " " & Me.Controls("ComboBox" & ...

Driver

Hello, looking for webcam driver for sony vaio vgn-fw230j. Thanks, Ali

Hi Ali, Not sure why you didn't use GOOGLE, since it will get you the answer immediatly. Here is the link GOOGLE provided: https://www.driverscape.com/manufacturers/sony/laptops-desktops/vgn-fw230j/9947 Best regards, Trowa

VBA: Delete empty rows based on value of cells [Solved]

Hello, my code works transfer my invoice from sheet1 to sheet 2 but my problem i would adjusted my code to become delete rows from a19: e32 if cells a19:a32=" " when press my macro becarful delete theses rows in sheet2 not sheet1 taking into consideration maybe occurs error in total the total =e33 and connected with... Read more

Hi Abdel, Ok, so you want to keep the transfered data, when you transfer a new data table. To be complete change the formula's on Sheet1: E19: =Sheet1!$E$9-D19 E31: =MIN(E19:E30) Then use the following code: Sub RunMe() Dim lRow As Integer lRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row Sheets("Sheet1").Range("...

For what I understand what you are trying to do is copy the table from sheet1 to sheet2 and then remove the empty rows. The code to do that is: Sub RunMe() Sheets("Sheet2").Rows("83:96").Delete Sheets("Sheet1").Rows("18:31").Copy Sheets("Sheet2").Rows("83") Dim x As Integer For x = 95 To 84 Step -1 If Sheets("Sheet2").Range("A" &...

Hi Abdel, I notice in your file that sheet 2 has the range 84:96 and sheet 1 has the mentioned 19:31. In your TransferData code you clean up sheet 1 before copying the data to sheet 2 (at the bottom of your range?). To show you the provided code lines work as intended, I have added the code RunMe. Before running the code, select th...

Averaging data on inserted rows in Excel

Hello, Please after successfully inserting specific number of rows in excel, is there any code that can help you average the data on the inserted rows?

Hi Kurug, No worries. Some more questions though: 4. Since you use a code to insert rows, I either have to know that the insertion part of the code is at the bottom (meaning, the inserted part is still selected) or I have to know the code (assuming you don't know how to alter your code). 5. There are 16384 columns and I'm pret...

Hi Kurug, With the given info it is impossible to help you. Could you provide more details? 1. Do you manually insert those rows? 2. Which column(s) of the inserted rows would you like to get the average of? 3. When we have the average, what do you want to do with it? Place it in a cell or show it as a message or ...? These are...

new keys please send

Hello, windows 10 pro

Sure, I'll buy a bunch of new keys for you, oh wait, no I'm not.

How can we show offline when we are online on while chaiting

How can we show offline when we online on whatsapp

Hi Sapna, I asked google for you: How do I appear offline on WhatsApp? Launch WhatsApp, and head to your Settings tab, located in the bottom right hand corner. Next, go to Chat Settings/Privacy > Advanced. Toggle the Last Seen Timestamp option to OFF, and then, select Nobody to disable the application timestamps. This method will all...

How to simply this Excel formula [Solved]

Is there a way to make this formula simpler? =IF($K9=0,"",SUM($L9*$L11)/R9+IF($K9=0,"",SUM($M9*$M11)/$R9+IF($K9=0,"",SUM($N9*$N11)/$R9+IF($K9=0,"",SUM($O9*$O11)/$R9+IF($K9=0,"",SUM($P9*$P11)/$R9+IF($K9=0,"",SUM($Q9*$Q11)/$R9))))))

Hi Rufus, Sure, how about this: =IF($K90,($L9*$L11+$M9*$M11+$N9*$N11+$O9*$O11+$P9*$P11+$Q9*$Q11)/$R9,"") Or even shorter: =IF($K9=0,"",SUM($L9:$Q9*$L11:$Q11)/$R9) NOTE: This is an array formula and needs to be confirmed using Ctrl+Shift+Enter. When done correctly the formula will be enclosed by curly brackets { }. Best regards...

FindAll This custom function returns Find and FindNext results as an array of va

Find All This custom function returns Find and FindNext results as an array of values. It will find all the instances of a string (sText As String) and returns an array containing the line numbers. Question: How could I modify the subject Macro to Find All instances of an email address in my table of contacts then delete all rows with... Read more

Hi Rodwell, You forgot to post the custom function. Or would you prefer a code that asks you for a string of text and then deletes all the rows containing that string of text? Are the email addresses in 1 column or all over your sheet? Best regards, Trowa