Getting a row to reference cells in a column

[Kate]

I'm sure there's some easy way to do this but....

On most sheets of my workbook I have January-December listed down
column A with various expenditure types in columns B-E or whatever.

The last sheet I'm trying to prepare needs to reference various columns
on other worksheets, but this time the Jan-Dec headings go across the
top.

I've tried paste special, paste link, various $s and VLOOKUP without
success. Can't get VLOOKUP to work at all, which is a shame because it
looked like it might work.

There must be a better way than doing every cell reference individually
by hand? Please help.

Thanks
Kate

 

[JE McGimpsey]

On most sheets of my workbook I have January-December listed down
column A with various expenditure types in columns B-E or whatever.

The last sheet I'm trying to prepare needs to reference various columns
on other worksheets, but this time the Jan-Dec headings go across the
top.

I've tried paste special, paste link, various $s and VLOOKUP without
success. Can't get VLOOKUP to work at all, which is a shame because it
looked like it might work.

There must be a better way than doing every cell reference individually
by hand? Please help.

You've described your data layout, but I'm not sure I understand what
you're trying to accomplish by your references to previous sheets, since
Paste and VLOOKUP do very different things...

Could you explain a bit further?

 

[PhilD]

There must be a better way than doing every cell reference individually
by hand? Please help.

You could sort out your formulae in columns, then copy --> paste
special --> transpose to get them in rows. If necessary, then move to
the right place, and delete the original.

Low tech, but should work.

PhilD

 

[Kate]

I want to enter the data once only, in columns, on the appropriate
worksheets but I want some of it to turn up on a report at the end.
This report has to be laid out differently, in rows because it is used
by another organisation and that's how their system works. So I'm
calling in columns of data from previous worksheets and laying it out
in rows to be used in the last report... or trying to.

I'm going to try PhilD's suggestion of building the report vertically
and using paste special to transpose the layout... Sounds quite quick
to me.

But I would have liked to get VLOOKUP to work for me!
K

 

[CyberTaz]

Hi Kate -

I believe what you are looking for is the TRANSPOSE function...

In the cell on the report (destination) sheet where you want the values
to start, click the fx button on the Formula Bar & select the TRANSPOSE
fx from the list (Lookup & Reference category). For the argument, click
the tab of the source sheet & select the cells in the column that
contain the Jan-Dec values.

*Rather than clicking OK* use Shift+Ctrl+Enter to finish the entry,
then select from the formula cell to the right 11 more cells (the 12
cells in the row where you want those values to appear).

Once those 12 cells are selected, click in the formula bar & hit
Shift+Ctrl+Enter again. That should generate the "array" you want.

HTH |:>)