Free Microsoft Excel 2013 Quick Reference

Adding data to the next row Results

If anyone can help me with any of these three questions I would greatly appreciate it!!

I would like to make an excel template that has an entry for the current days date that has a non-print (doesn't show in printed page) that you can click to automatically put the current date in that field.

Second, is there a way to make it so when I double click a cell it will automatically put the current time in that cell?

Third and last, often times in this worksheet the same entry needs to be duplicated for an entire area. Is there a way to put the data into say cell 1 and then click a box to have it duplicate that cell or cells for the next 5 rows?

I don't know if these things can be done in excel but I would greatly appreciate any tips.

Thanks!

Hi,

I am having some difficulties in making this template for my boss so can someone please give me a hand on this?

This is the situation:

I need to create a drop list and when I select the drop list, it will give me a few selections for example: Sports, News, Music. (This I can do). On the right side of the cell, i will have week 1, week 2 all the way to 25.

Once I select Sports, the data from the Sports section will appear in the next few cells such as basketball, baseball, tennis and so on along with the data in week 1, 2, ... etc There will be an empty cell right below the row of the drop list cell that will allow me to input a new type of sport as well as add data to the week 1, 2, columns.

Once it has been added, this new sport will be saved under the Sports section.

I hope this is clear enough!

I'm not really sure what I need to do this but I would appreciate any help or for someone to point me in the right direction.

I'm trying to get excel to add up some numbers from multiple worksheets, as they are created, to another master worksheet. The first worksheet will become the master worksheet and will take the numbers from subsequent worksheets that become available each week. The catch is that I need the master sheet to take the numbers from the rows with corresponding names and add them and then add any new names to the list.

Here is an example:

Worksheet A(master worksheet that will accumulate the numbers from other sheets as they become available) will have the same columns of data as the other sheets; columns B,C,D,E,F. Columns B,C have name data. Columns D,E,F have the numbers that I need added.

Worksheet B will be the second worksheet that becomes available the following week. It will look very similar. It will have most of the same names and their data for that week but there could be more new names or less names but not necessarily the same names.

What I need excel to do is recognize the same names that occur in worksheet A and B and then add worksheet B's numbers to the corresponding row in worksheet A. I then need to add the new names that come up that week to worksheet A's unused rows at the bottom.

If cell B4(on worksheet A) matches with a name in B14(on worksheet B) I need worksheet B's numbers from D14,E14, and F14 to add into worksheet A's D4,E4, and F4.

*I can copy and paste worksheet B's data next to the data in worksheet A to complete the task if necessary.

I apologize for such a lengthy post. Let me know of any questions to things unclear. Thank you for any help!

Hello. I am currently making a checklist in an excel spreadsheet. I have a
dropdown box next to the questions to select an answer. The answers given
may require an action (For example, if i answered "no" in my checklist, i
would need to follow it up), I would like a comments box at the bottom to
pick up which question numbers require actioning based on the answers given.

So effectively my comments and actions would start in row 30, and i have 10
questions above it. Questions 1, 2 and 9 have been answered with a "No", so
i would like the comments row(s) to pick up the question numbers so we can
type corresponding actions against.

Is this possible?

Any help would be greatly appreciated.

Hi,

I need to pull information from 2 tabs (one has header info; Wor Summary and the other raw data; WoR Questionnaire) and are within the same worksheet and have it automatically copy it into another spreadsheet. I wrote the code below the line and the code when individually for each tab works fine ... but when combined I only pull information for the 1st tab (Wor Summary) and it doesn't pull information from the 2nd tab (Wor Questionnaire). I think it's failing at the part where it has;

End With
r = r + 1
End If
Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False

Cuz it thinks it needs to stop therefore it fails to go to the next code to run to pull data from the next tab. Can anyone help me please?

----------------------------------------------------------
Sub HSSESafetyQuestions()
Dim fso, f, fldnm As String, WB As Workbook, WS As Worksheet, r, x As Long
Dim ws2 As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")

fldnm = "C:Documents and Settingsmoyea0My DocumentsAndreea10k2005Data" 'Folder to loop through
 
Set WS = Workbooks("HSSE_WoR_10k_master.xls").Sheets("HSSE Questions")

r = WS.Cells.Find(What:="*", LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1

x = WS.Cells.Find(What:="*", LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row + 1
Application.ScreenUpdating = False

'Mike Test
For Each f In fso.GetFolder(fldnm).Files
If UCase(Right(f.Name, 3)) = "XLS" Then
Set WB = Workbooks.Open(f.Path)
Set ws2 = WB.Sheets("WOR Summary")
With WS.Rows(r)
.Columns("j") = ws2.Range("c3").Value
.Columns("k") = ws2.Range("c2").Value
.Columns("l") = ws2.Range("c5").Value
.Columns("m") = ws2.Range("c8").Value
.Columns("n") = ws2.Range("c9").Value
.Columns("o") = ws2.Range("c7").Value
.Columns("p") = ws2.Range("f3").Value
.Columns("q") = ws2.Range("f4").Value
.Columns("r") = ws2.Range("f5").Value
.Columns("s") = ws2.Range("f6").Value
.Columns("t") = ws2.Range("f7").Value
.Columns("u") = ws2.Range("f8").Value
.Columns("v") = ws2.Range("f9").Value
.Columns("w") = ws2.Range("f10").Value

End With
r = r + 1
End If
Next
Application.ScreenUpdating = False
Application.DisplayAlerts = False


For Each f In fso.GetFolder(fldnm).Files
If UCase(Right(f.Name, 3)) = "XLS" Then
Set WB = Workbooks.Open(f.Path)
Set ws2 = WB.Sheets("WOR Questionnaire")
With WS.Rows(x)
.Columns("x") = ws2.Range("D12").Value
.Columns("y") = ws2.Range("D21").Value
.Columns("z") = ws2.Range("D29").Value
.Columns("aa") = ws2.Range("D55").Value
.Columns("ab") = ws2.Range("D62").Value
.Columns("ac") = ws2.Range("D64").Value
.Columns("ad") = ws2.Range("D70").Value
.Columns("ae") = ws2.Range("D93").Value
.Columns("af") = ws2.Range("D95").Value
.Columns("ag") = ws2.Range("D98").Value
.Columns("ah") = ws2.Range("D99").Value
.Columns("ai") = ws2.Range("D100").Value
.Columns("aj") = ws2.Range("D101").Value
.Columns("ak") = ws2.Range("D103").Value
.Columns("al") = ws2.Range("D104").Value
.Columns("am") = ws2.Range("D105").Value
.Columns("an") = ws2.Range("D106").Value
.Columns("ao") = ws2.Range("D107").Value
.Columns("ap") = ws2.Range("D109").Value
.Columns("aq") = ws2.Range("D108").Value
.Columns("ar") = ws2.Range("D110").Value
.Columns("as") = ws2.Range("D111").Value
.Columns("at") = ws2.Range("D112").Value
.Columns("au") = ws2.Range("D114").Value
.Columns("av") = ws2.Range("D118").Value
.Columns("aw") = ws2.Range("D130").Value
.Columns("ax") = ws2.Range("D119").Value
.Columns("ay") = ws2.Range("D129").Value
.Columns("ba") = ws2.Range("D121").Value
.Columns("bb") = ws2.Range("D122").Value
.Columns("bc") = ws2.Range("D123").Value
.Columns("be") = ws2.Range("D125").Value
.Columns("bf") = ws2.Range("D126").Value
.Columns("bg") = ws2.Range("D127").Value
.Columns("bh") = ws2.Range("D128").Value
.Columns("bi") = ws2.Range("D134").Value
.Columns("bj") = ws2.Range("D147").Value
End With
x = x + 1
WB.SaveAs fldnm & "Archive_" & Right(f, Len(f) - 41)
WB.Close
f.Delete
End If
Next
Application.ScreenUpdating = True
End Sub


Hi guys and girls,

Could anyone help me please.

I am trying to insert a new row using a variable called "countrytotal". The idea is to add all the rows where there is data (which I have done and is stored in "countrytotal") and then use the value of "countrytotal" + 1 to insert the next row for new data to be added.

To do it using numbers it's:

Rows("6:6").Select
Selection.Insert Shift:=xlDown

but I want to replace the "6:6" with the variable "countrytotal" if it is possible.

Any help will be very much appreciated.

Thanks,

T8RSP

I have a small spreadsheet made to keep track of a weekly payment from my son for his board. There are 5 columns for data entry. "Date", "Note", "Paid", "Added on" and "Board Due". I am using calendar add in to load my date and a drop down list for note. "Board due" is auto summed from the "Paid" and "Added on" columns. What I am looking for is a way to auto load data in 3 of the columns on the Friday of every week. The 3 columns that I want the Data loaded into are "Date" ,"Note" and "Added on". I want it to load the data in the next available row. I have a cell "A1" with the "Now" function and another cell With the "Weekday" function on the sheet. Not sure if I'm explaining myself properly or not, but it may prompt questions. Thanks in advance. RMontani

Hi there guys

I've solved many a problem on here recently, without having to make a post, so first of all thanks for that! However, I am not at all sure how to even approach the problem I have today, so I don't know what to search for - hopefully if I can describe the question, it'll make sense to somebody...

First off, I have used Excel for probably around 15 years, use it everyday at work, and have completed courses to "Advanced" level - however, I have never dabbled in VBA or macros, and only know the very basics of things such as PivotTables. So please go easy on me!!

Ok, so I have a great big workbook with many sheets, and rather a complicated workflow to analyse the data contained within it. The workflow is:
- Our sales system (Sage Line 50) generates a simple report in .csv format, which contains 4 columns - A=CustomerAccountCode, B=CustomerName, C=TurnoverPeriod (e.g, January, as it is done monthly) D=TurnoverYTD. I copy this to a sheet in my worksheet named after the month name.

- I run a simple VLOOKUP function in the 5th column, against a CUSTOMER worksheet in the file, which is a list of all our accounts as of the previous month (showing AccountCode, Name and their local Rep code). What this does is return an N/A if the account does not feature in the list - this is a useful step for me, as it highlights if any new accounts have been setup in the past month, that I might not know about, so I can call the relevent rep and find out more.

- It is also a necessary function at this point, as it then gives me the necessary information to add the Customer (AcccountCode & Name) to the list in the CUSTOMER worksheet, so when I run the same check next month it does not return the N/A again. I know this is a longwinded thing to do, but it really is useful to highlight the new accounts. However, it is the next steps that I am hoping to simplify!

- Once I have done the previous step, I go to the MASTER worksheet, where all the data is compiled, insert a row, and type in the data for the new customer (basically replicating the previous step), and drag down the formulae from the row above to calculate the various things I have setup in the columns adjacent. This is the awkward step and leads to my 1st question:

Q: Is there any way I can automate this step, so that once I have manually added the info to the CUSTOMER sheet, there is a macro I can run which will copy that data, go to the MASTER sheet, insert a row, and put the data in there, as well as (hopefully!) then filling in the neccessary formulae to (many) adjacent columns??? I don't even know if macros are the best way to attack this, but hoping so.

In addition, once I have added the info to the MASTER sheet I have the following workflow:
- In the relevent monthly column in the MASTER sheet, I do a VLOOKUP to look at the data which came out of Sage, to return the relevent month's sales data. Can this be automated somehow??

Finally, I have a further group of worksheets, one for each REP. These basically show all the information in the MASTER sheet, but only for the rep in question, so we can send him that sheet and he doesn't get anybody elses sales figures. I do this by, again, doing a VLOOKUP in the relevent monthly column, either to the original .csv data worksheet or to the MASTER sheet, to return the monthly sales. However, if I don't (again!) repeat the manually adding any new customers to the REP sheets before doing this, it obviously doesn't look for the sales for the new customer, and so the totals can be wrong at the end of the columns, as the sales data for the new customer is missing. Again, can all this be automated? And if so, how??!!

I'm sure this seems like a crazy obvious question to you guys out there - although the description of the problem is somewhat longwinded, I have a feeling that the solution will be a combination of actions which are incredibly simple to the experts...

In advance, many thanks for any help you can give me, and I think I'll go and get myself enrolled in a real "Advanced" course, and take a proper look at macros / VBA...

Caz

I'm not really sure what tool I need to use but I would appreciate some help or for someone to point me in the right direction.

I'm trying to get excel to add up some numbers from multiple worksheets, as they are created, to another master worksheet. The first worksheet will become the master worksheet and will take the numbers from subsequent worksheets that become available each week. The catch is that I need the master sheet to take the numbers from the rows with corresponding names and add them and then add any new names to the list.

Here is an example:

Worksheet A(master worksheet that will accumulate the numbers from other sheets as they become available) will have the same columns of data as the other sheets; columns B,C,D,E,F. Columns B,C have name data. Columns D,E,F have the numbers that I need added.

Worksheet B will be the second worksheet that becomes available the following week. It will look very similar. It will have most of the same names and their data for that week but there could be more new names or less names but not necessarily the same names.

What I need excel to do is recognize the same names that occur in worksheet A and B and then add worksheet B's numbers to the corresponding row in worksheet A. I then need to add the new names that come up that week to worksheet A's unused rows at the bottom.

If cell B4(on worksheet A) matches with a name in B14(on worksheet B) I need worksheet B's numbers from D14,E14, and F14 to add into worksheet A's D4,E4, and F4.

*I can copy and paste worksheet B's data next to the data in worksheet A to complete the task if necessary.

I apologize for such a lengthy post. Let me know of any questions to things unclear. Thank you for any help!

Hi, I need a little help with CONCATENATE

I already have a form that finds the last cell with data in sheet 1 then adds the new data to the row beneath (pretty standard thing to do and I’ve got that working).

What I need help with is, once that data is entered I want my concatenate formula in sheet 2 to write a little blurb about the latest entry (so staff can highlight it and paste it into another program).

So in sheet 1 I have data like
A B C
1 Type Size Colour
2 Truck Small Blue
3 Car Big Red

In sheet 2 I have =CONCATENATE(Sheet1!A3,Sheet3!A1……….)

In sheet 3 I have strings like ‘This is a’ to concatenate with the data from sheet 1.

So the end product should be a small narrative in sheet 2 that reads something like ‘This is a Big car that is the colour red’

What I can’t do is make reference to sheet 1 in the CONCATENATE formula. In the example above I would need to reference the last row with text, rather than type A3.

So when the next entry is added to sheet one, let’s say boat – small – white
The narrative in sheet 2 will change to read ‘This is a small boat that is the colour white’

Can anyone help? What I need is the formula for CONCATENATE that reads (In Sheet 1 find the last cell with data in column A, Sheet3!A1......) Instead of (Sheet1!A3, Sheet3!A1…)

Thanking you in advance

What types of functions can i use to write formulas for the following:

1. Enter data into one cell or range of cells within in a row.
2. Have this row of data replicated to a different cell range within the same worksheet or workbook. i.e. replicating and adding this row of data to another list of data.
3. Create a condition that prompts excel to replicate the data into an unnocupied cell range/row underneath provided that the target range of cells is not already occupied by data.
In other words i want to enter data into list A. then ask excel to look down list B and replicate that data into the next available line in list B.
eg. enter data into cell a1, replicate this data into cell b1, if cell b1 is already occupied by data then replicate this data into b2.
Can any of you guys help me to write a formula that would do this? I would appreciate it if any of you can!

Hello: I have attached a sample file that I am working on. In the Bill tab, I am trying to get the formula in V to AC to be inserted in the code so that everytime a new entry is added to the sheet depending on the name, the same formula will be inserted if the col A to U has data. The reason is because there are fee structures in each tab so the numbers that the formulas will be referencing will be different but the formulas will always be the same which is why I am trying to figure out if there is a way to imbed the formulas in the code.Thanks a lot for your help in advance.

      Sub
Test()
    
    Dim sh As Worksheet
     Dim sh5 As Worksheet
    Dim rData As Range, rCell As Range, rFrom As Range, rTo As Range, rTo2 As Range, rCell2 As Range
    Dim lRightCol As Long, lRightCol2 As Long
Sheets("Data").Select
    With ActiveSheet
        lRightCol = .Range("AC6").Column
        Set rData = .Range(.Range("A3:A14"), .Range("A" & .Rows.Count).End(xlUp))
        For Each rCell In rData
            Set sh = Nothing
            On Error Resume Next
            Set sh = Worksheets(rCell.Value)
            On Error GoTo 0
            If Not sh Is Nothing Then
                Set rFrom = rCell.Resize(1, lRightCol)
                With sh
                    Set rTo = .Range("A" & .Rows.Count).End(xlUp).Resize(1, lRightCol)
                    rTo.Insert , shift:=xlDown
                    rTo.Copy rTo.Offset(-1, 0)
                End With
                rFrom.Copy rTo
            End If
            Next rCell
            
        End With
        End Sub


Hi,

I really hope someone can help. I'm not just a newbie to these boards (although have browsed alot!) but also a VBA newbie. I have a spreadsheet with multiple sheets however, I'm concerned with only 2 sheets.

Sheet 1 is a data sheet which has a list of projects down one side, the Gate review number across the top (divided into planned and actual) and then dates in the main body eg project xyz, sub project 123 had its Gate 3 planned for Oct-10 and its Gate 3 actually happen Nov-10

In sheet 2 I need to see which projects have planned and held a gate review in any given month (dates run from Jan-10 to Dec-12 with 2 columns for each, 1 headed planned and 1 headed actual). The projects are listed down the left, row 1 has months in it (each duplicated twice), row 2 has planned and actual in (one for each month - hence the duplicated months in row 1).

The code I've written uses a set of variables and If, Then statements to look for project, subproject, planned, actual and date matches and return the value 1 for each match found (so if 3 gates are planned in a month then the value will be 3). The logic I have come up with works and returns the correct values. However, as there are so many columns and rows it takes forever (30mins+) to run.

This is the code (I've added comments to try and explain it in the code):

Sub Dates()

Dim datr As Integer 'Row number on the Gate Sheet (has the planned & actual dates for all 12 gates of the project on that
row)
Dim metr As Integer 'Row number on the Metrics sheet (shows the number of gates in a given month (Jan-10 to Dec-12) for that
project
Dim datc As Integer 'Column on the Gate sheet that has the planned & actual dates by Gate number
Dim mtc As Integer 'Column on the Metrics sheet that has the planned and actual by month count
Dim datd As Range 'Cell containing the date of a planned or actual Gate
Dim mtd As Range 'Cell containing the calendar dates in Row 1 of sheet
Dim mtdt As Date 'mtd value (which is a date)
Dim datdt As Date 'datd value (also a date)

Application.ScreenUpdating = False

'Makes sure that the project name data is the same in the Metrics (Calc) sheet as the Gate (data) sheet
'and clears any previous data out of the Metrics sheet
Sheets("Calc").Select
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "='CORE DATA'!R[2]C"
    Range("A3").Select
    Selection.AutoFill Destination:=Range("A3:C3"), Type:=xlFillDefault
    Range("A3:C3").Select
    Selection.AutoFill Destination:=Range("A3:C120"), Type:=xlFillDefault
    Range("A3:C120").Select
    ActiveWindow.ScrollRow = 119
    ActiveWindow.ScrollRow = 102
    ActiveWindow.ScrollRow = 87
    ActiveWindow.ScrollRow = 71
    ActiveWindow.ScrollRow = 56
    ActiveWindow.ScrollRow = 51
    ActiveWindow.ScrollRow = 46
    ActiveWindow.ScrollRow = 43
    ActiveWindow.ScrollRow = 40
    ActiveWindow.ScrollRow = 36
    ActiveWindow.ScrollRow = 31
    ActiveWindow.ScrollRow = 27
    ActiveWindow.ScrollRow = 19
    ActiveWindow.ScrollRow = 10
    ActiveWindow.ScrollRow = 4
    ActiveWindow.ScrollRow = 2
    ActiveWindow.ScrollRow = 1
    Cells.Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=2, Criteria1:="0"
    Rows("63:121").Select
    Selection.Delete Shift:=xlUp
    Cells.Select
    Selection.AutoFilter
    Range("D3").Select
    Range("D3:EV120").Select
    Selection.ClearContents
    Range("D3").Select

For datr = 5 To 120 'rows 5 to 120 contain the list of projects in the Gate data sheet
For metr = 3 To 118 'rows 3 to 118 contain the same list of projects as above
For datc = 17 To 51 'columns in the Gate sheet (3 for each of the 12 gates, 1st in planned, then actual then variance)
For mtc = 4 To 75 'columns containing months from Jan10 to Dec 12 (2 columns each month, 1 planned and 1 actual)


Set datd = Worksheets("CORE DATA").Cells(datr, datc)
Set mtd = Worksheets("Calc").Cells(1, mtc)

datdt = datd.Value
mtdt = mtd.Value

'If Project name and subproject name in the Gate sheet match those in the Metrics sheet then
If Worksheets("CORE DATA").Cells(datr, 2).Value = Worksheets("Calc").Cells(metr, 2).Value And
Worksheets("CORE DATA").Cells(datr, 3).Value = Worksheets("Calc").Cells(metr, 3).Value Then
'If the month and year of a Gate match the month and year of the column in the Metrics sheet then make sure
'that the planned or actual also matches (eg if a gate was planned for Jan-10 and the check is being done against
'the column headed Jan-10 and labelled in Row 2 planned then return a 1 (or if there is already a 1 add 1 ie if 2 gates are
planned in the same monmth
'then that month will show 2 under the month and planned column.  If not move onto the next column and check that
'Repeat until all columns have been checked and then move onto the next column (ie Actuals) and do same checks.
'Repeat until all columns checked and then move onto the next row and repeat steps above
If Month(datdt) = Month(mtdt) And Year(datdt) = Year(mtdt) And Worksheets("CORE DATA").Cells(4, datc) =
Worksheets("Calc").Cells(2, mtc) Then
Worksheets("Calc").Cells(metr, mtc).Select
ActiveCell.Value = ActiveCell.Value + 1

End If
End If
Next mtc
Next datc
Next metr
Next datr


End Sub
I'm sure there must be a less clunky way of doing this that doesn't melt my laptop but don't have the experience to know where to start.

(Sorry I can't upload the spreadsheet itself as it has Co. eyes only stuff in it.)

Any help gratefully received! And apologies if this is in the incorrect place or format

So I have a data dump of employee data that shows headcount (yellow section) and when the headcount is added or reduced by month (green section). I need to create a report on a monthly basis that shows the adds and reductions by month. As of right now I am manually going into the green section of the attached sheet by month and manually filtering by either a -1 (reduction) or 1 (addition) and pasting the employee name, department, etc. to another sheet to create this adds/reductions report. Any suggestions on how to automate this? I was trying to go the macro route, but got stuck at figuring out how to move to the next visible row when a filter was applied. Anyone think there would be an easier way to do this without using macros? Any suggestions would be much appreciated. This simple task is taking me waaaayyyy to long to do every month. TIA.

Hi,
Thankyou for taking the time to look at my post. I am yet another novice user trying to do more than they are capable of.
I have attached a jpeg of the userform I have created to aid in my description.
motorcheck.jpg
This is a database, for adding/viewing annual measurements for a particular conveyor. Each conveyor can have up to 100rows of data in the sheet.
If a user example clicks on the button C1.01 the worksheet selects row 100. Or if they select button C1.02, the worksheet selects row 200.
It will then auto-populate the Original/Base Value locked textboxes with the data from the first row (ie row 200).
The user is then able to add new data, which will add to the next available row (If base row is 200, and row 205 is empty it will add the data there, or row 206 etc).
The user will also be able to view previous data by pressing the next or previous button, which will move the focus up and down the rows and the unlocked freefield textboxes+checkboxes update with the values from the relevant row and cell. The user can modify existing data and press save.

So - depending on the row, the userform populates and allows data entry relative to the set of rows the user has selected.
I have looked at a bunch of examples, and many of them show how to do the normal functions, but i have seen none which are dynamic like this in the sense that it is relevant to a set of rows in the same sheet. I wonder if it is even possible?

I have also added my workbook, with functional C1 buttons, and example data for C1.01, C1.02, C1.03 (these are the original/base values - there are no new values to input yet).
MotorCheck.xlsm

Any ideas will be much appreciated. I hope this thread will help others too. I hope my description is sufficient.
Thanks again.

Hi everybody,

I've created a userform with a cmd button to insert data in a sheet named "DataBase".
Every time new data is entered, a new row is added in the sheet (this is working great). the problem is that one of the columns as a formula and that formula doesn't "jump" to the next row.

Am i being clear? Can anyone help me with the code?

Thanks

Hi All,

I have an issue that I cannot find a solution to anywhere and wondered if there's anyone out there that could possibly help me out.

Basically my data looks like this:
Col A
*******
xz7vc86
xvkzxjhv87
sdsk987
*************
kasjdhfkjas
hfsjkadfhk
jaksdfjh

sdjkfasjf

9238ofhf

********

How is it possible to get the data between the sets of stars placed into one cell (and the next lot down into another cell a row down)????

Thanks a lot,

Scott.

Hey everyone,

Apologies in advance for the long post, but I think I need to explain things carefully so people don't get confused!

A while ago I ventured onto these forums to seek some help regarding getting Excel to grab data from a datalogger and place it into columns on a worksheet. Since then I've been working on electronics, actually building the circuit and associated hardware, but now that the circuit works I've now moved back onto some programming to improve the Excel sheet I have currently to deal with the data.

The original post on these forums can be found here:
http://www.excelforum.com/excel-programming/639002-dde-macros-and-data-formatting.html

Now as the worksheet used to function it would collect the data every x seconds (set via VB, usually every 3 seconds, dependent on the button pressed), update the top left of the worksheet with the current results every x seconds, and at the same time, copy and paste the results into seperate columns (seperating each channel into a different column to make it easier to work with the data). This would then repeat every x seconds, placing each new set of data for each channel into the row below the last one, giving me a permanent record of all results taken so far. It then also placed a simple counter in one column (saying 1,2,3,4 etc down each row) showing which number in the sequence the data collected was. I then had a set of cells with formulae in them to work out a set of other variable from the original data obtained from the logger.
Having been playing around with the macro a bit recently, trying to make it do the things I want, I seem to have slightly broken it

I have attached an example worksheet to show you what it currently looks like, and with it is a small amount of sample data to show how it's working. Most of the macros are located in "Module 1", but some of the ones referred to at the end are in Module 2.

If I haven't confused you too much already, let me try to explain what I've been trying to make it do

-------- Simplify the 'interface' so that the whole thing can be triggered by one button press (as other people will be using this, and I want it to be as simple as it can be), and also monitored in one screen display (everything important can be seen without scrolling). To do this I made a new button (the "Start Readings" button), and attempted to create a macro do to the following things when pressed:

1. Clear the contents of the current data readings in the top left (range A4:C11)
2. Clear the contents of all cells in the range E17:W50000 [gets rid of all old data to make sure there is no mixing of sets of results]
3. Grab the latest set of data from the data logger, and place it in the top left area.
4. Look down the newly collected data in the top left, and use the Channel Names as cell contents as column titles from E17 going right until all channel names have been used (so the number of columns used could vary depending on the number of channels collected from the logger)
5. Then set the cells in row 17 to the right of the cells named in step 4 with a set of fixed titles (such as ALARM @150)
6. Then once all this has been set, call the macro which then collects the data from the logger every three seconds, and places it in the newly named columns, moving down a row with every new set of results.

The point of this set of steps is that when someone presses the Start button, it acts as a reset-all if you will, it clears out all old data, looks at the logger and re-tunes itself to be ready for importing the new data, then starts collecting it.

The problems I've encountered with these steps so far are:
1. works fine
2. I want the macro to look right down to the bottom row in Excel. This varies with different versions, and I have heard there is some kind of bottom row, or last row function, but having searched the web for a bit failed to find it. This would make sure it always clears out all the old data, by making it go right down to the bottom of the rows available. It's set at 50000 currently just because I can't find this function
3. works fine
4. Works, but I don't have the programming knowledge to make it more 'intelligent'; that is to make it only use the number of columns that there are channels, so that there aren't unessecary empty columns. It currently just creates a few empty columns that needn't be there. If lots more new channels are added, it also might not deal with them all as it may miss some off the ends.
5. Works, but same as for step 4: if the macro knew how many columns it was using in 4, it could then move these named columns right next to the data columns without gaps. There will be more columns in this area to come as the worksheet progresses.
6. This is where I seem to have really confused the macro! It now does quite a few strange things: places a batch of data at the top of the worksheet, where it isn't needed. The first row to use needs to be row 18, just below the titles for each column; it goes down a row every 3 seconds, but now ALL the rows update every three seconds with the new data - the old data is no longer preserved, so it now just progressively reads more and more rows all giving the same, changing results.

--------Adapt and extend the macro to add the calculated cells (the ones in columns R-W in the example attached, although there will be some more eventually) to be calculated and worked out as the macro goes along, rather than having all the values pre-placed in the cells manually, as this gives a lot less flexibility, and also means that you can't perform the "reset all" function I talked about above, as you lose the forumulae. There is also some conditional formatting associated with the two "Alarm" columns, which can be seen if you look at the cell's formatting. Ideally, if any of the cells in the two alarm columns flag up an alarm (and go red, displaying "Alarm"), then the large merged cell in D9:E15 would then display some formatting change to show an alarm has been detected, and perhaps pop up a message box too.

--------Ideally the graph at the top would also auto update with the new data as it comes in, automatically adjusting it's values when the sheet is reset etc. Currently I just placed the source data a long way down the sheet and it just displays the rows with values in, but this isn't a perfect solution.

--------Change the Time Elapsed column, which currently counts the number of the data record collected in each row, so that instead of simply counting the number (1,2,3 etc) it displays the current system time when each sample was taken. As it turns out (despite my old post) having the 'actual' time rather than just relative is actually more useful!

--------And if you want one final hurdle (which I've started with help from someone at work), if you take a gander in VB from Module 1 to Module 2, there will you find some code which is aimed at doing the following:
- as the data is recorded into the Excel file, it then also copies the results and places them in a comma-seperated text file, writing them in real time, to act as backup of the data in case Excel crashes or someone does something silly and deletes the data. The macro in this bit currently just counts up to a set time and displays a simple number count, also showing a system time stamp, and places it in a text file located in the same directory as the Excel file is located, with results comma seperated. It names the file currently "Testdd", with the dd being the current day of the month from the system time. Ideally it would name them the full date (yymmdd hhmm) because once we start collecting hundreds, an easily sortable file naming fomat will be invaluable!

Hmm, well this is indeed a long post with a lot of requests, and I've no doubt quite confusing, so apologies for you lot trying to understand it, if you need any more clarification then please ask and I'll try to explain it better!

As you can see I've got most of the areas started, and working to a certain extent, but my VB powers fail before getting it to be as 'slick' as I would really like it.

Any inputs on solving any of the issues mentioned is greatly appreciated!

**Edit: File was too large to attach, even zipped and reduced as much as I could. Have uploaded it to a sharing site, can hopefully be found here:
http://www.mediafire.com/?lzz0ujfz2yx

**Edit again: in the uploaded version, the graph is missing as I deleted it in an attempt to reduce the file size. The graph was set to display the two moving average columns of coil resistance change against time elapsed, as a nice visual representation of what's going on.

is the following possible with vba?

i'm performing a cashflow calculation for close to 300 products. the elements used in the calculation vary between products so i would like to have a table detailing the nature of all items required for each product (table 1). once this is calculated, i need to apply a tax rate which again is different depending on the product (table 2). is it possible to extract data by cross-referencing tables 1 and table 2 in a calculation?

also, i need to save year-to-date data. can a worksheet be used to store this data, such that the next months results are added to the next available row in the worksheet and totalled?

thanks in advance

How do I reference data from one worksheet to another worksheet cell with a
combo box and then have the data relating to this appear in the nesxt cell in
a combo box.

ie.- Workssheet 2, cell D6 need to reference worksheet 1 cells C2 & C3
(Documents) then have cells A2 to A12 ( Document Issues) in worksheet 1
appear in cell E6 in Worksheet 2.
Also I will have multiple cell references from worksheet 1 to worksheet 2
following this simular format into cell D & E6. Then it needs to put this
information into individual rows on worksheet 2.
ie- choose data for D6 then E6 and goto the next row for adding additional

Thanks in advance for your help.


No luck finding an answer? You could always try Google.