Table in excel that's displayed as a picture and values change with cell values

[tr1q]

Hi All,

I require to create a picture of an excel table which updates as the table values change also this picture should move with the screen with in excel.

Any help is greatly appreciated.

Thank You.

Tariq

[Olly]

You could use the "Camera" functionality, then a bit of code to keep the camera picture visible, as you scroll around the sheet:

Please Login or Register  to view this content.

See attachment for a worked example.

[Richard Buttrey]

Hi,

You can copy a range of cells and use the Paste Other Options and choose the Paste as a linked picture.

Can you explain what you mean by the picture should move with the screen since that's the default situation. i.e. as you scroll away the picture will stay fixed relative to the original cell where it was positioned. I think perhaps you mean you want it visible all the time, but please clarify. It may need a macro to move it in response to some event.

[tr1q]

Olly and Richard thank you for your replies. Olly's macro almost does what I require and apologies I was not clear in my question.

I need the picture to move as the user scrolls down or up on the spread sheet.This Picture will serve as running totals on the spread sheet.

Thank you again.

[Olly]

There is no easy way in Excel to catch the 'scroll event' - the closest we can get, without getting very complicated, is as I have done, and use the Selection_Change event.

Have a read of Chip Pearson's page about it: http://www.cpearson.com/excel/DetectScroll.htm

[Richard Buttrey]

Olly and Richard thank you for your replies. Olly's macro almost does what I require and apologies I was not clear in my question.

I need the picture to move as the user scrolls down or up on the spread sheet.This Picture will serve as running totals on the spread sheet.

Thank you again.

Still somewhat confused by what you mean by wanting the picture to move. I presume you mean that you want it to move relative to the cell where it is first overlaid IF that cell moves away when you scroll. i.e. you want the picture to remain at the same screen position.

Have you considered freezing a pane on the row underneath the picture?

Where are your running totals? Are they in the table at the bottom? Personally I prefer to put totals at the top of any table or data range so that the row with the totals can be 'frozen' with a freeze pane.

In other words are you sure you actually need a picture.

[tr1q]

Richard really sorry let me try again so this spread sheet is a sales spread sheet which has some macro buttons and sales stats on top of the spread sheet i.e no of sold vehicles and total value. now at the bottom vehicles details are given which are participating in the sale as the sale progresses (keeping in mind in the sale we sell about 100 cars in an hour) user one by one goes through the vehicles and confirms their sale values which changes the stats mentioned above. Table and buttons are hidden only admin people uses them to produce final reports second reason is if I freeze panes this will take a lot of space on top of the screen so i was thinking if something could be built as a picture that moves with screen position i will not have to show all the things at top and they can use spread sheet much better.

[Richard Buttrey]

Any chance you could upload an example workbook, anonymised if necessary and add some notes explaining what each user type, i.e. admin or general users expect to see and how the users interact with the system.

[tr1q]

Richard for some reason my browser wouldn't let me attach anything to reply so i have attached it to previous comment.

[Richard Buttrey]

If you're concerned at taking up 9 rows and freezing the pane at row 10, one option would be to have instead of a 7 row 2 column Sales Stats table, turn it 90 degrees and have a 2 row 9 column table.

Failing that, and as we've indicated it will need some sort of sheet event macro to trigger the scrolling picture. Or rather than moving the picture with a macro which might be jerky, you could delete it, scroll to the relevant place and then have the macro recreate it.