PDA

View Full Version : Insert row automatically?



n00b82
12-12-2016, 02:48 PM
So after hours and hours of searching, I feel I'm only scratching the surface of my problem. Full disclaimer to start - I'm completely new at this and self-taught, so please bear with me if I ask this question poorly.

Here's the scenario: Within a worksheet I have a table created that is linked to an Access database, that inserts a cell of information (general numbers) as the database gets updated by users. The general numbers are generated (by a different program) according to dates/times/number of incidents, and are sorted in the worksheet in chronological order (and need to be). I have subsequent cells in the same row that are then manually filled in with information that corresponds to the linked column. If a number is entered out of the chronological order (number 444 was entered after 555), it is still inserted appropriately into the linked column, however, the adjacent columns do not insert a blank row, or shift down the information to stay aligned.

I'm looking for a solution, whether it be VBA or otherwise, to solve this issue. The data is updated upon opening, if that matters.

p45cal
12-12-2016, 03:16 PM
If a number is entered out of the chronological order (number 444 was entered after 555), it is still inserted appropriately into the linked columnIs there some code which does this? I suspect so, in which case we'd need to see it so that it can be tweaked.

n00b82
12-12-2016, 06:22 PM
Is there some code which does this? I suspect so, in which case we'd need to see it so that it can be tweaked.

The numbers are reflections of dates and times, and Access reads and sorts the information based off of that, which I've assumed is just a 1:1 display in excel.

In the worksheet I've merely created a table that updates the data each time it's opened. If there's code controlling the linked cells, I didn't write it, and I'm unaware of how to access it. Nothing shows up in the visual basics window when I open it for the worksheet.

p45cal
12-12-2016, 06:54 PM
Supply a file maybe?

n00b82
12-12-2016, 07:04 PM
Unfortunately I can't post the file, but if it's absolutely necessary I can create one with superficial information. It'll take awhile.

n00b82
12-13-2016, 12:30 PM
17829

This attachment is set up the same as my worksheet, just without any real information. None of the programming was changed

p45cal
12-13-2016, 04:40 PM
same as my worksheet, just without any real information. None of the programming was changedJust all of the programming removed. There's no code whatsoever.

n00b82
12-13-2016, 05:46 PM
Just all of the programming removed. There's no code whatsoever.
That's why I said before that there isn't any

p45cal
12-13-2016, 06:21 PM
Right, so the cells in the top left of the sheet update themselves somehow, and it's not with code in this workbook. There's no querytable in your file which I suspect there might have been (can you right click say on cell A2 and choose 'Refresh' in the real worksheet?), you may have deleted it while preparing this file. You might find it under Data|Connections.
How did you: "In the worksheet I've merely created a table that updates the data each time it's opened."? You can safely leave such a query in the workbook as long as it depends on (queries) a file I don't have access to.
I need to know how the data is getting in/updated.

Currently all the formulae in the sheet refer to cells on the same row; is it the case that some formulae no longer refer to cells of same row after an update such as you describe?

p45cal
12-14-2016, 05:04 AM
In the absence of a response since my last message (you're probably tucked up in bed) this will be a complete shot in the dark; if, while the selection is in the table, you go to the Data tab of the ribbon, in the Connections section there should be a Properties icon, click it and the following dialogue box might come up:
17831

try different options in the circled area then refreshing the data after someone enters something out of sequence.

n00b82
12-14-2016, 08:12 AM
Yeah, the time differences are making this difficult lol
Thank you for continually checking though!

So, I think if I understand your first question: in the worksheet I attached - columns B C and D have formulas that refer to column A. The rest of the columns are manually entered after the fact. I didn't realize removing the data also removed the query, but I'll attach a new copy - hopefully with the query still intact. To create it, I used the Get External Data option, and then from Access and told made the spreadsheet pull a column of data from the database to column A of the worksheet (which hopefully you'll see in the new attachment).

I tried your second option to no avail. When "middle" data was entered, it just inserted it, and shifted column A down - but the other columns stayed the same without shifting down.

n00b82
12-14-2016, 08:20 AM
17833

Hopefully this one maintained the query

p45cal
12-14-2016, 08:34 AM
but the other columns stayed the same without shifting down.OK. But did the formulae in other columns still refer to cells on the same row as the formulae or had the references moved up/down a row or two?
I'll have a look at the file later today.

By the way, something I don't quite understand, the query to Access seems to return 4 columns, but you've got formulae in the right 3 columns..(B,C & D) How?
Maybe it's only the formulae in columns E to L that you're worried about?

n00b82
12-14-2016, 08:51 AM
Columns B C and D (with the formulas) still referred to the correct cell. The are set up to just refer to the cell of the same row. So when the data is updated, and column A adds a new cell of information, B C and D automatically update as well. So, even though they aren't inserting a new row, per say, they stay accurate to the associated information on their left in A.
However, the columns that are manually updated after the fact (columns AA through HH), do not insert a blank row of information - which is what I'm trying to solve. My "manual" columns are then in the wrong row, and with a whole spreadsheet with hundreds of rows, its impossible to tell where the new cells were inserted in columns A through D.
The only other thought that occurred to me was to see if there was a way to somehow highlight the new data that was inserted after the updated (upon opening) and then manually insert a new row in the "manual" columns. But, again, I have no idea if this is possible and I'm still researching that possibility.

p45cal
12-14-2016, 09:26 AM
There are 2 possibilities I'm thinking of:
1. You say the Access query updates only 1 column (column A) and the formulae in the rest of the table (columns B to D) update properly. I don't understand how this is happening, but you say it is, in which case, can you resize the table that currently only occupies columns A to D and make it include A to L (headed HH). You can resize a table in your most recent file attachment by grabbing the tiny triangle in the bottom right of cell D33 and dragging to the right as far as you need. You can also do this in the ribbon:Table Tools|Design tab, Properties section at the left, Resize Table.

2. Tell me what the formulae in those columns actually are (supply another file maybe) and we'll write a short macro to update the formulae to refer to what they should refer to, every time the table gets refreshed!

n00b82
12-14-2016, 11:02 AM
There are 2 possibilities I'm thinking of:
1. You say the Access query updates only 1 column (column A) and the formulae in the rest of the table (columns B to D) update properly. I don't understand how this is happening, but you say it is, in which case, can you resize the table that currently only occupies columns A to D and make it include A to L (headed HH). You can resize a table in your most recent file attachment by grabbing the tiny triangle in the bottom right of cell D33 and dragging to the right as far as you need. You can also do this in the ribbon:Table Tools|Design tab, Properties section at the left, Resize Table.

so, my issue with that (and maybe I did it wrong the first time) is that when the table covered all the columns, excel was rearranging the information in the manual columns, randomly, each time it was updated. You could manually refresh all data, and watch the cells jumps around within the column.
however, let me try it again with a copy of the workbook and see if I can recreate it.

2. Tell me what the formulae in those columns actually are (supply another file maybe) and we'll write a short macro to update the formulae to refer to what they should refer to, every time the table gets refreshed!

the formulas in columns B-D just refer to column A. I esssentially use a formula to pull the correct swction of numbers from A, put it in date format, and then use an IFERROR to keep the blank cells from showing as #ERROR when column A is blank.
E through L are just drop down menus that link to other sheets in the actual workbook. No formula are used.

n00b82
12-14-2016, 11:02 AM
My first response is in between your questions in the quote box :/

p45cal
12-14-2016, 11:26 AM
Try possibility 1 with the various options in msg#10, but also try removing the tick from Preserve column sort/filter/layout, especially if the query returns things in the right order. A bit of trial and error. I'm just wondering if the data is sorted in Access efore it's transferred. It might be that instead of a straight dump of a table from Access we could do an SQL with sorting while importing.
If that fails, then I'll have to think again…