Various Questions

  • Thread starter Thread starter Jeff Mackeny
  • Start date Start date
J

Jeff Mackeny

Hi, sorry for my second post today, I'm in the middle of creating an excel
spreadsheet for property management and i guess have more thena few
questions, bty if one knows an existing excel sheet for property mangmt,
please send it to me, I really appreciate your help.

1)Is it possible to sum a specific cell or cells from all sheets without
having to type ..+sheet1!+sheet2! and so on?

2)Can I break a cell in half, in other words B2 becomes two cells and I can
use separate numbers in each half?

3)How do I hide a formula (not lock) so when a user clicks on a cell the
formula its not visible?

4)what's the best way to carry over a balance from one sheet to another, so
for example I have a sheet for Jan, Feb and so on, so Feb should have the
remaining balance (if any) from Jan plus its own balance, Mar should have
the remaining balance from Jan, Feb and its own and so on?

5)Is it by any chance possible (or does anyone know of any macro) to create
a macro that will extract certain pre-defined data from an excel workbook
and put on a new sheet or workbook, for example to run a report on a
specific person or object within excel, so the macro or code would gather
and extract all requested data and put it on a new sheet or perhaps a
pre-defined document.

Thanks a million
Jeff
 
Jeff Mackeny said:
Hi, sorry for my second post today, I'm in the middle of creating an excel
spreadsheet for property management and i guess have more thena few
questions, bty if one knows an existing excel sheet for property mangmt,
please send it to me, I really appreciate your help.

1)Is it possible to sum a specific cell or cells from all sheets without
having to type ..+sheet1!+sheet2! and so on?
Click to expand...


=SUM(Sheet1:Sheet3!B1)


click the first sheet tab, hold down shift and click on the last, select the
cell and press enter

2)Can I break a cell in half, in other words B2 becomes two cells and I can
use separate numbers in each half?
Click to expand...

No you can't.
You can merge other cells and make it look like that but that is the way of
the fool.


3)How do I hide a formula (not lock) so when a user clicks on a cell the
formula its not visible?
Click to expand...

You most protect the sheet, first select all cells, do
format>cells>protection and then uncheck locked,
now select the cells with formulas and do format>cells>protection and check
hidden.
Now protect the sheet

4)what's the best way to carry over a balance from one sheet to another, so
for example I have a sheet for Jan, Feb and so on, so Feb should have the
remaining balance (if any) from Jan plus its own balance, Mar should have
the remaining balance from Jan, Feb and its own and so on?
Click to expand...


link to the previous sheet's cell and if needed use a date formula to know
what month it is and
if today's month is the same month as the sheet tab carry over the balance,
if the previous sheet is filled down/across and
the balance will be in a known cell that is empty until the last day of the
month just use something simple like

=IF('Jan'!A31 = "", "",'Jan'!A31)

5)Is it by any chance possible (or does anyone know of any macro) to create
a macro that will extract certain pre-defined data from an excel workbook
and put on a new sheet or workbook, for example to run a report on a
specific person or object within excel, so the macro or code would gather
and extract all requested data and put it on a new sheet or perhaps a
pre-defined document.
Click to expand...

Yes, but it is not an ordinary thing to do. You can start by recording a
macro when you do
create such a report, then post back with the code you have (preferably in
the programming NG)


--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
Peo,

thanks for your quick response, I didn't quite understand your 'carry over'
answer, so lets assume my sheets are named by the name of the month Jan,
Feb, Mar and so on, column K on the Jan sheet lists open balances all the
way down K1:K300, now I need to carry over this balance to column K on the
feb sheet, and then to column K on the Mar sheet, but of course Mar will now
also include the Feb balance, and finally I need to carry this over on a
specific day, say the 1st on every month, so the 29th of the month Mar will
have 0 balance from Jan and Feb.

Regarding the report, just hit the macro record button, is there maybe a 3rd
party program or code that will extract certain data from excel.

Thanks
 
Back
Top