PDA

View Full Version : Excel: duplicating data values in blank spaces



mwot
20th July 2006, 11:57 AM
Next query in my string of Excel queries (I would have liked to have tagged this on the end of my previous Excel query, but the Forum Search doesn't appear to be working for me) ...

I often need to replicate values against, for instance, sequential dates over long periods. I'm sure that there is a smarter way other than clicking and dragging values to fill the blank spaces (or the spaces that return #N/A errors from my vlookup formulae). How can I automatically populate an #N/A field with the preceeding, valid value?

Example of my data scenario would look like this:

Date | Value
1/7/06 | 122.34
2/7/06 | #N/A
3/7/06 | #N/A
4/7/06 | #N/A
5/7/06 | #N/A
6/7/06 | 124.98
7/7/06 | #N/A

The values for dates 2/7 to 5/7 should all be equal to the value in 1/7; the value for 7/7 is to be equal to the value in 6/7. Without clicking and dragging the values down manually, is there a formula that can look for the previous value and copy that value against the appropriate #N/A?? Happy to work with additional columns.

Or any other suggestions? As always, thanks in advance.

cheers.

step_andy
20th July 2006, 12:14 PM
=IF(ISERROR(VLOOKUP(reference cell,range,colum no,FALSE)),cell above,VLOOKUP(reference cell,range,colum no,FALSE))

Jamesley
20th July 2006, 12:17 PM
I'm not sure if I'm understanding correctly but does the value stay the same each day until it changes and then it stays the same each day until the next change?

If so, make the formula for each cell in that column [= the cell above] and when a change occurs, just over write the formula with the new value.

Hope that made sense

forgie
20th July 2006, 12:32 PM
Yeah, what Jamesley said.

i.e.

If C3 is your first data cell, make C4 "=C3", then drag down C4 to the bottom of that data column. Each cell should now equal the one above, unless you overwrite it.

mwot
20th July 2006, 02:39 PM
<div class='quotetop'>QUOTE(step_andy &#064; Jul 20 2006, 12&#58;14 PM) 196814</div>

=IF(ISERROR(VLOOKUP(reference cell,range,colum no,FALSE)),cell above,VLOOKUP(reference cell,range,colum no,FALSE))
[/b]


That&#39;s the winning ticket&#33; THis formula did the trick - exactly what I was after. Thanks heaps, step_andy. Not the first time you&#39;ve helped me out with an Excel formula query, so much appreciated.

... ever considered putting the tag &#39;Excel Guru&#39; in your sig? ;)

the_OM
20th July 2006, 03:10 PM
Have you taken the microsoft certified office specialist exam andy?

forgie
20th July 2006, 03:16 PM
That whole vlookup thing is great and all, but does it actually do anything different then the much simpler "= cell above" method?

There is intellectual beauty in simple solutions&#33; :)

step_andy
20th July 2006, 03:28 PM
<div class='quotetop'>QUOTE(the_OM &#064; Jul 20 2006, 03&#58;10 PM) 196879</div>

Have you taken the microsoft certified office specialist exam andy?
[/b]
Haven&#39;t done any training, just everyday use.
I used work as an analyst for Coca-Cola, CUB (Fosters) and currently at UQ where Excel is used very extensively.

and, I just love playing around ;)

Still remember the days at Coke were we were rolling out Business Objects and some "specialists" were saying to our requirements that some things "can&#39;t be done". Just loved finding ways of overcoming "can&#39;t be done" things.

mwot
20th July 2006, 05:23 PM
<div class='quotetop'>QUOTE(forgie &#064; Jul 20 2006, 03&#58;16 PM) 196882</div>

That whole vlookup thing is great and all, but does it actually do anything different then the much simpler "= cell above" method?

There is intellectual beauty in simple solutions&#33; :)
[/b]
What I was needing to do was a bit more complicated that a simple &#39;=above cell&#39; formula. step_andy&#39;s solution takes into account errors in the vlookup tables (where a value might not exist for the date range I&#39;m looking at and then finds the previous value to apply to that date).

Incidentally, I&#39;m booked in for an Advanced Excel course on Monday ... I&#39;m doubtful as to what they actually mean by &#39;advanced&#39; however, but will reserve judgement till after the course.

step_andy - you&#39;re proof that experience and a willingness to play around is what really counts. It&#39;d have taken an enternity for me to try and find something on the &#39;net. i&#39;ll be sure to pose the same problem to the course instructor on Monday to see if he/she can solve it too&#33;