Solve the problem and help me to understand formula [SOLVED]
- Thread starter paramveer
- Start date
SirJB7
Excel Rōnin
Hi, paramveer!
You've started 4 topics all regarding to copying formulas to other cells with non normal offsets for row and columns. As you stated in his last uploaded file I provided you a formula (which I adapted from a previous one that was proposed to you by other contributor) to solve a specific problem of addresses involving two cells. And as the specs for the offset methods were very weird (but I want to believe that respond to an actual problem and not to a mere exercise) neither you asked for a generalized formula to be moved everywhere (for which you didn't provide anything but 2 cell references) nor you were suggested a magic formula that could foresee the future.
I strongly recommend you to reformulate all your questions (actual file says question 4), create a unique sample file with the information needed for understanding all your whole offset rules, and wait and hope that someone who read that specs would be able to help you.
Regards!
You've started 4 topics all regarding to copying formulas to other cells with non normal offsets for row and columns. As you stated in his last uploaded file I provided you a formula (which I adapted from a previous one that was proposed to you by other contributor) to solve a specific problem of addresses involving two cells. And as the specs for the offset methods were very weird (but I want to believe that respond to an actual problem and not to a mere exercise) neither you asked for a generalized formula to be moved everywhere (for which you didn't provide anything but 2 cell references) nor you were suggested a magic formula that could foresee the future.
I strongly recommend you to reformulate all your questions (actual file says question 4), create a unique sample file with the information needed for understanding all your whole offset rules, and wait and hope that someone who read that specs would be able to help you.
Regards!
Debraj
Excel Ninja
Hi Paramveer..
Will you please stop playing puzzle..
Excel is very powerfull if You use it as you wants..
Please confirm below..
[pre]
[/pre]
Is this what you want!!
and above is just by using Drag ...
Regards,
Deb
Will you please stop playing puzzle..
Excel is very powerfull if You use it as you wants..
Please confirm below..
[pre]
Code:
A1 F3 When Formula in A1 fill data from F3
A7 F6 When Formula in A7 fill data from F6
A13 F9 When Formula in A13 fill data from F9
A19 F12 When Formula in A19 fill data from F12
A25 F15 When Formula in A25 fill data from F15
A31 F18 When Formula in A31 fill data from F18
...Is this what you want!!
and above is just by using Drag ...
Regards,
Deb
paramveer
Member
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx (EDITED SirJB7)
Fil name is Income tax and form 16
In this link i send you my original worksheet file
Debraj Roy : This is not done by using drag.
Sirjb7 : I am not good in excel as well as english. After starting work in excel problem arises and i discuss to yourself and other
Faseeh: I send you original file with comment on respected coloums.
So please all of you see, understand and solve my problem
Fil name is Income tax and form 16
In this link i send you my original worksheet file
Debraj Roy : This is not done by using drag.
Sirjb7 : I am not good in excel as well as english. After starting work in excel problem arises and i discuss to yourself and other
Faseeh: I send you original file with comment on respected coloums.
So please all of you see, understand and solve my problem
SirJB7
Excel Rōnin
Hi, paramveer!
I moderated your previous post since the link you posted was of a file with actual people personal information.
A week ago, b(ut)ob(ut)hc in reply to your 1st post guided you to read the green sticky topics at this forums home page:
http://chandoo.org/forums/topic/microsoft-excel-2013#post-105001
If you had read that, you should have noticed this:
"Always
Be wary that you are posting information on a public web site and that if you post confidential data, it won’t be confidential for long.
Randomize Numbers and Names if appropriate."
And this:
"Important
When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."
I think that in this case you must have done that, randomizing the names, addresses, jobs. Would you like to see your personal information as income and loans and taxes posted in the internet? Maybe one of the names in the list was of yours, and maybe you don't have any privacy concern, but are have you informed all the other 107 or 108 people in the list what were you about to do?
Regards!
I moderated your previous post since the link you posted was of a file with actual people personal information.
A week ago, b(ut)ob(ut)hc in reply to your 1st post guided you to read the green sticky topics at this forums home page:
http://chandoo.org/forums/topic/microsoft-excel-2013#post-105001
If you had read that, you should have noticed this:
"Always
Be wary that you are posting information on a public web site and that if you post confidential data, it won’t be confidential for long.
Randomize Numbers and Names if appropriate."
And this:
"Important
When Posting data online always remember to anonymise the data, especially names or data if it is commercial or confidential in nature."
I think that in this case you must have done that, randomizing the names, addresses, jobs. Would you like to see your personal information as income and loans and taxes posted in the internet? Maybe one of the names in the list was of yours, and maybe you don't have any privacy concern, but are have you informed all the other 107 or 108 people in the list what were you about to do?
Regards!
SirJB7
Excel Rōnin
Hi, paramveer!
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Solve%20the%20problem%20and%20help%20me%20to%20understand%20formula%20-%20Income%20tax%20and%20form%2016-1%20%28for%20paramveer%20at%20chandoo.org%29.xlsm
I did this, since you didn't explain fully clearly where should the info have been retrieved from.
a) Filled with any values columns C:H of worksheet '1'
b) Replaced your formulas of header at cells B2, E2, E3 as:
=DESREF('1'!$B$1;ENTERO((FILA()-2)/51);0) -----> in english: =OFFSET('1'!$B$1,INT((ROW()-2)/51),0)
changing $B$1 by $C$1 and $G$1 for the last 2
e) Replaced your cells in red by cells in blue with formulas like (D6 as example):
=INDICE('salary book'!$A:$Z;COINCIDIR(B2;'salary book'!D:D;0)+21;13) -----> in english: =INDEX('salary book'!$A:$Z,MATCH(B2;'salary book'!D:D,0)+21,13)
that's to say, it searched the value of B2 (name) in worksheet 'salary book' column D and retrieved the row number (given by MATCH), and then used INDEX to get to the found row + 21 and manually specify column for each case, in this one for M22 is 13 (as per M).
All the blue cells are alike, I set column manually when I found which column it was. For cells that remain in red you should repeat the same procedure as I guess you know of what columns are you talking about. For Tution Fees you wrote U20, as it was blank I used U22, so decrease in 2 the row (+19 instead of +21) if necessary.
f) I copied the 1st form (51 lines) at row 52 for the 2nd person, at it appears to be working.
g) Remember to type again the formula of D6 for each red cell instead of copying it, so as to keep constant the B2 cell reference (as it's a relative reference with no $ sign it's offset automatically by Excel when copying), or copy normally but edit and retype B2. Once did that for all the cells in the 1st 51 lines, then you can safely copy those 51 rows down without any issue.
So you know now how to do it, just complete the remaining fields for which you didn't specify the required information.
Regards!
Give a look at this file:
https://dl.dropboxusercontent.com/u/60558749/Solve%20the%20problem%20and%20help%20me%20to%20understand%20formula%20-%20Income%20tax%20and%20form%2016-1%20%28for%20paramveer%20at%20chandoo.org%29.xlsm
I did this, since you didn't explain fully clearly where should the info have been retrieved from.
a) Filled with any values columns C:H of worksheet '1'
b) Replaced your formulas of header at cells B2, E2, E3 as:
=DESREF('1'!$B$1;ENTERO((FILA()-2)/51);0) -----> in english: =OFFSET('1'!$B$1,INT((ROW()-2)/51),0)
changing $B$1 by $C$1 and $G$1 for the last 2
e) Replaced your cells in red by cells in blue with formulas like (D6 as example):
=INDICE('salary book'!$A:$Z;COINCIDIR(B2;'salary book'!D:D;0)+21;13) -----> in english: =INDEX('salary book'!$A:$Z,MATCH(B2;'salary book'!D:D,0)+21,13)
that's to say, it searched the value of B2 (name) in worksheet 'salary book' column D and retrieved the row number (given by MATCH), and then used INDEX to get to the found row + 21 and manually specify column for each case, in this one for M22 is 13 (as per M).
All the blue cells are alike, I set column manually when I found which column it was. For cells that remain in red you should repeat the same procedure as I guess you know of what columns are you talking about. For Tution Fees you wrote U20, as it was blank I used U22, so decrease in 2 the row (+19 instead of +21) if necessary.
f) I copied the 1st form (51 lines) at row 52 for the 2nd person, at it appears to be working.
g) Remember to type again the formula of D6 for each red cell instead of copying it, so as to keep constant the B2 cell reference (as it's a relative reference with no $ sign it's offset automatically by Excel when copying), or copy normally but edit and retype B2. Once did that for all the cells in the 1st 51 lines, then you can safely copy those 51 rows down without any issue.
So you know now how to do it, just complete the remaining fields for which you didn't specify the required information.
Regards!
SirJB7
Excel Rōnin
Hi, paramveer!
Yes, I assume that if I did all the job it'd be more helpful for you.
But:
a) You wouldn't feel the exciting and enriching experience of learning and doing that little pending part for yourself (in fact is just a matter of identifying which column and rows corresponds, subtract 4 from the column -because of the D in D1- and 1 from the row -because of the 1 in D1-, and replace them in the same formula of those all other identical).
b) You wouldn't be achieving what you stated in the topic title.
Solve the problem: I think that the problem is solved as much it could have been, missing only a couple of formulas identical to other as explained before and upwards, and this last just because of the inaccuracy of the information posted or its lack.
Help me understand the formula: I've made my best efforts to do that, sorry if I failed, but if you tell us which formula or part of it you still don't understand, if not me maybe some who read this would be able to help you further.
c) You neither provided the necessary (and asked I should add) information in your first posts nor on your last 2 ones, so even if Excel is a very powerful software (quoted) it still lacks from artificial intelligence, i.e., it doesn't do any magic. So that's your homework.
Regards!
Yes, I assume that if I did all the job it'd be more helpful for you.
But:
a) You wouldn't feel the exciting and enriching experience of learning and doing that little pending part for yourself (in fact is just a matter of identifying which column and rows corresponds, subtract 4 from the column -because of the D in D1- and 1 from the row -because of the 1 in D1-, and replace them in the same formula of those all other identical).
b) You wouldn't be achieving what you stated in the topic title.
Solve the problem: I think that the problem is solved as much it could have been, missing only a couple of formulas identical to other as explained before and upwards, and this last just because of the inaccuracy of the information posted or its lack.
Help me understand the formula: I've made my best efforts to do that, sorry if I failed, but if you tell us which formula or part of it you still don't understand, if not me maybe some who read this would be able to help you further.
c) You neither provided the necessary (and asked I should add) information in your first posts nor on your last 2 ones, so even if Excel is a very powerful software (quoted) it still lacks from artificial intelligence, i.e., it doesn't do any magic. So that's your homework.
Regards!
paramveer
Member
Hi SirJB7
a) As I told you earlier I have little knowledge about Excel and English also. I do practice on excel
b) i) You really done a fantastic job for me. You have excellent skills in excel.
ii) Don't say sorry. the fault is at my end.
In your formula i don't understand the meaning of D:D,0 ir correspond to which cell
=INDEX('salary book'!$A:$Z,MATCH(B2,'salary book'!D:D,0)+21,13)
C) You have understand my problems, Still you don't know what i want to say. You solve my problem 80%, thanks.
I believe Excel is very powerful software.
Regards!
a) As I told you earlier I have little knowledge about Excel and English also. I do practice on excel
b) i) You really done a fantastic job for me. You have excellent skills in excel.
ii) Don't say sorry. the fault is at my end.
In your formula i don't understand the meaning of D:D,0 ir correspond to which cell
=INDEX('salary book'!$A:$Z,MATCH(B2,'salary book'!D:D,0)+21,13)
C) You have understand my problems, Still you don't know what i want to say. You solve my problem 80%, thanks.
I believe Excel is very powerful software.
Regards!
SirJB7
Excel Rōnin
Hi, paramveer!
Don't worry about your English, there are a lot of people for whom it isn't their native language -as in my case- and everybody make their best efforts to correctly interpret everything that's posted.
The formula.
=INDEX('salary book'!$A:$Z,MATCH(B2,'salary book'!D:D,0)+21,13)
INDEX is a function whose syntax is "INDEX(matrix, row number[, column number])" and it retrieves the value of matrix for a given row and column (which might be optional so it's into square brackets), being in the example:
matrix: 'salary book'!$A:$Z, columns A-Z of worksheet 'salary book'
row number: MATCH(B2,'salary book'!D:D,0)+21
column number: 13
MATCH is a function whose syntax is "MATCH(value, matrix, type of coincidence)" and it retrieves the relative position on matrix where value is found, being in the example:
value: B2 cell
matrix: 'salary book'!D:D, column D of worksheet 'salary book'
type of coincidence: 0 (exact match, other possible values are 1 for approximate search in ascendent order and -1 for descending order)
So the whole formula does this, it retrieves from the worksheet 'salary book' the cell at the row where it found the B2 value (Tiger, Beer, ..., i.e., row 1, row 23, ...) plus 21, that's to say 22 for Tiger and 44 for Beer, and at the column 13. And what addresses are those of row 22 column 13 and row 44 column 13? M22 and M44, that where your indications for cell D6 of worksheet 'Income Tax'.
Hope it explains how does the formula works.
About your formulas in red that I changed for you in blue, those were for your red ones for which you provided in their comments an exact description of what columns from worksheet 'salary book' you wanted to retrieve. And about your formulas which I left in red that is because you didn't provide an exact method for identifying them (not all of us are from your country, whichever it is, not all of us are accountants nor payroll nor tax experts, ...) so for cell D26 the comment "captain: here also" doesn't give any clue of what to do. Despite of this I tried to find any relation between B26 cell contents "ELSS/NSC/PPF etc." and the header row 4 of 'salary book' and none matched.
That's why -and this is the 3rd time- I told you that your homework was identifying the correct (unprovided) column of 'salary book' that corresponded and then changing the 13 value (for column M) in the sample formula for the correct value for the involved column, repeating this process for each cell in red.
Regards!
Don't worry about your English, there are a lot of people for whom it isn't their native language -as in my case- and everybody make their best efforts to correctly interpret everything that's posted.
The formula.
=INDEX('salary book'!$A:$Z,MATCH(B2,'salary book'!D:D,0)+21,13)
INDEX is a function whose syntax is "INDEX(matrix, row number[, column number])" and it retrieves the value of matrix for a given row and column (which might be optional so it's into square brackets), being in the example:
matrix: 'salary book'!$A:$Z, columns A-Z of worksheet 'salary book'
row number: MATCH(B2,'salary book'!D:D,0)+21
column number: 13
MATCH is a function whose syntax is "MATCH(value, matrix, type of coincidence)" and it retrieves the relative position on matrix where value is found, being in the example:
value: B2 cell
matrix: 'salary book'!D:D, column D of worksheet 'salary book'
type of coincidence: 0 (exact match, other possible values are 1 for approximate search in ascendent order and -1 for descending order)
So the whole formula does this, it retrieves from the worksheet 'salary book' the cell at the row where it found the B2 value (Tiger, Beer, ..., i.e., row 1, row 23, ...) plus 21, that's to say 22 for Tiger and 44 for Beer, and at the column 13. And what addresses are those of row 22 column 13 and row 44 column 13? M22 and M44, that where your indications for cell D6 of worksheet 'Income Tax'.
Hope it explains how does the formula works.
About your formulas in red that I changed for you in blue, those were for your red ones for which you provided in their comments an exact description of what columns from worksheet 'salary book' you wanted to retrieve. And about your formulas which I left in red that is because you didn't provide an exact method for identifying them (not all of us are from your country, whichever it is, not all of us are accountants nor payroll nor tax experts, ...) so for cell D26 the comment "captain: here also" doesn't give any clue of what to do. Despite of this I tried to find any relation between B26 cell contents "ELSS/NSC/PPF etc." and the header row 4 of 'salary book' and none matched.
That's why -and this is the 3rd time- I told you that your homework was identifying the correct (unprovided) column of 'salary book' that corresponded and then changing the 13 value (for column M) in the sample formula for the correct value for the involved column, repeating this process for each cell in red.
Regards!