Excel (Microsoft) (4 Viewers)

[napolimp]

Anyone any good on Excel? Forums just my go to place for information now, for better or worse. You know someone's going to be an expert at what you want information on.

 

---

[Greggs]

What you looking for mate?

 

[duffer]

Let me just start my meter...

Now, what are you trying to do?

 

[duffer]

If it's calculating driving fines, the =sum() function is the way to go.

 

[napolimp]

What I'm looking to do, is automatically transfer rows of data between Excel workbooks. I'd like to be able to have a row of data in one workbook, with a drop down list at the end which has a number of options - for example "proceed to next stage" or "don't proceed". If you were to select "proceed to next stage" from the drop down list, I'd like the data to automatically populate on the next available row in a second workbook. Ideally it would only be certain cells of data from the row in the first workbook populated into the second, but to be honest that's not a total necessity. If possible, I'd like to reverse the auto population process as well if necessary. So if you change the drop down option at the end of the row to "don't proceed", it would then remove the data previously copied over to the second document.

Any ideas? Cheers.

 

[duffer]

Joking aside, if you're stuck shout up. I'm not a genius, and there's always someone better on here, but I have to use Excel a fair bit...

 

[duffer]

What I'm looking to do, is automatically transfer rows of data between Excel workbooks. I'd like to be able to have a row of data in one workbook, with a drop down list at the end which has a number of options - for example "proceed to next stage" or "don't proceed". If you were to select "proceed to next stage" from the drop down list, I'd like the data to automatically populate on the next available row in a second workbook. Ideally it would only be certain cells of data from the row in the first workbook populated into the second, but to be honest that's not a total necessity. If possible, I'd like to reverse the auto population process as well if necessary. So if you change the drop down option at the end of the row to "don't proceed", it would then remove the data previously copied over to the second document.

Any ideas? Cheers.

Nice of you to start off with something easy...

The first thing I always think of is, could I do this manually?

If I can do it manually, then I can record it into a macro.

If I can record it into a macro, then I can call it from somewhere (and amend the macro code).

The hardest bit here might be 'the next available row' thing.

In absolute honesty, I'll need to have a play with it for a bit and do some googling.

Interesting challenge this, happy to have a dabble, but in all honesty beyond me how to do it off the top of my head.

Wouldn't be at all surprised if there's someone here more current with the tool who can offer a better solution...

 

[napolimp]

Nice of you to start off with something easy...

The first thing I always think of is, could I do this manually?

If I can do it manually, then I can record it into a macro.

If I can record it into a macro, then I can call it from somewhere (and amend the macro code).

The hardest bit here might be 'the next available row' thing.

In absolute honesty, I'll need to have a play with it for a bit and do some googling.

Interesting challenge this, happy to have a dabble, but in all honesty beyond me how to do it off the top of my head.

Wouldn't be at all surprised if there's someone here more current with the tool who can offer a better solution...

This is great, thanks for taking an interest. Going to have a read up on this "macro" thing myself in the meantime.

To be honest, I could probably go without the "next available row" command. As long as the data is inputted into the other document in a coherent and readable fashion, then that's enough.

If you come up with anything I can read up on let me know. I'm not much of an Excel user past your basic stuff, but have been getting better as had enough of manually inputting loads of data, or just not being bothered and losing data altogether because I forget about it.

 

[Bertola]

It sounds like it could be done with an If statement.

IF Cell = "proceed" then...

 

[napolimp]

It sounds like it could be done with an If statement.

IF Cell = "proceed" then...

Interesting, would this only work in the same workbook though?

 

[Bertola]

Interesting, would this only work in the same workbook though?

I would imagine you can link the two in the same way you can use a lookup between two separate workbooks

 

[napolimp]

I would imagine you can link the two in the same way you can use a lookup between two separate workbooks

I'll have a little read up on this, thanks.

 

[Bertola]

I'll have a little read up on this, thanks.

If you're reading up, it may be more beneficial (and more efficient) to use a loop statement in SQL

 

[napolimp]

If you're reading up, it may be more beneficial (and more efficient) to use a loop statement in SQL

You've lost me but I'll read up on it. At first glance it appears that's not specifically an Excel thing?

 

[SkyBlueSoul]

Don't let anyone force you to do anything, I hear Excel Bullies are a big problem right now.

 

[shmmeee]

Got to say, if you’re doing stuff across multiple workbooks at this point you’ve probably outgrown Excel and should look at a proper data management system like a database or a SharePoint system.

Next best would be Google Sheets as it’s far more programmable and linkable.

If you insist on building apps in Excel like an absolute mad man there’s a few hacky ways you could do this.

Use Power Query to import the data (Data > Get and Transform Data) and only pull in the rows you need from the source file. You’d need to clear out the sheet and refresh the query each time to ensure rows that change back are removed though.

Write a VB script that triggers on a button or data change. Here’s ChatGPT to get you started:

Creating a VBA (Visual Basic for Applications) script to transfer data between workbooks based on a specific condition can be quite involved but highly customizable. Below is a simplified version of how you could set this up, making some assumptions about your workbook structure and requirements.

**Preparation**:

1. Ensure that your data in `Source.xlsx` is organized in a table format.
2. Assume that the table in `Source.xlsx` has a column named "Status" which will have the value "Proceed to next stage" or "Don't Proceed", based on your selection from a dropdown list.

**Steps to Create the VBA Script**:

1. **Open the Source Workbook (`Source.xlsx`)**:
- Open `Source.xlsx`.
- Press `Alt + F11` to open the VBA editor.

2. **Insert a New Module**:
- In the VBA editor, right-click on `VBAProject (Source.xlsx)` in the left-hand pane.
- Select `Insert` -> `Module`.

3. **Write the VBA Script**:
- In the module window, paste the following script:

```vba
Sub TransferData()
Dim sourceSheet As Worksheet
Dim destWb As Workbook
Dim destSheet As Worksheet
Dim lastRowSource As Long
Dim lastRowDest As Long
Dim i As Long

' Set references to source sheet, destination workbook and destination sheet
Set sourceSheet = ThisWorkbook.Sheets("Sheet1") ' Adjust "Sheet1" to your source sheet name
Set destWb = Workbooks.Open("C:\path\to\Destination.xlsx") ' Adjust path to your destination workbook
Set destSheet = destWb.Sheets("Sheet1") ' Adjust "Sheet1" to your destination sheet name

' Clear previous data in destination sheet
destSheet.Cells.Clear

' Find the last row of data in the source sheet
lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row

' Loop through each row in the source sheet
For i = 2 To lastRowSource ' Assuming headers are in row 1
If sourceSheet.Cells(i, "Status").Value = "Proceed to next stage" Then ' Adjust "Status" to your status column name
' Find the next available row in the destination sheet
lastRowDest = destSheet.Cells(destSheet.Rows.Count, "A").End(xlUp).Row + 1

' Copy the entire row from source to destination
sourceSheet.Rows(i).Copy Destination:=destSheet.Rows(lastRowDest)
End If
Next i

' Save and close the destination workbook
destWb.Save
destWb.Close

' Notify the user
MsgBox "Data transfer complete."
End Sub
```

4. **Adjust the Script**:
- Change `"Sheet1"` to the name of your source and destination sheets.
- Change `"C:\path\to\Destination.xlsx"` to the path of your `Destination.xlsx` file.
- Change `"Status"` to the name of your status column if it's different.

5. **Run the Script**:
- Press `F5` to run the script and transfer the data.

6. **Add an Event Trigger (Optional)**:
- If you want to automate this process further, you could set up an event trigger to run this script whenever a certain condition is met, like saving the workbook or changing a cell value.

7. **Create a Button (Optional)**:
- You could also create a button in your source workbook that runs this script when clicked, providing a user-friendly way to transfer the data.

This script checks each row in the source sheet, and if the status is "Proceed to next stage", it copies the entire row to the next available row in the destination sheet. Before doing this, it clears the previous data in the destination sheet to ensure that rows are removed if they no longer meet the criteria.

Remember to save and backup your workbooks before running any VBA script to prevent data loss in case of errors.

(Note remove the three backticks ‘’’vba before and the three backtick ‘’’ after the code)

Or you could use something like Zapier or MS Power Automate to trigger an automation on data change. But that’s probably the least reliable and requires your data to be accessible online.

 

[duffer]

+1 for @shmmeee advice here.

We'd perhaps differ on whether Excel is appropriate for this - it can be and I've seen it used this way in various places I've worked. It's ok if you're a "power user" with a reasonable amount of experience, but tough to do and get right if you're not.

I've spent a moderate amount of my working life converting this kind of Excel stuff into more formal database structures, and some of the spreadsheets I've worked on clearly weren't doing the thing that they were supposed to!

That said, in the absence of better tools, I think Excel will work for this. I've no experience of Google sheets, but as shmmeee says that might be an even better option...

 

[napolimp]

Got to say, if you’re doing stuff across multiple workbooks at this point you’ve probably outgrown Excel and should look at a proper data management system like a database or a SharePoint system.

Next best would be Google Sheets as it’s far more programmable and linkable.

If you insist on building apps in Excel like an absolute mad man there’s a few hacky ways you could do this.

Use Power Query to import the data (Data > Get and Transform Data) and only pull in the rows you need from the source file. You’d need to clear out the sheet and refresh the query each time to ensure rows that change back are removed though.

Write a VB script that triggers on a button or data change. Here’s ChatGPT to get you started:


(Note remove the three backticks ‘’’vba before and the three backtick ‘’’ after the code)

Or you could use something like Zapier or MS Power Automate to trigger an automation on data change. But that’s probably the least reliable and requires your data to be accessible online.

Thanks for all the suggestions. In normal circumstances I would be open to trying other software for what I want to achieve, but in this case it has to be compatible for work within Microsoft Teams.

 

[shmmeee]

+1 for @shmmeee advice here.

We'd perhaps differ on whether Excel is appropriate for this - it can be and I've seen it used this way in various places I've worked. It's ok if you're a "power user" with a reasonable amount of experience, but tough to do and get right if you're not.

I've spent a moderate amount of my working life converting this kind of Excel stuff into more formal database structures, and some of the spreadsheets I've worked on clearly weren't doing the thing that they were supposed to!

That said, in the absence of better tools, I think Excel will work for this. I've no experience of Google sheets, but as shmmeee says that might be an even better option...

I have an absolutely rational hatred of Excel

 

[shmmeee]

Thanks for all the suggestions. In normal circumstances I would be open to trying other software for what I want to achieve, but in this case it has to be compatible for work within Microsoft Teams.

I totally hear you. Just for your future data engineers sake, please document your files somewhere at least.

 

[napolimp]

I totally hear you. Just for your future data engineers sake, please document your files somewhere at least.

Do you mean like save elsewhere? Because I do a weekly download of the live file and replace the version I have saved on the servers. Just so there's always an offline copy available which is almost up to date.

 

[shmmeee]

Do you mean like save elsewhere? Because I do a weekly download of the live file and replace the version I have saved on the servers. Just so there's always an offline copy available which is almost up to date.

I mean whatever crazy business logic you’ve got hidden inside there, make a note of it for the inevitable next person who has to work out what you’re thinking. As you may have guessed my job is often to be that person.

 

[napolimp]

I mean whatever crazy business logic you’ve got hidden inside there, make a note of it for the inevitable next person who has to work out what you’re thinking. As you may have guessed my job is often to be that person.

Haha, yeah I'm trying to make it as user friendly as possible. My job isn't actually primarily IT based, and when I came in it was total chaos, so now I put together step-by-step guides to everything for the very scenario you're suggesting.

 

[wingy]

Do you mean like save elsewhere? Because I do a weekly download of the live file and replace the version I have saved on the servers. Just so there's always an offline copy available which is almost up to date.

It was somewhere around here that all the shit srarted re the site.

 

[Nick]

It was somewhere around here that all the shit srarted re the site.

What do you mean?

 

[wingy]

What do you mean?

It was after nagolimps posting on here when the popup appeared.

 

[wingy]

The reply after duffers comment.

 

[Nick]

Oh no it's not related