New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

How to repeat a cell depending on a value in another cell ?

Formulas, Functions, Formatting, Charts, Data Analysis, etc.

Re: How to repeat a cell depending on a value in another cel

Postby pbcaer » Tue Dec 27, 2011 7:41 am

hm I tried to work with yours again, tho it's a cool job, not what I seek bro,
Im sending you the file..

REGARDS

THANKS FOR HELP
  • 0

You do not have the required permissions to view the files attached to this post.
pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Wed Dec 28, 2011 5:31 am

Hi,
You moved the goal posts! :twisted:
Now you split the year into 5 and 7 months instead of 2 x 6.
This formula will deal with any number of months:
Code: Select all
=INDIRECT("$D" & 2 + MATCH($A10, $C$2:$C$6)-IF($A10=INDIRECT("$C" & 1 + MATCH($A10, $C$2:$C$6)),1,0))

But it will need more infrastructure.
I have based this formula on your column of Last Month payments to which I have added a row at the top that holds zero. This is needed by the MATCH function. MATCH compares the payment number in column A with the range of Last Months including the added zero at the top. It follows that you must have sequential numbers, starting with 1, in the rows where you list the payments.

The two INDIRECT functions require an Offset which is located relative to the Last Month list. I have adjusted the formula to look at the first row of the reference range (Last Months column). So, the INDIRECT in column D has the offset in the first row of the Last Months list, now starting with 0. The INDIRECT in column C counts the rows above the list. Therefore it is always one less than the offset in column D. The above formula has the first row of the Last Months list in row 2. Therefore the number of rows above it is 1.
It follows that you can't have your Last Months list start in row 1 without tweaking the formula.

All of this may be clearer when you look at the attached sheet where the formula has been put to use on the trial data you have provided.

I hope this will, finally, pass muster. :D
Have a great day!
  • 0

You do not have the required permissions to view the files attached to this post.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Re: How to repeat a cell depending on a value in another cel

Postby pbcaer » Thu Dec 29, 2011 4:02 am

thank you bro!!!!!!!

you rock. This is totally what I needed!!!!!

I need only 6 by 6 period but I wanted to learn a more general rule in case it changes somehow.

Thanks, will post the eventual work, when I'm done.

Been a month couldnot get the Indırect work properly. Thanks a lot.
  • 0

pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby pbcaer » Thu Jan 12, 2012 10:37 am

I have another problem now.. Is it possible to get active data in a sentence? For example; I have this in a cell:

You have 100.000 $ left.

The underlined text will be linked to another cell. I can do this via "Y......."&X99&"left." but, this sentence is way longer than this and it may require additional info, so need to be editable.. İs there a way ?

regards
  • 0

pbcaer
Rookie
 
Posts: 15
Joined: Dec 13, 2011
Reputation: 0

Re: How to repeat a cell depending on a value in another cel

Postby Sisyphus » Thu Jan 12, 2012 10:48 pm

Hi,
This is definitely a new thread. Please post your next question separate from previous ones. Thank you.

And this is how you do it:
Code: Select all
=CONCATENATE("You have ",TEXT(A4, "$#,##0.00")," left in your account")

You could have typed like this, too:
Code: Select all
=CONCATENATE("You have ",A4," left in your account")

The difference is in the format of the number you insert.

Of course, it is possible to take the parts of the text also from other cells or use IF functions to create texts that change with the contents of the referenced cell. Like ..
Code: Select all
=CONCATENATE("You have ",IF(A4, TEXT(A4, "$#,##0.00"),"no money")," left in your account")

Have fun!
  • 0

Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Previous

Return to General Excel Questions

Who is online

Users browsing this forum: Google [Bot] and 45 guests