Adding manual data to a power query?

I'll set the scene..

We have a big ident list at work with around 1000 items on it. Each entry has a unique ID number. The list is split into 6 sheets because different people are responsible for looking after each one.

I need to do two things..
1. I need a combined list of all 6 sheets so that I can easily search and reference data with INDEX & MATCH etc.
2. I need to be able to add my own data to the end few columns.

I've managed to work out how to get power query to append all the sheets together onto one master list (I've also removed some of the columns I don't need). I was hoping that I could then simply add the columns onto the appended output for manual data entry from there but it doesn't seem to like it for some reason. I tried putting in some dates and it keeps removing them at random when I refresh the query.

Is there a way I can tell it to leave those few columns alone or something along those lines?

Thanks for the reply.

So there's no simple way of adding data to an appended table in excel? By the sound of it it's best to leave those tables alone?

I made a similar file before, not usuing query but using about 10'000 VLOOKUPS. It wasn't ideal because it required both files to be open, couldn't tell if a new row was added, and it was pretty heavy on the memory usage. So I was hoping for an easier solution but I guess it's just not as simple as it sounds in my head :/

I could send you a very rough file if you'd like to take a look? I'll have to rename and delete a few things and I'd rather not paste the link directly on here as it's confidential but I can send you a version of it?

Sure, you can desensitize data as much as you can, and you can PM me. I'm bit busy over the weekend, but I can take a look when I find time.

Thanks

I sent a PM. Not sure if its actually gone or not because its not showing in my Sent folder.

I've received it.

Few suggestions.

1. I'd recommend adding another sheet for set up.
It can house CELL() function to dynamically change file location and query path (to pass on to PQ). You can find tutorial on this in thread below (post #6)
https://www.excelforum.com/excel-gen...mic-range.html

2. Since you have Trial no. as unique. I'd have separate sheet for data entry inside Master workbook, using Trial no. to add columns. Then merge (not append) that info to the query using left join.

What would be the process flow?

1. Thanks ill look into that.

2. Ok so this would allow me to input data into the 2nd sheet and then it'd be displayed on the master list? I guess that could be a solution. The only problem is, as you can see, some of the trial numbers haven't been assigned yet. So if the 2nd sheet is independant of the source file then it won't update when a new trial no. gets added.

I feel like this is probabaly as close as we're going to get though.. Maybe it's best to just talk to the trial managers and see if they'll let me add the columns to the tables on the source file?

That would be the simplest method. Yes.

As well, another thing you could implement, is to have it mandated that before you take any action, trial no. must be assigned. If that's possible.
Then you filter out null values on Trial no. column in your query.

Oh, here's sample query, which is done in single query instead of using separate query to each sheet. I'll send the file in PM.

Please Login or Register  to view this content.

My bad.

@S7ewi
If you are ok with it. I'll upload the file here.

Thanks for your help. Ill give that a try :D

Ermmm.. I'll make another copy with less info in it. I know I'm being paranoid but I could literally be fired if I was found to have let that out.

Ok well if it's that much of an issue then feel free to delete the thread. I was after some help with a personal issue, CK76 was kind enough to help out. The file is safe and doesn't share any confidential information but there's still enough on there to trace it back to me if the right person saw it. I wasn't aware every detail had to completely public. I've not been on that kind of forum before.

I apologise, and if it's that much of a problem please feel free to delete the topic.

I appreciate the advice but I know my company policy. There's nothing on there that breaks any kind of confidentiality agreement. I'm just paranoid that it could still be traced back to me becuase I had to leave some basic info in, otherwise the table would have been empty.

It seems as though, what I was originally asking, can't be done anyway so that's pretty much it :/

I understand

Thanks guys, I think I'm going to rewrite the thread as more of a project because I think I need to look at other solutions now.

Thanks