I'm trying to reference a different tab in Excel that has an NL(Rows) function, but I don't want the cell references to change when the replicator creates new rows.
Sheet 1 which has the replicator pulls item, usage, and forecast data.
Sheet 2 is a bunch of cell references to Sheet 1 that converts and formats the data differently. Essentially, a reference to cell C10 in design mode moves to cell C100 in report mode, but I still want it to reference C10.
Hopefully I've explained myself clearly enough.
Does anybody have a solution?
My backup solution is to put all formatting and conversion on the same sheet as the raw data, but I'd like to avoid this if possible to make the users' lives easier.
Date
Votes
4 comments
-
Jet Reports Historic Posts Because of the way Jet inserts new rows of data with the replicator, any cell reference is going to get pushed around so as far as I know, there isn't a way to simply say, "Always look at C10 even if a replicator pushes it down".
Even if you make it an absolute reference or a named range, those will be moved once Jet inserts the new rows of data.
Can you put the reference in an area that isn't going to be affected by the replicator? I guess I'm not seeing how you would use this. -
Jet Reports Historic Posts Thanks for the confirmation. I figured it was impossible. To the backup solution I go!
I'm trying to create a sheet that can be uploaded in to NAV for forecasting, and if I can isolate the sheet to be uploaded it makes the user's life easier. In our case, NAV wants to deal in units, but our sales team wants to deal in $. Thus there are some conversions that need to take place to get valid data. -
Jet Reports Historic Posts What about pulling the data normally as Jet needs to, then doing your conversion in a cell beside it, with an NP("EVAL") formula to turn the Jet result (units) into the type of result (dollars) that you need for your sales person.
Using the "EVAL" function allows you to hide and delete the column you don't want in your final output. -
Jet Reports Historic Posts You can create cell references in text (Excel won't touch those, no matter how much Jet will insert or delete) and use the INDIRECT function to read from that reference.
INDIRECT needs a text parameter and will try to 'translate' it to a real reference.
HTH
rmw
Please sign in to leave a comment.