Hello guys,
I have an un-solved problem at https://www.mrexcel.com/forum/excel-...ml#post4834698 .
May I request you to look into it please?
Here is the original workbook (where data is both in Sheet1 and Sheet2) : http://s000.tinyupload.com/?file_id=...35416706175982 (I was unable to attach here]
Thanks for your time.
-Daniel
to upload a file her which is preferable to going to an unknown source:
Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
OK, initially I tried clicking the Attachment icon, now I have uploaded it here. Thanks.
Given that there have now been more than 20 replies at Mr Excel and there is no description on EF as to what is wanted.... I'll leave this one to Mr Excel.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
@ Glenn Kennedy:
My questions arose after getting the 20+ replies at Mr Excel.
As to what is wanted as a solution, understanding the problem in full is required, and for that, reading the concerned thread in full at Mr Excel is necessary in my opinion. I thought that repeating the problem in full here too is redundant, and hence I didn't ask explicitly what is wanted.
Anyway, if you want me to explicitly ask, here are my questions:
(i) How to make the cells show only blanks instead of zeroes under the relevant situations?
and
(ii) What would be the formula that can accommodate deletion of rows and/or insertion of columns?
Yorkuvir - this forum and Mr Excel are two separate communities: you should not expect members of this forum to have to trawl through long threads elsewhere in order to understand your issue. You must provide the requisite information here yourself in order that anyone with no prior knowledge of the issue may help you without having to look elsewhere. Thanks for your co-operation and understanding.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
I had no intention of wading through 20 odd posts, following all sorts of twists and turns. To be honest, I'm still not 100% sure what you want. What are/were you hoping that the formula in I2 was doing??? I think I may have addressed some of your other issues regarding zeros, etc. Please come back with specific comments, if any.
@AliGW :
Thanks for kindly letting me know. I will follow your above advice hereafter.
@ Glenn Kennedy:
Thanks for attempting to provide a solution.
OK, I will try to ask my questions in entirety:
I have 2 worksheets xxx and yyy, both attached below.
In file xxx, I have placed a formula at P2, and calling it at G5 through I18 (and below). Obviously, the formula transposes values in cells D4..D6, D7..D9, etc. into G5..I5, G6..I6, etc.
Copying the ZERO values from D4 into G5, D16 into G9, etc. is done correctly. However, the formula also copies BLANK values as zeroes. For example, D10 is copied into G7 as zero, not a blank.
I wish that zero values are copied as zeroes, and blank values are copied as blanks only (not zeroes).
How do I modify the formula in G5 through I18 (and below) to do this?
The above was my first question. And the second question is below:
I copied the file xxx into a new file, copied the formula at P2 into the same row as of the headers 'Test-Score Judge1 Judge2' and deleted the first 3 rows in the file xxx, and saved it as a new file yyy. I expected the formulas at G5 through I18 (and below) to adjust themselves to reflect the row-deletion. However, I find that the first 3 values [0,75, 27 - highlighted in yyy file] are not reflected under 'Test-Score Judge1 Judge2' and they are simply not there but the remaining values are all there from H2 to J14 (and below).
How do I modify the formula in G5 through I18 (and below) in xxx file to accommodate the row-deletion and reflect the values correctly?
Thanks for your time.
One way for xxx:
=IFERROR(IF(COLUMNS($A:A)<=3,IF(INDEX($D$4:$D$45,COLUMNS($A:A)+(ROWS($1:1)-1)*3)="","",INDEX($D$4:$D$45,COLUMNS($A:A)+(ROWS($1:1)-1)*3)),""),"")
Odd formula at P2 (still don't know what it was for...) deleted.
Try this as a solution t 1 and 2.... {Please put me out of my misery, though. What was P2 supposed to be doing??
Perfect! Your latest solution has worked very well for me and it addresses both the problem with a single formula. Thanks a lot!Originally Posted by Glenn Kennedy
The formula at P2 was placed 'to calculate the row number of the last row with data in it' , as advised by another gentleman. If you would like to know more about it, the specific post is at https://www.mrexcel.com/forum/excel-...ml#post4834663 .
Thanks again.
-Daniel
You're welcome.
If that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
It'd also be appreciated if you were to click the Add Reputation button at the foot of any of the posts of all members who helped you reach a solution.
here's a better way to check out. I have used a Named Range (CTRL-F3 to view) to make the data range in column D fully dynamic. Should be even better than the last version.
Excellent again. I have checked out the latest version, looks even better. I have added reputation for you, and have marked the Thread as Solved. Thanks.
Ye can always get better help at the Excel Forum!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks