You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

User profile for user: Neil Paisnel
Neil Paisnel Author
User level: Level 1
51 points

Value Change in lookup bel..keep current entries

I have a rental document for a stable yard.


Columns

Date/Name/Month/Amount/ Tenant Running Total 1 -2-3 etc / Total of all.



Now I wanted to Create a LooKup table, so when ever I enter a Tenants name in the Name column, the individual Amounts is filled in to their line,


Easy enough from what I can see.



BUT,,


IF halfway through the year, I put their rent up because they rent another horse box or field, change their agreement to Fulliverry etc..I want to change the entry in the LookUp Table.


From what I understand, that will then go and change all previous entries for previous months of the year



How can I set it up so that Once the LOOKUP function is used and an entry is taken from it in to he main sheet, that value does not change even when in future months, the value in the LOOKUP table changes.


Thanks

Posted on Dec 27, 2021 12:48 AM

Reply
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Posted on Dec 27, 2021 10:57 PM

Lookup will return whatever data is in the Lookup Table and fits the search term.


Before you change the rental rate in the Lookup table, you need to disable the Lookup formulas on entries that have already been calculated and are not to be recalculated with the new rates.


Fortunately, this is a fairly simple procedure.


The change in rates will affect only some of your clients, and will affect those clients only on dates starting on the date the changes go into effect. changing the values previously calculated with a reference to the lookup table to fixed values will have no effect on future values in those cells.


With that in mind, and assuming your table is recorded in order by date*, select all of the cells up to the date on which the change is to take place, then press command-C to copy those cells.

With the copied cells still selected, go to the Edit menu and select Paste Formula Results.

All of the numbers in these cells will now be fixed values, and won't be included in the lookup for rental rates.


Once all cells of this type have been modified, go to the Lookup table and change the rates for clients whose rentals have changed.


Regards,

Barry

View in context

Similar questions

7 replies
Question marked as Top-ranking reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Dec 27, 2021 10:57 PM in response to Neil Paisnel

Lookup will return whatever data is in the Lookup Table and fits the search term.


Before you change the rental rate in the Lookup table, you need to disable the Lookup formulas on entries that have already been calculated and are not to be recalculated with the new rates.


Fortunately, this is a fairly simple procedure.


The change in rates will affect only some of your clients, and will affect those clients only on dates starting on the date the changes go into effect. changing the values previously calculated with a reference to the lookup table to fixed values will have no effect on future values in those cells.


With that in mind, and assuming your table is recorded in order by date*, select all of the cells up to the date on which the change is to take place, then press command-C to copy those cells.

With the copied cells still selected, go to the Edit menu and select Paste Formula Results.

All of the numbers in these cells will now be fixed values, and won't be included in the lookup for rental rates.


Once all cells of this type have been modified, go to the Lookup table and change the rates for clients whose rentals have changed.


Regards,

Barry

Reply
User profile for user: Badunit
Badunit
User level: Level 7
28,204 points

Dec 27, 2021 6:34 AM in response to Neil Paisnel

It sounds like your original table is a list of the payments made by your tenants each month, one row for each payment, entered as numbers and text, not formulas. I do not see anything in your list of columns that would change when their rent changes other than the payments they will make in the future will be higher/lower. All the rows with past payments will be as they were.


If you do a lookup on the data in that table, it should return the data from the table. I do not understand what would change when their rent goes up that would affect that. You may have to explain further.

Reply
User profile for user: Neil Paisnel
Neil Paisnel Author
User level: Level 1
51 points

Dec 28, 2021 1:47 AM in response to Barry

Badunit, Barry, thank you both for taking the time to reply


Barry has cottoned on to what I was eluding to ..sorry if I did not explain it very well


I have uploaded a copy of my numbers document here:

http://paisnel.co.uk/upload/rental.numbers


That is WItHOUT any lookup table yet created. I was not going to try and do that only to find what I was after was possible.



Barry, Thank you, yes your answer is what I was looking for. I will just be able to highlight the entire table

Columnns D through to P line 2 down as far as I need to go , until I change the data its eh LookUp table and as you say, CMD-C then CMD SHIFT V to paste just the results.



The lookup formula will be to go into Column "D", the Total Column, instead of the drop down list of values



I had considered trying to build a sheet that some how imports the Bank downloaded CSV file of the account the rent is paid in to , and get it all auto filled in ...but that may be beyond my limited ability!


I am sure you may find lots of 'errors ', silly mistakes, or 'roundabout ' ways of login stuff that could be achieved in an easier way..but it is a sheet that has developed since 2017 .but it works for me. I took over the running of the farm and the stables in 2000, and until 2017 jsut did it all long hand in a rent book. By 2017 I thought this is getting silly ! Time to put it on a spread sheet. Then the Expenses bit ?profit Loos section all got added in to .


Thank you for helping out a dumb old farmer :)







Reply
User profile for user: Neil Paisnel
Neil Paisnel Author
User level: Level 1
51 points

Dec 28, 2021 2:29 AM in response to Neil Paisnel

This seems to work.


Table with Lookup function


http://paisnel.co.uk/upload/rental-with-lookup.numbers


Initially it created a red Triangle warning error with empty argument to the Lookup Formula in the Detail column..but an IF statement solved that .


Is that the way to do it or is there an auto function that does that better?


This is what I came up with


IF ( B8 ="", "", LOOKUP (B8, Monthly::A, Monthly::B))




Reply
User profile for user: Barry
Barry
User level: Level 9
59,951 points

Dec 28, 2021 10:07 PM in response to Neil Paisnel

IF ( B8 ="", "", LOOKUP (B8, Monthly::A, Monthly::B))


This is a supplement to LOOKUP I have often recommended.


In most cases I've described the part in bold as a switch that prevents calculation from occurring until there is data in cell B8, and allowing the calculation ( in normal weight type) to occur when B8 contained a value.


Regards,

Barry

Reply
User profile for user: Neil Paisnel
Neil Paisnel Author
User level: Level 1
51 points

Dec 29, 2021 2:17 AM in response to Barry

Was a bit of head scratching for me ..but had a light bulb moment that this had occurred to me before..and then I knew what the solution was..I use Numbers maybe once every 3-4 months when I check the bank statements and that every one has paid...

Creating new spreadsheets or functions ..not even every year..so it is a new experience almost every time

1


Thanks for your help



Reply

Value Change in lookup bel..keep current entries

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.
Learn more Sign up