cell link question

yoyeeyee

what i want:

a current odometer reading - coming from a mileage log that is changing daily - is there a way to make the cell link current? i'm sure there's a work around that i just can't think through at this time?

MCorbin

You could use a cross sheet formula - In your Current Odometer cell, formula =Max({Current Odometer Reading})

Where you use "Reference Other Sheet" to pull up the sheet with the Current Odometer Reading column and Max to get the highest value.

This assumes that the sheet with the readings applies only to this one cell. If you might have multiple vehicles in that sheet, you'd have to use Max(Collect(.... to get to the right vehicle and pull the readings). (I could help with that formula too, but would need to see more columns in both sheets)

MCorbin

You could use a cross sheet formula - In your Current Odometer cell, formula =Max({Current Odometer Reading})

Where you use "Reference Other Sheet" to pull up the sheet with the Current Odometer Reading column and Max to get the highest value.

This assumes that the sheet with the readings applies only to this one cell. If you might have multiple vehicles in that sheet, you'd have to use Max(Collect(.... to get to the right vehicle and pull the readings). (I could help with that formula too, but would need to see more columns in both sheets)

yoyeeyee

awesome @MCorbin - that worked perfectly!! Each vehicle has it's own mileage sheet, so this is ideal.

question: would you see an advantage to just one mileage sheet for multiple cars (64 at this time) vs. individual mileage sheets for each employee/driver? the drivers input mileage via form, mostly daily. it seems like it would end up being pretty large by the end of the year? but I don't know if it matters...

MCorbin

64 drivers * 1 daily entry = approximately 24,000 rows (assuming 7 days per week.... 17k if it's 5 days a week)

We currently have a limit of 20,000 rows per sheet. So you could start hitting the limit of maximum rows if you add them all to the same sheet.

Depending on your purpose for that sheet, you could have it archive older rows - use Automation to Copy Rows that are older than, say 6 months, to another sheet. (I usually make a copy of the sheet and call it "Archive: original sheet name")

You would need to change your formulas then to find the right row based on some identifier for car and/or driver. But once that is done, it's done and it will continue to work.

Having a single sheet is less maintenance (Only 1 place to update if something changes). It makes your metrics sheets a little more complicated, but it's not difficult to do, and once you've set them up, they work and are low maintenance. It also makes it a LOT easier to create metrics sheets that show trends across all drivers, over time, etc.