Specification Comparison Table from Different Worksheets

Hi Ali, probably easier if I share the file with dummy data for better communication. As you can see, the layout for my real data will be identical to the one attached. I tried following the instruction but seems not working...
 

Attachments

  • Camera Revised Inputs.xlsx
    43.8 KB · Views: 4
Well, yes and no! This way, I just do it all again for you and you learn nothing.

Far better to share with me the version that shows how far you got and allows me to troubleshoot. So do that, please.
 
OK - the thing is, when you ask for help, you really need to try to make sure that the data you provide as a sample truly reflects the data you are working with in terms of layout. You didn't do that, and this is why it's failing.

Never mind! I have reworked the instructions up to and including step 5. From step 6 onwards, you will need to ADAPT the cell references to fit the new parameter list, but I think that you are quite capable of doing this yourself. If not, then shout again.

Attached are the updated instructions and an updated version of your workbook taking you as far as and including step 5.
 

Attachments

  • PQ Camera Comparison V2 AliGW.xlsx
    80.6 KB · Views: 8
  • PQ for Camera Comparisons User Guide.docx
    260.8 KB · Views: 8
Aaarrrggghhhh!!!!

I have just noticed another bloody merged cell - ROLL. Going to have to re-write again. NOW I'm getting annoyed. :(
 
Well, you've made this much harder to test by using the RAND() formula and not then copying and pasting values, and I don't have time to fix that on top of the other changes that needed to be made. I hope that what you have shared this time properly reflects the real data, because I really don't have any more time to spend on this, sorry.

Attached are the COMPLETED up-to-date instructions, including a fix for the ROLL issue, and a copy of the workbook with the whole lot implemented.

I am happy to consider any MINOR tweaks going forward, but I won't be rewriting the whole lot a third time - I do hope you understand. You need to get stuck in and work through each of the steps in the queries to begin to understand how they work. It's a fairly steep learning curve with PQ, but very well worth the effort: this is something I now use every day at work.
 

Attachments

  • PQ Camera Comparison V2 AliGW.xlsx
    89.2 KB · Views: 7
  • PQ for Camera Comparisons User Guide.docx
    261.7 KB · Views: 6
Hi Ali, thanks for the advice. Sorry that I've caused so many issues for you. I was suddenly inundated with some urgent projects. I do apologize and I overestimated my ability to be able to troubleshoot in the hopes to not cause too many troubles for you, but obviously it failed miserably. I'll dive into it and feedback next week.

I really appreciate your kindness and help in this, and will definitely revert on how I've been going.
 
I've just been inundated with urgent timetable changes to be done before Monday and I have to work on next year's timetable, so I won't be around so much for the next few weeks as term starts up again.

I have a real fondness for PQ, so ensuring that any solution works for anyone I help is really important to me as I want people to know how good it is. I can be a woman on a mission when time permits, so I'm looking forward to the pleasure of retirement from July.

If you have questions, just post them here and I'll respond, but it may not be immediate. :)
 
Hi Ali, sorry it's been a while since I posted. I've been playing with the worksheet with my own data and it was working really well until the last step. where I get the "N/A" error in some of the cells which I have data in them. Unfortunately what i have is quite sensitive data so I'm not sure how to post it here for troubleshooting. Should I try to replicate them in the camera worksheet?
 
I can't troubleshoot it without, although an #N/A error sometimes means a possible mismatch or a leading/trailing space issue.
 
Yea I'm looking through the worksheet. The list down to trigger different models are fine but i'm not quite sure why some of the rows trigger #N/A. Is there a way to view excel cells to see the formatting of individual cells? Else it's so close yet so far!! Argh!
 
It will be fixable, but I can't help without a workbook - sorry. A screenshot MIGHT help.
 
Hi Ali, if I were to email you the attachment would it be okay to have a look at it? The file is too confidential to show in a public domain like this unfortunately.
 
I would, but you don't know me, who I really am or whether I can be trusted. Do I want to share my personal E-mail with someone I know nothing about? No. So I'm afraid it's either provide a desensitised copy here that shows the error, or try to fix it yourself. It's possible that trimming one of the fields in the query might resolve the issue, or changing the formatting of one of the fields.
 
Hi Ali, I do understand the dilemma here. I seem to be able to replicate the issue here in this camera workbook and provided some dummy info. As you can see, some #N/A shows up itself and dont seem to be able to reference the correct cells. Thanks.
 

Attachments

  • PQ Camera Comparison V5.xlsx
    72.9 KB · Views: 3
In the Name Manager, change the Parameters reference from this:

=Compare!$A$2:$B$59

to this:

=Compare!$A$1:$B$59

then refresh queries.
 

Attachments

  • PQ Camera Comparison V5 AliGW.xlsx
    73.2 KB · Views: 4
Hi Ali, thanks for the error spotting! The #N/A i have in the table results in error arises from the formula: =IF(D$1="Brand & Model","",LOOKUP(2,1/((Composite[Brand & Model]=D$1)*(Composite[Parameters]="Resolution "&$B12)),Composite[Value])) for specific cells. Can you educate me on how is it executed? I have narrowed it down to here being where the problem is for the "real data". As far as cell formatting is concern, I've copied the same formatting style as other cells and just replace the values in there and still yields #N/A error.
 
Did you try the fix on your real data? Until you do, the formula will not be able to find anything to return. If that's not fixing it in the real data, then something else is at play.

You can share the real file with my via a private message using a link to a file-sharing site (r.g. DropBox) if you are prepared to take the risk and trust me, but you can't attach files directly to PMs.
 
Hi Ali, yea I tried to fix it for few days now without success. I really did try to understand the codes but it's a little too much for me at the moment. I will proceed with PM you on a dropbox link in the next 2 hours. Thanks Ali.
 
A few days??? I only gave you the fix yesterday! Are you SURE you applied that fix AND refreshed the queries???

I will have a look at your DropBox link.
 
Back
Top