[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Excel 2010 'renumbering' forumla values on linked worksheets

Posted on 2011-09-09
9
Medium Priority
?
356 Views
Last Modified: 2012-05-12
Hi All -

I have a pretty basic workflow going on that I'm having issues with.

I have a database with values. These values are then pulled into an excel doc on a worksheet. Nothing formatted there - just a pure 'data' worksheet. Then I have another worksheet where I map in the =VALUE(Data!AC3)... in to the appropriate field. Well I have a bunch of these so I just drag the formula around so each cell is sequential in the formula. It works great. I can refresh it and it refreshes...

UNTIL

A new row is added to the source data.

Whenever this happens, the sequence on the formulas skips a number, so instead of going from =VALUE(Data!AC3) to =VALUE(Data!AC4) in the next cell it goes =VALUE(Data!AC3) to =VALUE(Data!AC5)

I have no clue how to fix this as I've never seen it happen before.

Any ideas why??? Any ideas on how to fix it??

It's Excel 2010 and MS SQL Server 2008 R2 RTM.

Thanks in advance!
0
Comment
Question by:rmm2001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36511909
Try this instead:

=VALUE(INDIRECT("Data!AC" & ROW())

This assumes that row 1 of data sheet goes to row 1 of other sheet. If not put +n between the last brackets with n being the number of rows that they are different.

Thanks
Rob H
0
 
LVL 7

Author Comment

by:rmm2001
ID: 36511929
Hmm I've never used INDIRECT before (although I've seen it posted online in places). Do I need to reference my cell number when i do the "Data!AC"?

The row numbers don't match up. So Data!AC5 would match up to [other worksheet]!BH789

Would that be  =VALUE(INDIRECT("Data!AC" & ROW()+784)?

Thanks!
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36512001
Sorry, in that case it should be -n so for your example it would be:

=VALUE(INDIRECT("Data!AC"&ROW()-784))

or

=VALUE(INDIRECT("Data!AC"&ROW(BH5))

To expand on the original question:

Why is it happening? in your example, Contents of Data!AC5 are linked to OtherSheet!BH789. New data is inserted into the data sheet by inserting rows. Excel has a link to row 5 but row 5 has just been pushed down to row 6 by inserting a row above it, therefore the link moves.

How does INDIRECT work?  A simple formula of =A1 will return the contents of A1. Whereas =INDIRECT(A1) will look at the text contents of A1 and evaluate the contents as a cell address and return the contents of that cell instead. If the text contents do not evaluate to a cell address it will return an error.

So the formula above is forcing the "Data!AC" part of the cell address as a text string and then ROW() returns the row number of the cell reference within the brackets or if blank the row of the cell in which it is placed. Thus completing the cell reference.

Thanks
Rob H
Rob H
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 7

Author Comment

by:rmm2001
ID: 36512044
Oh wow that makes so much sense! Thank you for explaining that.

I have a row like this:

=VALUE(SUM('Data'!H2:I2))

How would I convert that to using the INDIRECT? (Or does it need to be two steps)
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36512045
This approach is somewhat volatile as it relies on the structure of the 'OtherSheet' remaining static.

Is there any other reference that could be used to identify the relevant row from the source data, eg lookup on another column.

Thanks
Rob H
0
 
LVL 7

Author Comment

by:rmm2001
ID: 36512089
The other sheet will be static - it's just a linked table in from SQL server. There's really no lookups - just the two fields there that need summed. Would putting them in their own field on the "OtherSheet" be the best approach?
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 36512093
I assume it would be the 2 in the formula that might change.

=VALUE(SUM(INDIRECT("Data!H"&ROW()&":I"&ROW())

With the adjuster after the ROW() if so required.

Thanks
Rob H
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 2000 total points
ID: 36512125
Easiest way to develop the formula like this would be:

1) create the formula as it should be

=SUM(H2:I2)

2) Convert the range reference to text

=SUM(INDIRECT("H2:I2"))

3) Replace parts of the formula within the text with formulae that equate to text or numbers depending on part using " " around hard coded text strings and the & as a joiner to keep the text string intact.

=SUM(INDIRECT("H"&ROW()&":I"&ROW()))

The creation of the text string can be done in a separate cell and then copied in if so required.

Thanks
Rob H
0
 
LVL 7

Author Comment

by:rmm2001
ID: 36512147
Oh wow - thank you so much! This makes a lot of sense!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question