• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help to auto populate cells based on a number in an input cell

R

razaas

Member
Please help with 3 formulas to auto populate the numbers, sample highlighted in yellow, in the attached excel file. Cell K5 is the input number, based on K5 all yellow highlighted cells shall auto populate in the pattern shown in Table 1 and if the assigned formulas working fine then the result should look like the Table 2 as the number in K5 change to number in K12, hope this makes sense and some of the experts here can provide a solution.
 

Attachments

  • Book1.xlsx
    8.9 KB · Views: 5
Hi ,

A few questions :

1. Will the maximum number of columns to be auto-populated be 8 ?

2. What will be the range of values which can be input into cell K5 ?

3. Is the logic as follows ?

The first 6 values , will at most be 50.

The seventh value , if present , will at most be 100.

The eighth value , if present , will be the remainder after subtracting 400 from the entered value in K5.

Narayan
 
Thanks a lot NarayanK991 for looking into my query, yes the number of columns will be 8 only, max input value cell K5 can be 2500 and all other Qs "yes" you guessed them correctly.

Thanks,
Raza
 
Hi Narayan,

Thanks a lot, it works well, but you missed the 3rd highlighted row, in the mean time i tried myself and succeeded with very long IF formula and first row with hard coded numbers, i wanted one formula for all cells, sample attached for your comments and suggestions. Please check all 3 rows, C2, C3 & C5.

Thanks once again, highly appreciate.

Raza
 

Attachments

  • Book1_a.xlsx
    9.7 KB · Views: 5
Hi ,

I have checked your file , and I do not find the formula I used in my uploaded file.

If you have no use for that formula , I do not intend to start all over again. Sorry.

Also , you have clearly mentioned :
i wanted one formula for all cells
Click to expand...
I don't think I can come up with such a formula ; also , I think it is a waste of time trying to think of one formula , when the 8 columns where this formula is going to be used , can be separated into 3 sections of 6 columns , 1 column ( the 7th column ) and another 1 column ( 8th column ).

Secondly , the formula for one row may or may not be the same for another row , especially when the logic itself is not the same.

Narayan
 
Hi Narayan,

Don't be so arrogant please, I did not ask you for another formula, appreciating your super skills I just asked your comments and suggestions on the formula in the attached file, few of my comments which I think you ignored
in the mean time i tried myself and succeeded with very long IF formula and first row with hard coded numbers
Click to expand...

sample attached for your comments and suggestions. Please check all 3 rows, C2, C3 & C5
Click to expand...

i wanted one formula for all cells
Click to expand...
what I meant was for all cells in each row which I achieved as in the attached Book1_a (Reply # 5) above
 
Hi ,

In my 'arrogance' , I will ignore your first sentence.

The problem description should come from you , since others cannot guess what your problem is.

Initially , you have highlighted three rows , signifying that you wish to have formulae in those 3 rows.

Now , you say that you have hard-coded values in the first row ; on this you want my comments ? No comment.

In the second row , you have a formula , which works provided the first row has hard-coded values in all the cells ; if these values are replaced by the appropriate formulae , then the formulae in the second row in the second row will not work.

In the third row , you have a formula which works provided the second row is correct ; check what happens if a value of 399 is entered in K5.

Narayan
 
Initially , you have highlighted three rows , signifying that you wish to have formulae in those 3 rows.
Click to expand...
You have not given any formula for 3rd row.

Now , you say that you have hard-coded values in the first row
Click to expand...
Because I am not expert like you.

In the second row , you have a formula , which works provided the first row has hard-coded values in all the cells ; if these values are replaced by the appropriate formulae , then the formulae in the second row will not work.
Click to expand...
In the third row , you have a formula which works provided the second row is correct ; check what happens if a value of 399 is entered in K5
Click to expand...
I knew my solution might not be perfect that is the reason I asked for your comments and suggestions, you gave only comments but no suggestions.

Highly appreciate your comments, your formulas for first two rows, please give third row formula if not very angry:)

Thanks a lot.

Raza
 
You must log in or register to reply here.
Back
Top