Tom's Guide | Tom's Hardware | Tom's Games

Computing.Net > Forums > Office Software > how to do this formula

Computer Problems? Computing.Net has over 1,000,000 posts about all things technology related! Over 90% answered within 24 hours! Click here to start participating now! Also, be sure to check out the New User Guide.

how to do this formula

Reply to Message Icon

Name: dogface
Date: January 20, 2008 at 22:07:05 Pacific
OS: xp home
CPU/Ram: 1 gig
Comment:

Could someone please help me with this formula
If I put any data between cells A1 to A7 The same data will show up in cell A10
Thanks




Sponsored Link
Ads by Google

Response Number 1
Name: DerbyDad03
Date: January 21, 2008 at 08:55:38 Pacific
Reply:

The first question we have to ask is:

Are you are only talking about *one* piece of data in A1:A7 or do you need to combine all the data in A1:A7 and put it in A10?

The next question would be is what type of data is it: text, numbers, dates, etc?

A single piece of numeric data anywhere in A1:A7 could be handled as easy as =SUM(A1:A7)

Assuming all other cells in A1:A7 don't contain numeric values, then only that single value would show up in A10.

For a single piece of data of any form, you could use:

=IF(A1<>"",A1,IF(A2<>"",A2,IF(A3<>"",A3,IF(A4<>"",A4,IF(A5<>"",A5,IF(A6<>"",A6,IF(A7<>"",A7,"")))))))

(This would return the first peice of data found in A1:A7)

To combine data, you might use:

=CONCATENATE(A1,A2,A3,A4,A5,A6,A7)
or
=CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",A7)


0

Response Number 2
Name: dogface
Date: January 22, 2008 at 01:39:30 Pacific
Reply:

Thanks, DerbyDad03 for your response.

I would like one numeric data in each cell from A1 to A7. When I put a numeric data into anyone of these cells between A1:A7 It should automatically go into cell A10.
I dont want the data to add up.
Your other Formulas will come in handy later on.
This Formula did not work for me
=IF(A1<>"",A1,IF(A2<>"",A2,IF(A3<>"",A3,IF(A4<>"",A4,IF(A5<>"",A5,IF(A6<>"",A6,IF(A7<>"",A7,"")))))))

THanks for any help you can give me


0

Response Number 3
Name: Bryco
Date: January 22, 2008 at 03:40:54 Pacific
Reply:

Then you want the last formula offered by DerbyDad03:

or
A10=CONCATENATE(A1," ",A2," ",A3," ",A4," ",A5," ",A6," ",A7)

The data will appear with a space in between each value.

Bryan


0

Response Number 4
Name: DerbyDad03
Date: January 22, 2008 at 05:40:01 Pacific
Reply:

If the CONCATENATE formula does not do what you want, please provide an example of input and output. Show us an example of what might be in A1:A7 and the resulting contents of A10.



0

Response Number 5
Name: dogface
Date: January 22, 2008 at 22:46:54 Pacific
Reply:

Ok this is what I would like
A1 1 ---- A10 1
A2 3 ---- A10 3
A3 4 ---- A10 4
A4 2 ---- A10 2
A5 6 ---- A10 6
A7 17 ---- A10 17
What ever number I put into the cells,A1 to A7. I want it to mirror it into A10
The formula you told me use works,but it puts all the numbers into A10 at the same time. Each hour I have to put a number in a cell between A1 to A7. So each hour A10 will change.
Sorry for any confusion I gave you in my early post and the way I wrote it. I hope you understand what I am trying to say.
Thanks for having patients with me


0

Related Posts

See More



Response Number 6
Name: Bryco
Date: January 23, 2008 at 05:31:20 Pacific
Reply:

I don't know the answer but to make it clear...
You want cell A10 to show only the last number entered in any cell between A1 to A7.

Is that what you are looking for?

Would it not be easier to just enter the last number in A10 after entering it into the cells between A1 and A7?

Bryan


0

Response Number 7
Name: DerbyDad03
Date: January 23, 2008 at 08:34:23 Pacific
Reply:

I'm still as confused as Bryco. Obviously A10 can't match *every* cell in A1:A7. What you haven't told us is which value in A1:A7 at any given time should end up in A10.

If the value in A10 should be last value that you enter anywhere in A1:A7, then try this:

Right click on the sheet tab, chose View Code and paste this macro into the code window that opens. Everytime you make a change anywhere in A1:A7, A10 will mirror that change.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A7")) Is Nothing Then [A10] = Target
End Sub

If you looking for A10 to mirror the "last" cell in A1:A7 - i.e. start looking at A7 and move up until you find a value, then try this code. Note: This code assumes that there are no blank cells above the last piece of data. e.g. if A5 is the last cell populated, then A1:A4 must also be populated.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A7")) Is Nothing Then
[A10] = Range("A" & Range("A8").End(xlUp).Row)
End If
End Sub


0

Response Number 8
Name: dogface
Date: January 24, 2008 at 01:47:21 Pacific
Reply:

Thanks DerbyDad03
that last formula work fine

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:A7")) Is Nothing Then [A10] = Target
End Sub

Sorry that you could not understand what I was trying to say in the above post.
Many thanks again



0

Sponsored Link
Ads by Google
Reply to Message Icon

corrupt assignment Nero issues



Post Locked

This post is quite old and has been locked from receiving new replies. Please create a new posting instead.


Go to Office Software Forum Home


Sponsored links
Ads by Google


Results for: how to do this formula
how to recieve yahoo thru firebird? www.computing.net/answers/office/how-to-recieve-yahoo-thru-firebird/5326.html

How to do it by script? www.computing.net/answers/office/how-to-do-it-by-script/4550.html

How to show distribution list name? www.computing.net/answers/office/how-to-show-distribution-list-name/2788.html